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.

26 Comments

  1. Joe

    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

    Reply
    1. NickolajNickolaj (Post author)

      Hi Joe,

      Thanks for the kind words!

      Have you seen this post?

      http://www.scconfigmgr.com/2014/02/12/create-a-custom-report-with-parameters-for-configmgr-2012/

      Regards,
      Nickolaj

      Reply
  2. TenkerBell

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

    Reply
    1. NickolajNickolaj (Post author)

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

      Regards,
      Nickolaj

      Reply
  3. Kurt

    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?

    Reply
    1. NickolajNickolaj (Post author)

      Hi Kurt,

      I’ve never tried that myself, but you’re able to create Data Sources in the Report Builder. So why not give it a shot?

      Perhaps this resource can provide you with some assistance:

      http://www.mssqltips.com/sqlservertutorial/2107/report-builder-data-sources/

      Regards,
      Nickolaj

      Reply
  4. Rob

    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?

    Reply
    1. NickolajNickolaj (Post 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

      Reply
  5. Adrian

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

    Reply
    1. NickolajNickolaj (Post author)

      Hi Adrian,

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

      Regards,
      Nickolaj

      Reply
  6. Ihti

    Hi,

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

    Keep up the good work!

    Reply
    1. NickolajNickolaj (Post author)

      Hi Ihti,

      Thank you for the kind words! 🙂

      Regards,
      Nickolaj

      Reply
  7. JJ

    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

    Reply
  8. bala

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

    Reply
  9. Pawan

    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.

    Reply
    1. NickolajNickolaj (Post author)

      You’re most welcome 🙂

      Regards,
      Nickolaj

      Reply
  10. Michael

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

    Reply
  11. Vince

    Excellent post.

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

    Vince

    Reply
  12. Suliman

    Awesome post! Thank you..

    Reply
  13. Vova

    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.

    Reply
  14. AmirArsalan

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

    Reply
  15. Rohit Chaudhary

    Thank-you

    Reply
  16. Evgeniy Bichev

    Big thank your excellent work.

    Reply
  17. Michael

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

    Reply
  18. Michael

    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

    Reply
  19. Michael

    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,

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *