Inventory of all Microsoft Office Versions older than 2010

[anw]

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!!

[Susan_A]

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]

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 · Accepted solution]

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