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:
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…).
Our full range of SQL and WQL Collection queries are available here.
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.