Software File Versions.

So a request came in from a business team to find out who isn't on the correct version and/or patch level of SAPGui. 

I had quite a few attempts at using Asset Intelligence but Hardware Inventory wasn't giving me the right info. Instead, I turned to Software Inventory. Software Inventorying is notoriously slow/intensive and isn't really recommended save for specific reasons (e.g. see Sherry Kissinger's posts and blog) but it's enabled weekly for .exes only so it was already gathering the right stuff for me.

Props go Sherry Kissinger for her help with my TechNet post. She very kindly and gentl pointed out "You're doing it wrong"

First, I needed to know how many versions of SAPGui.exe were out there, so a SQL query could do this:

Select sf.filename, sf.fileversion, sf.filepath, count(*)
from v_gs_softwarefile sf
where sf.filename = 'sapgui.exe'
group by sf.filename, sf.fileversion, sf.filepath

Returned:

Copying those results out and into Excel, removing duplicates and separating into general versions gave me all versions out there:

I am only concerned with the Program Files path exes, so I can now create my Collections with Queries:

• All Computers with Sagui.exe installed
• All Computers with the supported version 7300.3.15.8950 of Sapgui.exe installed
• All Computers with version 7300 that aren't on the supported 7300.3.15.8950 installed
• All Computers with version 7400 installed (naughty!)
• All Computers with version 7200 installed
• All Computers with version 7100

(The reason for the 7200 and 7100 Collections is that they require a different Application in order to upgrade)

All Computers with Sagui.exe installed
select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\")

All Computers with the supported version 7300.3.15.8950 of Sapgui.exe installed
select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.3.15.8950"

All Computers with version 7300 that aren't on the supported 7300.3.15.8950 installed

Query 7300.1.0.8948

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.1.0.8948"

Query 7300.3.7.8950

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "sapgui.exe" and (SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files (x86)\\SAP\\FrontEnd\\SAPgui\\" or SMS_G_System_SoftwareFile.FilePath = "C:\\Program Files\\SAP\\FrontEnd\\SAPgui\\") and SMS_G_System_SoftwareFile.FileVersion = "7300.3.7.8950"

And repeat changing the SMS_G_System_SoftwareFile.FileVersion for each Query and for each Collection.

Now I can include (or re-use) the relevant Collection(s) in my Software Deployment Collections, and my Business team will be happy bunnies.

Lessons learnt:

1. Don't use Like!
2. Use SQL to help get your query right
3. Know what you are going to use the Collections for in the end