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.
Again we are writing this guide on an assumption that you have at least a basic knowledge of SQL and SSRS so if you do have any questions, please use our Disqus system at the bottom of this guide to post any comments.
Open up your SSRS console. You will be presented with a dialog box like this one:
You want to create a Blank Report New Report, so click “Blank Report”. This will then take you to the main report builder screen. The first thing that you need to do here is to add a Data Source for your report. This data source will be your Configuration Manager database.
To do this, right click on Data Sources in the tree view on the left hand side and select “Add Data Source”.
You will then be presented with another dialog box where you need to create your SQL connection to your database. You should then do the following:
- Give your Data Source a friendly name
- Select the radio button for “Use a connection embedded in my report”
- From the drop down for connection type, select “Microsoft SQL Server”
You now need to build your SQL connection string. There are two ways of doing this, as we know SQL well, we would normally just build our own string which would be similar to this:
Data Source=TGHSQL1;Initial Catalog=CMDB
This means that our Data Source is the name of our SQL server and the Initial Catalog is the name of our Configuration Manager database.
The alternative way is to click on the Build button, you can then enter your SQL server and it will give you a drop down of databases on that server where you can select your Configuration Manager database from.
Once you have built your connection string the dialog box should look like this:
To confirm that everything is correct, you should click on the Test Connection button. If it is correct, you should continue, if not, check all your settings again.
Click the OK button once you are happy and this will complete the Data Source setup for your new report and you should now see it in your tree view:
Now that you have your SQL data source for your new report, you need to create a query that will pull the data you require from your database that you can use in your report.
As a simple example, we are going to create a very basic query to pull through the columns that we want from the Autodesk table but we are also going to link in three other tables so that we can allocate the software to machine names and also report on make, model and serial numbers of those machines with the Autodesk software on them.
The script that we are going to use is this:
SYS.Netbios_Name0 AS [Computer Name],
USR.Manufacturer0 AS [Make],
USR.Model0 AS [Model],
BIO.SerialNumber0 AS [Asset Tag],
CAD.KeyName00 AS [Product ID],
CAD.ProductName00 AS [AutoCAD Suite],
CAD.Release00 AS [AutoCAD Version],
CAD.SerialNumber00 AS [AutoCAD Serial Number],
CAD.TimeKey AS [Last Inventory]
INNER JOIN v_R_System SYS
ON SYS.ResourceID = CAD.MachineID
INNER JOIN V_GS_COMPUTER_SYSTEM USR
ON SYS.ResourceID = USR.ResourceID
INNER JOIN v_GS_PC_BIOS BIO
ON SYS.ResourceID = BIO.ResourceID
WHERE ProductName00 IS NOT NULL
ORDER BY "AutoCAD Suite"
This SQL script will give us the use of many useful columns from different tables within the Configuration Manager database, you will be able to pick and choose which ones you want to use later in the guide.
So now that we have a basic SQL script to pull data from the database, how do we create the query in SSRS?
In your tree view, you should right click on the Datasets folder and select “Add Dataset”:
You will then be presented with a new dialog box where you should enter the details as follows:
- Give the Dataset a friendly name
- Select the radio button called “Use a dataset embedded in my report”
- From the Data source drop down menu you should select the Data Source that you created earlier
- You should then either copy and paste our script or write your own script into the Query box
Click the OK button and once you have done that, you should now see under your tree view a new Dataset and also a number of columns from the tables that we stated in our query:
You now have everything in place to start the design of your report. I would suggest that you first give the report a name, so where is says “Click to add title” – type in a report name:
You can also start to change the size of the page and layout but we will leave you to do that yourselves!
There are then a few ways to get the column data onto your page, but our favoured method is to use a “Matrix”. This is basically a fancy table where you can put the columns into and is very simple and straight forward to use.
To do this, right click anywhere within the white space on your new report, select Insert and then Matrix.
Once you have inserted your Matrix, you should move it on the report to where you want it. You are now ready to start to add your data columns to it.
You can now simply drag and drop your data columns from your Dataset into the Row Groups section towards the bottom of the screen:
When that is complete, you should end up with a report that looks like this:
You should now save your report somewhere on your SQL SSRS server and either browse to that location using a web browser or click the Run button at the top left of the SSRS console to test your report. You should now get a full list of all the machines that have so far reported back to your Configuration Manager server.
When you are completely happy with that, you can start to format and colour the report in anyway your wish to make it look tidy and professional instead of just boring black and white tables!
If you have any problems with any of the above, then please leave your comments and questions below using our Disqus system and we will try to get back to you as soon as we can.
We also like to hear back any success stories too, so if you have used our guide and found it helpful, we would love to hear from you.