Configuration Manager SQL Query for Installed Software Per Machine & Per Collection

microsoft

If you are using Configuration Manager, you will be aware that you can create a number of custom reports using the SQL data and SSRS to produce the reports.

The best way to start to create a custom report is to ensure that you get your SQL query correct first. We have written hundreds of various queries over the years for our own use and by request through our website. This guide will show you how to pull data from the Configuration Manager database and give you the following information:



  • Machine Name
  • Username
  • Make
  • Model
  • Architecture
  • RAM
  • Publisher
  • Program
  • Version
  • Install Date
  • Product ID

You can add and remove any of these columns where you feel the need to customise them but for this guide, these are what we will use. By running our first query, it will bring back a huge amount of data (depending on your environment size of course) – it will basically show everything that is in the Add/Remove Programs list for every machine in your Configuration Manager database.

To do this, run the following query:

As you can probably see from the returned data, it is a lot and looks a bit of a mess. So, how can we break it down a little? Our first suggestion is to remove the possible junk from the query return, so you can add the following to the end of your query:

This will now remove any “Program” that is “NULL” or have KB in the title – this means removing all/most of the Windows Update information. If you run this query now, you should get a return which looks much tidier.

But what if I want to run this report based on a collection?

Well, you can do that too, if you have a number of set collections be it for machine types or for departments etc, then you may want to just return all installed software on all machines within the Finance department…

To do this, you first need to find the Collection ID of the Configuration Manager Collection that you want to base this query on – as I’m assuming it will be mostly System Center Pros reading this, I will assume you know how to find this, if you do not, then please leave a comment below and I will be happy to explain…

Once you have your Collection ID, you need to make a declaration in your SQL query, so you should add the following to the start of your query:

Replacing the SMS000AA with your Collection ID (do not remove the quotation marks..).

You will also need to JOIN a new table to your query, so you should add this:

Once you have done that, you need to add a WHERE statement at the bottom of the query like this:

This should be after your JOIN but before your other WHERE statements.

So, if we use the full query from above, your new SQL query which is now based only on machines within the declared Collection, it should look like this:

You should now see only machines within your declared collection and all programs that are installed on the machines (that have report Add/Remove Programs information…).

What if I want more information?

If you want to expand the data that is returned, you can always either join more tables or run the following query and select the columns that you want:

Once you are happy with your SQL query, you can then use it in SSRS to produce a professional looking report.

How Do I Create The SSRS Report?

We have now created a guide that will help you to create your SSRS reports based on the data that this guide will pull into the Configuration Manager database.

You can access this guide by clicking the link below:

Create a SSRS Report using Configuration Manager Database Data

More Queries

Our full range of SQL and WQL Collection queries are available here.

Feedback

If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.



1 Response

  1. Ajith Bhojani says:

    Thanks for the query. It is exactly what I needed 🙂

Leave us a message...