SCCM SQL Reporting code “Computers with one of many installed apps”

Today at a Client, the need arose to create a report which would list computers that have any of two applications installed.  The principles used could easily be modified to fit a much larger list of applications, if the need arises.

I started by modifying the canned “Search for Installed software” by stripping a number of the default prompts, and then altering the comparison line of the query to include two or comparisons, one for Mozilla Firefox and another for Google Chrome.  The result was this report, showing a count of how many instances of each version of the app was found, which was pretty neat.

02 - need to edit

It was desired to know beyond this which computers actually have a particular application installed. This was done by using the SCCM reports linking function. However, out of the box we didn’t have any good reports available, so we created one.

As I normally do, I began in the SQL Management Studio. I was able to identify the ProductName0 Column of the v_GS_INSTALLED_SOFTWARE_CATEGORIZED Table as a good identifier to locate the software we needed.  For the actual code, see the link below.

After creating our new report which asks for the input of a Product name and then shows you all of the juicy details including computers which have that product name installed, I went back to the original report in the ConfigMgr console and added a link to the newly created report. In order to link reports, you need to specify one of the columns on the output rows of one report as an input for a prompt on the receiving report. An easy tip to determine which column number is which is to mouse over the header of each line, and note the code tip in your status bar (for most browsers). We’ll mouse over the Product Name field and see what happens.

03 - need to edit

Note the ‘Col=’ bit of the url, highlighted in Yellow. This is our clue which column to select in the linked field, as depicted below.

04 - need to edit

And, now, in the final product, clicking the Linked icon in the front of any row will launch another report, listing all of the computers with this software installed.

02 - need to edit

05 - need to edit

Sorry for the somewhat gratuitous redacting of information.  Some of my clients are a bit sensitive when it comes to serial numbers and collection names, etc.

I hope that this demonstration was helpful! As always, please let me know if you see any room for improvement or if you have any questions.

Edit:  I now have a better way of doing this, which joins all product versions together.  

Thanks go out to Simon Cartin for helping me figure out how to do the IF Then and SubString commands!

Here is a screen shot of this output.

6 - new version

The code for this is also included in the below link.

All source code is available here –


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s