First published on TECHNET on Mar 04, 2019 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.
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
Upload both reports in same root folder/path in your SSRS server.
Update your datasource for both rdl reports.
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]
GRANT SELECT ON [Collections_L] TO [smsschm_users]
GRANT SELECT ON [Collections_G] TO [smsschm_users]
[Edit: 08/06/2021] - You can download the reports located at the end of this blog!
Using the Report Builder, you can set the report to auto refresh by applying the setting in seconds:
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