Listing software versions in SCCM

And here is another windows-related posting. So I needed to look for which Windows computers had a given program and which version they had installed. Since over here they use SCCM, I thought it might be something right up its alley. And it was.. after a fashion.

So, under \Monitoring\Overview\Queries it has a query called "All Systems with Specified Product Name and Version,"

which when you run it asks for the, well, product name and version and then returns the hosts that match that. So, when you click it, it pops a window

Where you enter the product name. Once you press "OK" then it asks for the product version. After that it should run and spit out the hosts matching the criteria and some other info, which we do not know what yet since we are good kids and did not look under the skirt.

Thing is, it returned nothing.

Hmmm, maybe I did not enter the proper data. So how about if we pick a program that we know to be installed in at least some of the machines? First we need to see what it looks like under SCCM:

What we are interested on are

• Product Name = Mozila Firefox 41.0.1 US(x86 en US)
  
• Product Version = 41.0.1

Don't ask me why Mozilla decided to put the Product Version in the Product Name; I've asked them before and never had an answer. Just between you and me, it really does annoy me because it caused some issues deploying packages using puppet. That said, I think we should save that conversation for another time.

I think we have been good children for long enough. Time for some skirt pulling! And here is how the query looks like (I did some pretty formatting so we can see what is going on):

select distinct 
   sys.Name, 
   sys.SMSAssignedSites, 
   sys.OperatingSystemNameandVersion, 
   sys.ResourceDomainORWorkgroup, 
   sys.LastLogonUserName, 
   sys.IPAddresses, 
   sys.IPSubnets, 
   sys.ResourceId, 
   sys.ResourceType 
from
   SMS_G_System_SoftwareProduct as prod 
   inner join SMS_R_System as sys 
on 
   sys.ResourceId = prod.ResourceID 
where 
   prod.ProductName like ##PRM:SMS_G_System_SoftwareProduct.ProductName## 
and 
   prod.ProductVersion like ##PRM:SMS_G_System_SoftwareProduct.ProductVersion##

The ##PRM:SMS_G_System_SoftwareProduct.ProductName## means that it will stop and ask for the SMS_G_System_SoftwareProduct.ProductVersion, which means we can have someone run the query and enter the search data without needing to mess with the code. So, we can see what they were trying to accomplish even though it does not work. So, let's do some thinking with our fingers try something else:

select 
distinct 
   SMS_R_System.Name 
from  
   SMS_R_System 
   inner join SMS_G_System_INSTALLED_SOFTWARE 
on 
   SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId 
where 
   SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Mozila Firefox%"

And that works! It prints out a list of hostnames which have that product. I will not be able to show the screen capture since it would show the name of production machines instead of my lab. So you will have to have some faith. While we are at it, how about if we add more useful info? I don't know about you, but I would like to know which version of the software is installed and when that installation (or upgrade) took place. I will not not comment on the extra attributes we are getting back because I think you can figure out

select 
distinct 
   SMS_R_System.Name,
   SMS_G_System_INSTALLED_SOFTWARE.ProductVersion,
   SMS_G_System_INSTALLED_SOFTWARE.InstallDate
from  
   SMS_R_System 
   inner join SMS_G_System_INSTALLED_SOFTWARE 
on 
   SMS_G_System_INSTALLED_SOFTWARE.ResourceId = SMS_R_System.ResourceId 
where 
   SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Mozila Firefox%"

We could make this interactive by changing

   SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like "Mozila Firefox%"

to

   ##SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName##

Incidentally, it seems that SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName gave the same result as SMS_G_System_INSTALLED_SOFTWARE.ProductName, but that does not mean that will always be the case.