If you haven’t read part 1 and part 2 of this article series yet I recommend you do. Reading part 1 & 2 are essential for this next part because I go over the first few steps in order to get you to this point.
I now have all my clients using my new inventory classes I created. I’ve verified with a few clients that they are successfully sending this data to the site server. What’s next? The next piece of this process is to query the database. This is an optional step. If you want, you can jump down a little bit to setting up the SSRS report but I always like to setup a WQL query in the SCCM console first. It’s a quick and easy way to figure out what’s in your database.
Create a SCCM query (Optional)
I’ve already created a query for you that allows you to quickly see which clients are reporting in. I’m providing this to you as an exported SCCM MOF file. If you’re not familiar with exporting and importing objects, check out this great Technet article. It goes over the basics of what you need to get my OSD History query object into your database. Once you get this query imported into your database, you should now see an OSD History query show up in the Queries pane of your SCCM console.
Run it and you should get an output like this.
Create the SQL Query
If you do, you know the clients are properly reporting the required information into the database. The next and final step is getting our pretty report generated. To create a SSRS report that your boss is going to love that you’re going to need to build a SQL query. The whole purpose of this report is to get an eyeball on how many images we’re throwing down to our clients within a specific timeframe. I’ve chosen to display imaging activity by month. Luckily, since we’re just looking for counts here we really only need 1 value; the date in which the image was applied. Once we’ve got this it’s then just a matter of counting all the instances and grouping them by the month.
The most important part of this report is the query you need to gather the data. I’ve provided it below. This SQL query gathers all of the clients that have either have the CM_DSLID0 value set (for SCCM images) or the DeploymentMethod0 value set (for MDT images). I’m then grouping these clients by month and ordering by the date.
Create the SSRS Report
The final step is to actually create the SSRS report itself. If you’re not familiar with creating SCCM SSRS reports I’m not going to go into detail here. I found a great beginner article entitled SCCM 2012 Reporting for dummies: Creating your own SSRS reports that’s great for SSRS beginners.
To save you some time I’ve exported my report. Before you go importing this though you need to make 2 changes. The first is the line specifying your site database name.
The second is the line specifying your site server name.
Once you’ve made those changes it’s just a matter of uploading the RDL to whatever server you have the Reporting Services point installed and running the report!
If everything went right, the final product in all its glory will look like this.
It’s a beautiful thing, isn’t it?