MSEndpointMgr

Create Custom Reports for ConfigMgr 2012 with Report Builder

Reporting in ConfigMgr 2012 is a powerful way to get alot of information about almost everything in your environment. But sometimes the built in reports that Microsoft has provided is not enough. If you search on google for how to create a custom report you’ll get several great articles/posts on the subject. What most of them lack though is how to create the actual query part of the report. You could of course search the internet and hope that somene else has already published a query for exactly what you’re trying to accomplish, but most of the time you’ll not find what you’re looking for. In this post I’ll walk through the steps to create a custom report for usage in the ConfigMgr console, and the best part, you don’t need any knowledge on how to write SQL queries.

Overview

  • Environment setup
  • Create an in-console Query
  • Locate the SQL query statement
  • Create a custom report
  • Run the custom report

Environment setup

In order to use the Reporting functionality in ConfigMgr, you’ll need to have the Reporting Services Point Site System role installed on the SQL server for your Primary Site server. In my lab environment I’ve got a Primary Site server called CM01.contoso.com where the SQL server is running locally. In my case I’ve installed the Reporting Services Point on CM01.contoso.com. When you install the Reporting Services Point, it’s recommended to use a dedicated service account as the Reporting Services Point Account. The picture below shows that the Reporting Services Point Account is set to a domain account called CONTOSO\CM_RS.
76_1
Additionally my environment looks like this:

  • ConfigMgr 2012 R2
  • SQL Server 2012 SP1 with SQL Server Report Builder 3.0

Create an in-console Query

You may think, why are we creating an in-console Query first? The answer is simple, we will be using the this query to get the correct SQL query statement that is needed in order to create a custom report. In this post we’ll be creating a simple Query to get all devices with any version of Silverlight installed.
1. Go to the Monitoring node, right-click on Queries and select choose Create Query.
76_2
2. In the Name field, give the new query a name e.g. Devices with Silverlight.
3. Limit the collection to a collection that is suitable for your environment. In my lab I’ll just choose the All Systems collection.
4. Click on Edit Query Statement.
76_3
5. Click on Show Query Language and paste the following WQL query into the Query Statement field:

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Silverlight"

This post will not cover the basics of creating a Query. If you’ve never creating a query before, I suggest that you read this.
6. Click OK and then Next. Click Next again on the Summary page and then click Close.

Locate the SQL query statement

Now that we’ve created the query to get devices that has Silverlight installed, we need to get our hands on the SQL query statement. When you are working in the ConfigMgr console, almost anywhere you click you’re querying the database for information. This information is being collected by the SMS Provider and sent back to be shown in the console. So if we were to run our newly created query from the ConfigMgr console, we’d ask the SMS Provider to get the results from our SQL server. When we create a in-console Query, it’s being generated as a WQL query (WMI Query Language) which cannot be used to get information from a SQL server directly. Now, here’s the really awesome part. Since the SMS Provider is a WMI provider, the SMS Provider needs to interprete and convert the WQL query into a SQL query instead. This conversion can be found in the SMSProv.log file, located in C:\Program Files\Microsoft Configuration Manager\Logs if you’ve installed ConfigMgr 2012 in the default location.
1. In the Monitoring node, right-click on the newly created query that we called Devices with Silverlight and select Run.
76_4
2. Open SMSProv.log with CMTrace.exe and look for a row starting with Execute SQL. You may have to scroll up a bit to find it.
3. Compare the row with the WQL query we used earlier:
WQL:

select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Silverlight"

SQL:

select  all SMS_R_System.Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00,__System_ADD_REMOVE_PROGRAMS0.Version00 from vSMS_R_System AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey  INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey   where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 like N'Microsoft Silverlight'

If the row you’ve located seems to be the correct one, copy the contents of the whole row into e.g. Notepad.
76_5
4. In Notepad, remove the part where it says Execute SQL =. You now have the full SQL query statement which can be used to create custom reports. Save this SQL query statement for later usage.

Create a custom report

