SCCM 2012 Query: Installed Application Version

Hello,

So I ran into this today - needed to find all machines in SCCM 2012 that have Microsoft Office 2007 installed but do NOT have service pack version 3 (SP3).

I created the query mentioned below and confirmed that this returned the correct machines by comparing with a colleage's different query version for the same task and checking samples of the machines' Office versions in Resource Explorer.

Here's the method:

In SCCM I looked up a machine that I knew had SP3 installed.  I right clicked and Start -> Resource Explorer -> Hardware -> Installed Applications and found the application in question - in this case Office 2007.  Microsoft Office Professional Plus 2007 was seen under the Display Name.  On the far right of the application entry is the version number.  I took note of both of these.

By the way:

Office ProPlus 2007 Service Pack 2 Version Number is 12.0.6424.1000
Office ProPlus 2007 Service Pack 3 Version Number is 12.0.6612.1000

So in my query I did this for the criteria:

Simple Value Installed Applications - Display Name is equal to Microsoft Office Professional Plus 2007
AND
Simple Value Installed Applications - Version is less than 12.0.6612.1000

You can also do "is not equal to" rather than "is less than."

This returned all the machines that had MS Office ProPlus 2007 service packs below SP3.

This process can of course be modified to query for any software listed in the Applications area of Resource Explorer.

Just in case someone wants it, below is the resulting criteria query language from the selected query options shown above.

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2007" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "12.0.6612.1000"


Additional Resources:

http://www.4kcc.com/How2/sp_version.html

http://pcsupport.about.com/od/keepingupwithupdates/a/office-service-pack.htm