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

Estimated reading time: 5 minutes

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:

SELECT
       COMP.Name0 AS 'Machine Name',
       COMP.UserName0 AS 'Username',
       COMP.Manufacturer0 AS 'Make',
       COMP.Model0 AS 'Model',
       COMP.SystemType0 AS 'Architecture',
       COMP.TotalPhysicalMemory0/1024 AS 'RAM',
       ARP.Publisher0 AS 'Publisher',
       ARP.DisplayName0 AS 'Program',
       ARP.Version0 AS 'Version',
       ARP.InstallDate0 AS 'Install Date',
       ARP.ProdID0 AS 'Product ID'
  FROM v_Add_Remove_Programs ARP
       JOIN v_GS_COMPUTER_SYSTEM COMP
       on ARP.ResourceID = COMP.ResourceID

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:

WHERE ARP.DisplayName0 IS NOT NULL
      AND ARP.ProdId0 NOT LIKE '%(KB%)%'
      AND ARP.DisplayName0 NOT LIKE '%Update%'

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:

Declare @CollID char(8)
        Set @CollID = 'SMS000AA'

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:

JOIN v_FullCollectionMembership FCM
     on ARP.ResourceID = FCM.ResourceID

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

WHERE FCM.CollectionID = @CollID

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:

Declare @CollID char(8)
       Set @CollID = 'SMS000AA'
  SELECT
       COMP.Name0 AS 'Machine Name',
       COMP.UserName0 AS 'Username',
       COMP.Manufacturer0 AS 'Make',
       COMP.Model0 AS 'Model',
       COMP.SystemType0 AS 'Architecture',
       COMP.TotalPhysicalMemory0/1024 AS 'RAM',
       ARP.Publisher0 AS 'Publisher',
       ARP.DisplayName0 AS 'Program',
       ARP.Version0 AS 'Version',
       ARP.InstallDate0 AS 'Install Date',
       ARP.ProdID0 AS 'Product ID'
  FROM v_Add_Remove_Programs ARP
       JOIN v_FullCollectionMembership FCM
            on ARP.ResourceID = FCM.ResourceID
       JOIN v_GS_COMPUTER_SYSTEM COMP
            on ARP.ResourceID = COMP.ResourceID
       WHERE FCM.CollectionID = @CollID
            AND ARP.DisplayName0 IS NOT NULL
            AND ARP.ProdId0 NOT LIKE '%(KB%)%'
            AND ARP.DisplayName0 NOT LIKE '%Update%'

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:

SELECT
      *
  FROM v_Add_Remove_Programs ARP
     JOIN v_GS_COMPUTER_SYSTEM COMP
        on ARP.ResourceID = COMP.ResourceID

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.