1. In the Monitoring node, expand Reporting, right-click on Reports and choose Create Report.
76_6
2. On the Information page select SQL-based Report as Type.
3. In the Name field, name the report e.g. Devices with Silverlight.
4. Click on Browse and select a folder to store the custom report. In this example I’ve choosen Software Distribution – Application Monitoring. This might not be the most appropiate place, but it will have to do. When ready, click Next.
76_7
5.  On the Summary page, click Next.
6. On the Completion page, click Close.
7. When you’ve clicked on close, Report Builder will launch.
76_8
8.  In the left pane, right-click on Data Sources and select Add Data Source.
76_9
9. In the Name field, enter ConfigMgr.
10. Select Use a shared connection or report model and click Browse.
76_10
11. Double-click on ConfigMgr_<site_code>, scroll to the end and select the item that looks like a GUID. Click on Open.
76_11
12.  Click on Test Connection to see if it works. If the connection was successfully established, you’ll see the following message:
76_12
13. In the Data Source Properties window, click on OK.
14. Right-click on Datasets and choose Add Dataset in the left pane.
76_13
15. In the Name field, enter SQLQueryStatement (can be whatever you want, but no spaces or special characters). Select Use a dataset embedded in my report. Select the ConfigMgr item from the Data source drop down. In the Query field, paste the saved SQL query statement from earlier.
76_14_1
16. Click on Refresh Fields. In the Enter Data Source Credentials window that pops up, enter the password for the Reporting Services Point Account (see note about this account in the beginning of this post). Click OK.
76_15
17.  At this point you’ll most likely get an error message like below. If you didn’t get an error, click OK and skip the next part and jump directly to step 18.
76_16
From the details of this error message, we can see that there is a permissions issue. In order to resolve this, we need to give the Reporting Services Point Account the permissions to read from the database. Leave all the Report Builder windows open, and launch SQL Server Management Studio on the SQL server and logon. Expand Security and Logins, right-click on the Reporting Services Point Account (in my lab environment that’s CONTOSO\CM_RS) and choose Properties.
76_17
In the left pane, select User Mapping. Under Database role membership for: CM_<site_code> put a check mark next to db_datareader and click OK.
76_18
Go back to the Dataset Properties window from before and click on Refresh Fields again. This time around nothing should really happen, which means that it’s working.
18. Click on Table or Matrix.
76_19
19. Click Next in the New Table or Matrix window (assuming that the correct dataset is choosen, see picture below) .
76_20
20. Now it’s time to arrange the fields from the SQL query statement in the dataset that we created earlier. There’s no right or wrong here really, it all comes down to how you want to display the results. As shown in the picture below, I’ve dragged and dropped all fields into the Values field. Click Next.
76_21
21. On the Choose the layout page, click Next.
22. On the Choose a style page, either choose the default style or select another. I’ve choosen Corporate. Click Finish. At this point we could continue to extend this report, perhaps insert some text above the table with the name of the report, but for this post we’ll not go further.
23. Click on the Report Builder button and choose Save.
76_22

Run the custom report

Now that we’ve successfully created our custom report, lets go ahead and see how it looks when we run it from within the ConfigMgr console.
1. Go to the Monitoring node. Expand Reporting and then Reports. Scroll down to Software Distribution – Application Monitoring (or the folder you’ve selected when you first created the report).
2. Right-click on the Devices with Silverlight and select Run.
76_23
3.  Report Viewer will now launch and the results from the SQL query statement should be visible as below:
76_24
As you can see, it works! But the layout and looks may need some customization. In order to make any modification to this report, simply right-click it and choose Edit. That will bring back Report Builder where you now can make changes to the report.

(60637)

Nickolaj Andersen

Chief Technical Architect and Enterprise Mobility MVP since 2016. Nickolaj has been in the IT industry for the past 10 years specializing in Enterprise Mobility and Security, Windows devices and deployments including automation. Awarded as PowerShell Hero in 2015 by the community for his script and tools contributions. Creator of ConfigMgr Prerequisites Tool, ConfigMgr OSD FrontEnd, ConfigMgr WebService to name a few. Frequent speaker at conferences such as Microsoft Ignite, NIC Conference and IT/Dev Connections including nordic user groups.

