Create an SSRS Report using Configuration Manager Database Data

Estimated reading time: 9 minutes

An SSRS Report is an excellent way of taking information gathered by SCCM and turning it into readable and understandable reports for senior management.

We have written a number of guides on how to collect client machine data using Configuration Manager 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 set up 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 for your SSRS report creation.

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 client’s machine into the Configuration Manager database so that we can now use it to create an SSRS report.

Report AutoCAD Product and Serial Numbers using Configuration Manager

Assumptions

We are now assuming that you have gone through the above guide and that 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.

SQL Query Check for Data

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:

SELECT
*
FROM
AUTODESK_INVENTORY_DATA
WHERE
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 launch SSRS to go ahead and create the SSRS report.

Creating the SSRS Report Instructions

Again we are writing this guide on an assumption that you have at least a basic knowledge of SQL and SSRS report creation so if you do have any questions, please use our comments section 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.

Setting up the Data Source

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”

So you should end up with something similar to this:

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.

SQL Query for your SSRS 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:

SELECT
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]
FROM
AUTODESK_INVENTORY_DATA CAD
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 your SSRS report?

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

Once you have done all this, your dialog box should look similar to this:

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:

Setting up the SSRS Report Layout

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 yourself!

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 straightforward 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.

Allocate Data to the Matrix

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 SSRS 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!

More Queries for an SSRS Report

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.