If you haven’t read part 1 and part 2 of this article series yet, we recommend you do. Reading parts 1 and 2 are essential for proceeding part 3.
Moving past parts 1 and 2, 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, however this is an optional step. What we'll discuss next is setting up the SSRS report, but I always like to setup a SQL query in the SCCM console first. It’s a quick and easy way to figure out what’s in your database.
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, here is a great beginner's article entitled SCCM 2012 Reporting for dummies: Creating your own SSRS reports.
There are a few important changes made to my report, however. The first is the line specifying your site database name:
<DataSourceReference>/ConfigMgr_CON/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}</DataSourceReference>
The second is the line specifying your site server name:
<rd:ReportServerUrl>http://siteserver/reportserver</rd:ReportServerUrl>
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!