34 comments

  • An outstanding share! I have just forwarded this onto a co-worker who has been doing a little homework on this.
    And he actually bought me dinner simply because I found it for him…
    lol. So let me reword this…. Thanks for the meal!!
    But yeah, thanx for spending time to talk about this subject here on your web page.

  • Thank you for the post. It was a great one. I followed the instructions, but I could not get the report for the application: Respondus. Everything worked as you mentioned except no result for my application. Any thoughts? Thank you.

  • Great post – worked like a charm. Thanks very much – I have a book on SCCM but the SSRS part didn’t go much in depth and I was struggling with generating a report. This was just what I was looking for. Thank you Nickolaj

  • Great article, allowed me to easily create a report I’ve been working on for far too long. I do have one question. How do I add multiple applications to the same report? We are searching for any type of bittorrent application so we can get them removed from said systems.

  • This is the correct query from the log file:
    select all SMS_R_System.Name0,__System_ADD_REMOVE_PROGRAMS0.DisplayName00,__System_ADD_REMOVE_PROGRAMS0.Version00 from vSMS_R_System AS SMS_R_System INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey INNER JOIN _RES_COLL_SMS00001 AS SMS_CM_RES_COLL_SMS00001 ON SMS_CM_RES_COLL_SMS00001.MachineID = SMS_R_System.ItemKey where __System_ADD_REMOVE_PROGRAMS0.DisplayName00 like N’Microsoft Silverlight’
    And this is an error in SQL:
    Invalid object name ‘vSMS_R_System’.
    Thank you,

  • I got this from the log file:
    select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Microsoft Silverlight”
    Should i be able to run this in SQL Management?
    For some reason i am getting the following error:
    Invalid object name ‘SMS_R_System’.
    Thanks for the help

  • On step 5:
    select SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Microsoft Silverlight”
    If i run this inside SQL Management should it work? I am getting an error?
    Invalid object name ‘SMS_R_System’.

  • Excellent post, it help me to understand how sql report builder work.
    But I have an issue when I try to list all devices from a collection, will be cool if you can help me:
    I have a collection with 35 devices, when I create my query with only one class like “System Resource” it list all 35 devices, but once I add another class like “Computer system” it show me only 10 devices.
    Do you have any idea?
    Thank in advance.

  • Excellent post, what I really need for my task. Thank you for sharing with us.

  • Excellent post. Till now, everyone would show how to create queries… Was looking for an article which explains how to create SQL Report. here it is….. thanks alot.

  • Is it possible to write a query that includes device collection names, site names, computer name , and os type????? plshelp me

  • I am getting an error when pressing Refresh Fields: An item with the same key has already been added. The query I created works fine. Is there truly a duplicate in here? I’m not a SQL junkie so I don’t see any issues. Thanks!
    select all SMS_Query.QueryKey,SMS_Query.Comments,SMS_Query.WQL,SMS_Query.CollectionID,SMS_Query.Name,SMS_Query.QueryKey,SMS_Query.Architecture from Queries AS SMS_Query where (SMS_Query.QueryKey in (select all Folder##Alias##810314.InstanceKey from vFolderMembers AS Folder##Alias##810314 where (Folder##Alias##810314.ObjectTypeName = N’SMS_Query’ AND Folder##Alias##810314.ContainerNodeID in (16777265))) AND SMS_Query.Architecture N’SMS_StatusMessage’) order by SMS_Query.Name

  • Hi,
    I found this really helpful and wish to thank you for this wonderful post.
    Keep up the good work!

  • Thanks so much for this awesome tutorial. One question I have is that, I am trying to find the program installed on all my servers. While we have a couple of 2000 and 2003 servers, the bulk of our servers are 2008 R2 and 2012 servers where Add/Remove Programs is called Programs and Features. Is there a way I can look for one or the other?

    • Hi Rob,
      If you enable hardware inventory for your clients, even if they’re Windows Server 2008 R2 or 2003 (according to the documentation, Windows Server 2000 is not supported with Configuration Manager 2012: https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SupConfigClientOS ), all data from installed application will be available in a table in the database. Take a look at the ‘dbo.v_GS_ADD_REMOVE_PROGRAMS’ and ‘dbo.v_GS_ADD_REMOVE_PROGRAMS_64’ views in the database, these should be able to give you the data you may be looking for.
      Regards,
      Nickolaj

  • Question on the above work…can you also bring in other data sources to SCCM 2012? For example, if I had location data or people data I know I could relate to the existing reports or build a custom report with SCCM…can I integrate another source?

  • Hi,
    This is awesome! thanks for sharing the information.
    Just wondering how to create a custom reports to have some fields when you’re running a report? If you have an email, I could send you a screen shot what I meant.
    Many Thanks

Sponsors