SCCM: COLLECTION DASHBOARD REPORT


Hello! My name is Matt Balzan and I am a PFE with the Windows and Devices team, in the UK. I was recently at a customer where they asked me if I could analyse why their collections were taking so long to evaluate (at least 4 hours!).

After digging about in their database I found they had way too many collections with Incremental Updates or collections with hardly any members in them - this resulted in a massive performance hit on their database.

In total there were:

  • over 11000 collections
  • over 8000 with fewer than 5 members or less
  • some crazy sql queries taking over an hour long to run - not good! 🙁

As we know SCCM logs everything in logfiles and its own SQL database. Therefore all that was required was to create a dashboard to display the following information:

  • total collections in environment (CAS or Primary standalone)
  • collections with less than 5 members (this links to a sub-report)
  • all sites collections taking less than 10 seconds to evaluate
  • all sites collections taking between 10 & 20 seconds to evaluate
  • all sites collections taking longer than 20 seconds to evaluate
  • highest collection modifier
  • weekly collection modifiers and their totals
  • collection refresh types count
  • WQL & SQL query script used per collection and more...

And here it is. See content and use explained below.

 

TOP & MID SECTION

This report is split into 3 parts:

The TOP section displays the evaluation times which range from 0-10, 10-20 and 20 and above in seconds. The yellow and red charts are hyper-linked to the tables shown in the bottom section.

The MID section displays the Collection Refresh Types, Total Collections and Collections with less than 5 members, the Highest Collection Modifier plus some modifier details. The Collection Refresh Types is also hyper-linked to table in the BOTTOM section.

 

 

BOTTOM SECTION

Tables displayed:

  • COLLECTION EVALUATIONS >20 SECS
  • COLLECTION EVALUATIONS 10-20 SECS
  • COLLECTION INCREMENTAL & FULL SCHEDULED REFRESH TYPES

All tables include a legend with the following detail:

S: Site Code | ID: Collection ID | CN: Collection Name | MC: Member Changes | M: Member Count | LMCT: Last Member Change Time | CS: Current Status | Q: Query | T: Time Evaluation (seconds)

 

 

The following table shows the legend with this detail:

ID: Collection ID | CN: Collection Name | LCN: Limit To Collection Name | M: Member Count | RF: Refresh Type

 

SUB REPORT – COLLECTIONS LESS THAN 5 MEMBERS

This report will show you:

  • total of collections with 5 members or less
  • chart showing you all the collections with their member count total
  • table with a legend showing the following detail:

ID: Collection ID | CN: Collection Name | M: Member Count | CT: Collection Type | CS: Current Status | LCN: Limit To Collection Name | RF: Refresh Type

 

 

HOW TO USE THESE REPORTS

  1. Upload both reports in same root folder/path in your SSRS server.
  2. Update your datasource for both rdl reports.
  3. Ensure you have SELECT permissions for the tables mentioned below (or you or your DBA can use the sql script below).

 

GRANT SELECT ON [Collection_Rules_SQL] TO [smsschm_users]

GO

 

GRANT SELECT ON [Collections_L] TO [smsschm_users]

GO

 

GRANT SELECT ON [Collections_G] TO [smsschm_users]

GO

 

Download the reports from here: [Collection_Dashboard_Reports]

 

TIPS

  1. Using the Report Builder, you can set the report to auto refresh by applying the setting in seconds:

 

 

  1. Also, check above if your Language is set accordingly to ensure the correct date/time format! Since I am in the UK, mine is set to en-GB

 

Now you can start troubleshooting those problematic collections, begin tackling the ones that are taking too long to evaluate and investigate why the SQL/WQL queries are taking too long to return the desired data.

Hope this helps with your SCCM Collection housekeeping! In the next blog I will show you how to fix the issues with your collections and the best practices to keep your environment in good shape.

😊

++Special kudos to Ross Moore & Doug Varner for testing this in their environments!

DISCLAIMER
The sample files are not supported under any Microsoft standard support program or service. The sample files are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample files and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the files be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.

Matt Balzan | Premier Field Engineer | SCCM, Application Virtualisation

 

Comments (8)
  1. sno27 says:

    DOPE !!!!! Love this and its free. SCCM NEVER DIES!

  2. JMAC303 says:

    This looks awesome! I’m having trouble configuring it for my environment. Admittedly, I don’t know much about SQL reporting. What do I update the Data Source to?

    1. Matt Balzan says:

      After you upload the .rdl files to your Report Server, they will appear listed in the browser window. If you hover the cursor over the report, the yellow dropdown icon appears, click on it and select “Manage” from the list of options.
      Now the properties of the report will appear, click the next option down called “Data Sources”, click on the Browse button in the right pane where “A shared data source” is and scroll down right to the bottom of the screen to find your data source GUID. It should like this: {5C6358F2-4BB6-4a1b-A16E-8D96795D8602}
      Select this GUID and click on the Apply button. If done correctly you should see that the Data Source will point to: /ConfigMgr_/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}
      Now do the same for the other .rdl file and you are good to go!

  3. Awesome !!! It would make many environment stable 🙂

  4. Matt Balzan says:

    Folks, I have updated the reports with a different No Data Message in the sections where no rows are returned by SQL so it makes the report more user friendly – the zip file is now called Reports2.zip

  5. TrevorJones says:

    Nice work, thanks for posting!

  6. TheGambler01 says:

    Well done, Matt. Superb summary and detail on this. Extremely helpful in identifying problematical Collections.

  7. Ben_22 says:

    11/10 – awesome work…. so good it should get integrated into the product upon next release.

Comments are closed.

Skip to main content