cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
anw
Engaged Sweeper
Hi,

i hope you can help me.
We need to upgrade all older Versions to Microsoft Office 2010.

So, i need a report wich show me a list of all Assets they're have Microsoft Office Products older than Microsoft Office 2010 installed. (14.0)

This is my code now:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain As Domain,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And
tblSoftware.softwareVersion <= '14' And tblAssetCustom.State = 1) Or
(tblSoftwareUni.softwareName Like '%Office%' And
tblSoftwareUni.softwareName Like '%Microsoft%' And
tblSoftwareUni.softwareName Like '%2000%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%'And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And
tblSoftwareUni.softwareName Not Like '%proofing%')
Order By tblSoftwareUni.softwareName


Is this coding clean?

Do you have any suggestions for improvement?

Many thanks in advance!!
1 ACCEPTED SOLUTION
Bart_E
Lansweeper Employee
Lansweeper Employee
The version filter in your SQL query doesn't really make sense because tblSoftware.softwareVersion is a text field, not a numeric field. It might be easier to add some additional filters to the tblSoftwareUni.softwareName field as shown below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain As Domain,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Not Like '%2010%' And
tblSoftwareUni.softwareName Not Like '%2013%' And
tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And tblAssetCustom.State = 1
Order By tblSoftwareUni.softwareName

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
TblSoftware.softwareVersion is the only field that stores software version numbers. This field is a text field, not a numeric field, and mimics what you see in Add/Remove Programs on the client machine itself. You'll notice that the software version of Office 2010 is not just "14" for instance, but something like "14.0.7015.1000". This is text, not a numeric value.

Using a > or < filter on a text field does not make sense if you are trying to treat the data as numeric values. If you have two text values like the ones below for instance, SQL will consider 2.0 to be "greater than" 10.0. This is because SQL reads text values from left to right. As 2 is greater than 1, 2.0 is considered to be "greater than" 10.0. While this behavior might not be a problem in all situations, you cannot rely on > or < filters added to softwareVersion to give you the results you are looking for.

10.0
2.0


If you want to treat the tblSoftware.softwareVersion values as numeric values, you will need to isolate the relevant version number from the text and convert it to a numeric value. If the "main" version number is the one preceding the first period in the string, you could use CharIndex to isolate it, like so:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Cast(SubString(tblSoftware.softwareVersion, CharIndex('.',
tblSoftware.softwareVersion) - 10, 10) As integer) As MainVersionNumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Order By MainVersionNumber
anw
Engaged Sweeper
Hello Bart.E,

thanks!

That's what I have done now. It seems to work well.

But is there any other possibility to filter the software by the version number?
Any other db-typ field?
Bart_E
Lansweeper Employee
Lansweeper Employee
The version filter in your SQL query doesn't really make sense because tblSoftware.softwareVersion is a text field, not a numeric field. It might be easier to add some additional filters to the tblSoftwareUni.softwareName field as shown below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain As Domain,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Not Like '%2010%' And
tblSoftwareUni.softwareName Not Like '%2013%' And
tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%runtime%' And
tblSoftwareUni.softwareName Not Like '%visio%' And
tblSoftwareUni.softwareName Not Like '%proofing%' And
tblSoftwareUni.softwareName Not Like '%analyzer%' And
tblSoftwareUni.softwareName Not Like '%resource%' And tblAssetCustom.State = 1
Order By tblSoftwareUni.softwareName