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.

Nickolaj Andersen
Principal Consultant 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. Currently working for TrueSec as a Principal Consultant. 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 and user groups.

(16102)

comments
  • Joe
    Posted at 03:04 April 9, 2014
    Joe
    Reply
    Author

    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

  • TenkerBell
    Posted at 15:15 June 19, 2014
    TenkerBell
    Reply
    Author

    Nice posting. Thank you for sharing with us. Personally I like it.
    General Contractors Austin, TX

    • Nickolaj
      Posted at 23:00 June 19, 2014
      Nickolaj
      Reply
      Author

      No problem, that’s the reason with this blog. Sharing is caring 🙂

      Regards,
      Nickolaj

  • Kurt
    Posted at 19:23 September 15, 2014
    Kurt
    Reply
    Author

    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?

  • Rob
    Posted at 18:25 September 18, 2014
    Rob
    Reply
    Author

    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?

    • Nickolaj
      Posted at 12:52 September 21, 2014
      Nickolaj
      Reply
      Author

      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: http://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

  • Adrian
    Posted at 16:30 October 13, 2014
    Adrian
    Reply
    Author

    Probably the most complete walk through I’ve ever read. Thanks for the info.

    • Nickolaj
      Posted at 18:58 October 13, 2014
      Nickolaj
      Reply
      Author

      Hi Adrian,

      I’m glad that you’ve found this post useful, and thank you for the kind words!

      Regards,
      Nickolaj

  • Ihti
    Posted at 09:48 October 14, 2014
    Ihti
    Reply
    Author

    Hi,

    I found this really helpful and wish to thank you for this wonderful post.

    Keep up the good work!

    • Nickolaj
      Posted at 15:10 October 14, 2014
      Nickolaj
      Reply
      Author

      Hi Ihti,

      Thank you for the kind words! 🙂

      Regards,
      Nickolaj

  • JJ
    Posted at 21:28 October 15, 2014
    JJ
    Reply
    Author

    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

  • bala
    Posted at 11:24 November 5, 2014
    bala
    Reply
    Author

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

  • Pawan
    Posted at 15:39 March 26, 2015
    Pawan
    Reply
    Author

    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.

    • Nickolaj
      Posted at 14:35 March 28, 2015
      Nickolaj
      Reply
      Author

      You’re most welcome 🙂

      Regards,
      Nickolaj

  • Michael
    Posted at 01:45 May 27, 2015
    Michael
    Reply
    Author

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

  • Vince
    Posted at 13:51 June 9, 2015
    Vince
    Reply
    Author

    Excellent post.

    Can I ask how can I add totals to the bottom of a report?

    Vince

  • Suliman
    Posted at 09:15 June 16, 2015
    Suliman
    Reply
    Author

    Awesome post! Thank you..

  • Vova
    Posted at 19:54 August 16, 2015
    Vova
    Reply
    Author

    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.

  • AmirArsalan
    Posted at 18:10 June 29, 2016
    AmirArsalan
    Reply
    Author

    hi,this is look amazing!is it applicable for hardware inventory also?

  • Rohit Chaudhary
    Posted at 04:42 August 1, 2016
    Rohit Chaudhary
    Reply
    Author

    Thank-you

  • Evgeniy Bichev
    Posted at 13:14 November 9, 2016
    Evgeniy Bichev
    Reply
    Author

    Big thank your excellent work.

  • Michael
    Posted at 23:55 December 8, 2016
    Michael
    Reply
    Author

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

  • Michael
    Posted at 00:03 December 9, 2016
    Michael
    Reply
    Author

    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

  • Michael
    Posted at 00:07 December 9, 2016
    Michael
    Reply
    Author

    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,

  • Kevin L Sholder
    Posted at 16:19 April 19, 2017
    Kevin L Sholder
    Reply
    Author

    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.

  • Shyam Singh
    Posted at 15:37 May 28, 2017
    Shyam Singh
    Reply
    Author

    Nice Post, Thanks for Sharing

  • Bob
    Posted at 19:58 September 14, 2017
    Bob
    Reply
    Author

    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

  • Leave a Reply to Rohit Chaudhary
    Cancel Reply