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

Find Product GUID w/ WMIC or Powershell, & Other Useful Commands

Occasionally one needs to know what software is installed and its associated GUID.

There are a few simple ways to acquire this information for .msi's without going line by line through the Uninstall key in the Registry.

First there is the basic wmic method in a command prompt:

wmic product get

This will output a lot of information that is generally hard to look at, but nonetheless shows you the installed programs and their GUIDs if you can muddle your way through it (or output the results to a text file and search from there, which is discussed below)

Second you can simply call only application names and search for the product there, and then run a second command to find its specific GUID.

1) wmic product get name
2) wmic product where name="APPNAME" get Name,Version,IdentifyingNumber

Insert the application name acquired from step one where it says APPNAME (keep the quotes) in step two.


For any of the above commands, you can output them to a text file and or run them for a remote computer on the network.

Output to Text file named InstalledPrograms on C:
wmic product get name > C:\InstalledPrograms.txt

Run wmic command for a remote computer:
wmic /node:COMPUTERNAME {rest of command here}

Replace COMPUTERNAME with the hostname of the machine you want the results from.  Don't remove the /node: part.

Here's a neat trick using Powershell to get the information with output in a neat little table.  In powershell, run:

get-wmiobject -class Win32_Product | Format-Table IdentifyingNumber, Name, LocalPackage

To do the same only for a remote computer:

get-wmiobject - class Win32_Product -computername COMPUTERNAME | Format-Table IdentifyingNumber, Name, LocalPackage

Replace COMPUTERNAME with the hostname of the machine you want the results from.

This line will return all the subkeys and their registry values for the Uninstall registry key.

Get-ChildItem hklm:\software\Wow6432Node\microsoft\windows\currentversion\uninstall | ForEach-Object {Get-ItemProperty $_.pspath}

Other random but useful commands are listed below.

To get hardware information on a remote system (replace COMPUTERNAME w/ hostname):

systeminfo /s COMPUTERNAME

To see the currently logged on user of a remote system (replace COMPUTERNAME w/ hostname):

wmic /node:COMPUTERNAME ComputerSystem Get Username

To get the IP address of a remote system (replace COMPUTERNAME w/ hostname)

wmic /node:COMPUTERNAME nicconfig get ippaddress

FYI - here are the Uninstall reg key locations:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\CurrentVersion\Uninstall

***Many of these commands can take some time especially if done for a remote machine or if there are a lot of applications in question.  You may want to go get a cup of coffee after pressing enter and when you get back you'll have the information you need :) ***


Resources:

http://www.computerhope.com/wmic.htm

http://software-inventory.net/installed-software-audit

https://technet.microsoft.com/en-us/library/dd347651.aspx

http://stackoverflow.com/questions/29937568/how-can-i-find-the-product-guid-of-an-installed-msi-setup

http://betanews.com/2011/01/14/wmic-the-best-command-line-tool-you-ve-never-used/

http://blogs.technet.com/b/askperf/archive/2012/02/17/useful-wmic-queries.aspx

https://technet.microsoft.com/en-us/library/ee176852.aspx