Create a SSRS Report using Configuration Manager Database Data
We have written a number of guides on how to collect client machine data using Configuration Manager 2012 in the past including guides like Autodesk serial number collection, Adobe serial number collection and Nitro PDF serial number collection.
These guides detail how to setup your Configuration Manager environment to collect the data into the database but it has been brought to our attention that we have never stated how to create reports from the database into something that most people can read. This has always been based on assumption that our readers would know how to use SQL and SSRS, but obviously this is not the case for everyone.
So, we have decided to write this guide that will hopefully help people to take the data from their Configuration Manager database and put it into a nice little report using SSRS that can then be viewed via a web browser for all “normal” users (like the boss!) to view.
For the purposes of this guide, we are going to use Autodesk serial number reporting as this is one of the most popular posts on our website. But the same rules apply – just different scripts to pull the data from the database into SSRS.
DATA COLLECTION INTO CONFIGURATION MANAGER DATABASE
As stated above, we are going to use the Autodesk serial number reporting data for the purposes of this guide. The below link is the guide that should be used (and has been used prior to creating this report) to get the information from the clients machine into the Configuration Manager database so that we can now use it to create a SSRS report.
We are now assuming that you have gone through the above guide and this has worked and that you now have data from your client machines in your Configuration Manager database. You should note that this process can take days or weeks depending on the size and configuration of your environment. For example, this information will only get injected into the database from the client machine once the Hardware Inventory has run on the client machine and reported back to the Configuration Manager server.
To check if you have any data in your Configuration Manager database, then open your SQL Console and connect to your Configuration Manager database. If you have not changed the name of the table within the MOF file that we have provided then you should run this query on the database:
ProductName00 IS NOT NULL
If there is data in there, then you should get a return of 11 columns of raw data similar to below:
The reason that we put in the “WHERE ProductName00 IS NOT NULL” statement at the end is that it will otherwise pull back a load of unwanted data that corrupts the true data that we want to see.
If you see nothing, then either none of your clients have yet reported back, or you have done something wrong at the original setup, so please recheck everything you have done and follow our original guide very carefully.
So, once you have the client machine Autodesk data in your Configuration Manager database, you can move on to the next stage which is to actually create the report in SSRS.