Hi there, I am Matt Balzan and I am a Microsoft PFE with the Windows and Devices team, in the UK. Today I am going to show you how to manage and troubleshoot the collections that are affecting the performance on your site.
In my previous blog (LINK HERE) I created a dashboard report to help you monitor your collections and identify any issues with collection evaluation time plus cross examine SQL queries and identify none or poorly assigned Limiting Collections in the collection membership rules.
First I would advise to run the above report and identify the count of RED rows in the EVALUATION TIME 20 SECS AND ABOVE section, clicking this section will jump to the table to the list of collections marked as RED.
If you have no RED row counts, you can then move on to the EVALUATION TIME 10 TO 20 SECS YELLOW rows section, clicking this section will jump to the table to the list of collections marked as YELLOW.
To help you out I have written sections with scripts and SQL scripts to identify the poorly written queries / designed collections and how to remedy them.
Use the script below to start cleaning up your collections!
In order to run the script make sure you have the following parameter values ready so that you can run it from a PowerShell command Line:
.\collhouse.ps1 -SiteCode CS1 -FilterColl test -CollExpPath c:\test
-SiteCode > This is the site code of your SCCM site server.
-FilterColl > This is the filter keyword for the collection(s) you are searching for.
-CollExp_Path > The folder where you would like to export your collections if you choose menu option 1 or 2.
COPY THIS SCRIPT AND SAVE IT AS COLLHOUSE.PS1 # Begin of script
<# Disclaimer The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts 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 scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts 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. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Connects to SCCM site and runs a query using collection filter + Then it parses the Schedule hex key and shows the expanded results + Finally launches a menu to housekeep collections. + MATT BALZAN (PFE) - v1.0 20/1/2020
+ Example: .\collhouse.ps1 -SiteCode CS1 -FilterColl test -CollExpPath c:\test #>
param(
# Site code of your SCCM environment (eg. CS1) [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=0)] [ValidateNotNullOrEmpty()] [ValidateLength(3,3)] [String] $sitecode,
# Collection filter (eg. test) [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=1)] [ValidateNotNullOrEmpty()] [String] $filtercoll,
# Path to export collection (will be exported as a mof file) [Parameter(Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true, Position=2)] [ValidateNotNullOrEmpty()] [String] $collexp_path )
# Customizations $initParams = @{} #$initParams.Add("Verbose", $false) # Uncomment this line to enable verbose logging #$initParams.Add("ErrorAction", "Stop") # Uncomment this line to stop the script on any errors
# Import the ConfgurationManager module and connect to site code provider Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" Set-Location $sitecode":"
function Show-Menu { Write-Host "____________________________[ COLLECTION MAINTENANCE OPTIONS ]____________________________" -ForegroundColor Cyan Write-Host " RESULTS:" $Collections.count "FILTER: $coll" "OUTPUT FILE: $collexp_path"-ForegroundColor Green "" Write-Host "Press 1 to EXPORT ALL COLLECTION RESULTS" -ForegroundColor Yellow Write-Host "Press 2 to EXPORT A SPECIFIC COLLECTION" -ForegroundColor Yellow Write-Host "Press 3 to SET REFRESH TYPE TO [NONE] TO ALL COLLECTION RESULTS" -ForegroundColor Yellow Write-Host "Press 4 to REMOVE ALL COLLECTION RESULTS" -ForegroundColor Yellow Write-Host "Press Q to QUIT" -ForegroundColor Gray Write-Host "___________________________________________________________________________________________________" -ForegroundColor Cyan "" }
$Collections = Get-CMCollection | ? {$_.Name -like "*$filtercoll*"} Foreach($Collection in $Collections) { "" "________________________________________________________________________________________" Write-Host $($Collection.Name) -ForegroundColor Cyan "COLLECTION ID: "+$Collection.CollectionID "LIMIT TO COLLECTION NAME: "+$Collection.LimitToCollectionName "MEMBER COUNT: "+$Collection.MemberCount "REFRESH TYPE: "+$Collection.RefreshType "CURRENT STATUS: "+$Collection.CurrentStatus "REFRESH SCHEDULE: [DAYS] "+$Collection.RefreshSchedule.DaySpan + " [HOURS] "+$Collection.RefreshSchedule.HourSpan+ " [MINS] "+$Collection.RefreshSchedule.MinuteSpan
} ""
# functions to export multiple collections or a specific collection, set refresh type and/or remove the collection. function ExportCollections {
$Collections | Where-Object { $CollExportPath = $collexp_path + "\$($_.Name)"+".mof"; "Exporting " +$_.Name + " to " +$CollExportPath;Export-CMCollection -CollectionId $_.CollectionID -ExportFilePath $CollExportPath -Confirm}
}
function ExportCollection { $collID = Read-Host "Enter the Collection ID" $CollExportPath = $collexp_path + "\$collID.mof" "Exporting " +$collID + " to " +$CollExportPath Export-CMCollection -CollectionId $collID -ExportFilePath $CollExportPath -Confirm }
function SetRefreshType { $Collections | Where-Object {"Setting Refresh Type to NONE on: " + $($_.Name); Set-CMCollection -CollectionId $_.CollectionID -RefreshType None -Confirm } }
function RemoveColl { $Collections | Where-Object { "Removing Collection: " + $($_.Name); Remove-CMCollection -Id $_.CollectionID -Force -Confirm } }
do { Show-Menu $input = Read-Host "Please make a selection" switch ($input) { '1' { ExportCollections }'2' { ExportCollection } '3' { SetRefreshType } '4' { RemoveColl } 'q' { cd c: return } } pause } until ($input -eq 'q')
# end of script
|
When you run the script it will connect to your site code provider and run the query based on your keyword filter, it will return any results it finds followed by a menu like this:
The menu will give you 5 options to choose from:
Press Q to Quit
Selecting option 1 will export all the returned collections to the specified $collexp_path defined in your script. The result will be a .mof format which can be used to import the collection if required in the future.
Selecting option 2 will export a specific collection to the specified $collexp_path defined in your script. You will be prompted for the Collection ID. The result will be the same as option 1.
Selecting option 3 will change the Refresh Type setting to NONE on all the returned collections.
Selecting option 4 will remove all the returned collections.
NOTE! All the above options are enforced with a –Confirm parameter switch which will prompt you to click: Yes | Yes To All | No | No To All | Suspend
Selecting option Q will quit the script from running.
(Make sure you run this in your test environment before using in your live, production environment!)
Time and time again I have visited customers where I discover collections using ALL SYSTEMS as their LC.
This is a big No-No! You are adding to the performance lag on the SQL server and affecting the entire Collection Evaluation process.
By setting up limiting collections that query on, for example, “Windows 10” devices - that will only run against the db for these machines and return the results faster.
So what can we do to check and remediate?
First you will need to install SQL Server Management Studio so that you can run your troubleshooting SQL queries. You will also need the correct access to run these queries (ask your DBA to facilitate this!).
Run this query to show all your Limiting Collections and their Collection IDs:
SELECT SiteID AS 'Collection ID',CollectionName,
CASE LimitToCollectionID
WHEN 'SMS00001' THEN 'All Systems'
END AS 'Limiting Collection'
FROM v_Collections_G
WHERE LimitToCollectionID = 'SMS00001'
AND SiteID NOT LIKE 'SMS%' --<-- this part skips the built in collections
The example output from my lab displays the following results:
Uh oh! These collections are targeting ALL SYSTEMS as their LC! Now I know I need to fix these collections to be limited against meaningful limited collections.
Awesome link: https://docs.microsoft.com/en-us/configmgr/core/servers/manage/create-queries
You can also use this query to analyse the collection’s Limiting Collection and their Membership Rule using it’s Collection ID.
DECLARE @SRCID nvarchar(10)
SET @SRCID = 'XXXXXXXX' -- <<-- Change this value to your Limiting Collection ID
SELECT DISTINCT SiteID AS 'Collection ID',CollectionName,(Select Name FROM v_Collection WHERE CollectionID = @SRCID) AS 'Source Collection Name',
LimitToCollectionID,
(SELECT Case
WHEN vcd.relationshiptype = 1 THEN 'Limited'
WHEN vcd.relationshiptype = 2 THEN 'Include'
WHEN vcd.relationshiptype = 3 THEN 'Exclude'
END
) AS Relationship
FROM v_Collections_G vg
LEFT JOIN vSMS_CollectionDependencies vcd on vcd.DependentCollectionID = vg.SiteID
WHERE vg.LimitToCollectionID = @SRCID
ORDER BY CollectionName DESC
The above script will help you identify which Limited Collections are included or limited – now you can back track and decide whether they should be present or not
Here you can see a collection I diagnosed from a customer site had an incremental update and no deployment assigned to it - plus its WQL query was horrendous! Was it being used for reporting all kinds of installed SQL software or returning all versions of SQL Servers? It should have been placed in a report rather than a collection. We will never know why, but the harsh reality was that it took over an hour to finish. It often timed out and would then run again, causing a backlog to the collection evaluator.
TIP: On your Primary server, open the colleval.log and look for the following entries as tell-tale giveaways:
02-11-2019 12:15:20.347 SMS_COLLECTION_EVALUATOR 21060 (0x5244) PF: Refreshing collection CS1010C6
The incremental update definitely has to go and the full update only runs outside core business hours.
From the SCCM Collection Dashboard report, this showed up as RED in the EVALUATION TIME 20 SECS OR ABOVE.
I clicked on the RED section which drills into the report and found the collection in the table, which in turn showed me the WQL & SQL query.
I copied the SQL query from the table and ran it in the SSMS:
--DO NOT RUN THIS IN YOUR ENVIRONMENT – THIS IS TO DEMONSTRATE ONLY!
SELECT ALL VRS.itemkey,
VRS.discarchkey,
VRS.name0,
VRS.sms_unique_identifier0,
VRS.resource_domain_or_workgr0,
VRS.client0
FROM v_r_system AS VRS
INNER JOIN add_remove_programs_data AS __System_ADD_REMOVE_PROGRAMS0
ON __System_ADD_REMOVE_PROGRAMS0.machineid = VRS.itemkey
INNER JOIN add_remove_programs_64_data AS __tem_ADD_REMOVE_PROGRAMS_641
ON __tem_ADD_REMOVE_PROGRAMS_641.machineid = VRS.itemkey
WHERE ( __System_ADD_REMOVE_PROGRAMS0.displayname00 LIKE N'%SQL Server%'
OR __tem_ADD_REMOVE_PROGRAMS_641.displayname00 LIKE N'%SQL Server%' )
Finally after over an hour these were the results and messages:
Bear in mind this runs incrementally and eventually times out. So there is no way this collection will ever populate, or finish in time to evaluate!
Also notice how the above script is not using Select Distinct SQL syntax and is searching for all devices that contain the word ‘SQL Server’ in the Add/Remove Programs view. This is true whether the system is x86 or x64.
The most efficient way is to split up the queries and make sure they use Select Distinct in both queries. Then add them as separate queries in the Membership Rules of its collection.
I ran both my queries separately and both took 5 and 1 seconds to run respectively:
--X86 query
SELECT DISTINCT vrs.itemkey,
vrs.discarchkey,
vrs.name0,
vrs.sms_unique_identifier0,
vrs.resource_domain_or_workgr0,
vrs.client0,
ARP.displayname0
FROM v_r_system
INNER JOIN v_gs_add_remove_programs AS ARP ON ARP.resourceid = vrs.itemkey
WHERE ARP.displayname0 LIKE 'Microsoft SQL Server 20%'
--X64 query
SELECT DISTINCT vrs.itemkey,
vrs.discarchkey,
vrs.name0,
vrs.sms_unique_identifier0,
vrs.resource_domain_or_workgr0,
vrs.client0,
ARP64.displayname0
FROM v_r_system
INNER JOIN v_gs_add_remove_programs_64 AS ARP64 ON ARP64.resourceid = vrs.itemkey
WHERE ARP64.displayname0 LIKE 'Microsoft SQL Server 20%'
Avoid querying multi-architecture systems and split them up – the results will speak for themselves.
GEEK OUT TIP: If you set the SQL command: SET STATISTICS IO,TIME ON before your SQL script, you will be able to look deeper into how the query is being processed. (Use this for analysis only and run SET STATISTICS IO,TIME OFF when you are done testing.)
An example output is shown below:
--List of collections where value for LIKE operator starts with '%
SELECT Name, C.CollectionID, QueryExpression
FROM v_CollectionRuleQuery AS CQ
INNER JOIN v_Collection AS C ON CQ.CollectionID = C.CollectionID
WHERE QueryExpression LIKE '%like%''[%]%'
ORDER BY collectionID DESC
-- Collections that have been suspended
SELECT * FROM sys.sysprocesses WHERE program_name LIKE '%Collection%' AND status LIKE '%suspended%'
-- This query will find "bad" collections where query has a filter starting with a wildcard. For example:
SELECT * FROM v_R_System WHERE Name0 like '%abcdef'
-- List of collections with NOT LIKE operator
SELECT Name, C.CollectionID, QueryExpression
FROM v_CollectionRuleQuery AS CQ
INNER JOIN v_Collection AS C ON CQ.CollectionID = C.CollectionID
WHERE QueryExpression LIKE '%NOT LIKE%'
ORDER BY collectionID DESC
-- Number of Query Membership Rules per collection
SELECT COLL.Name, COLL.CollectionID, Count(*) AS [# MEMBERSHIP RULES]
FROM v_CollectionRuleQuery AS CRQ
INNER JOIN v_Collection AS COLL ON CRQ.CollectionID = COLL.CollectionID
GROUP BY COLL.Name, COLL.CollectionID
ORDER BY [# MEMBERSHIP RULES] DESC
-- When will the collections next update?
SELECT * FROM Collection_EvaluationAndCRCData ORDER BY NextRefreshTime
-- Grab the SQL & WQL query of a specific collection
SELECT CollectionName, WQL, SQL, vc.SiteID, MemberCount, RefreshType
FROM v_collections vc
INNER JOIN collection_rules_sql s ON vc.collectionid = s.collectionid
WHERE CollectionName LIKE 'TEST%'
(In this case, look for "spCollBeginIncEvaluation: Too many changes, marking incremental collections for full evaluation" in CollEval.log)
RECOMMENDED READING / RESOURCES TO HELP YOU OUT
Collection Evaluation tool: https://docs.microsoft.com/en-us/configmgr/core/support/ceviewer
Best Practices: https://docs.microsoft.com/en-us/sccm/core/clients/manage/collections/best-practices-for-collections
SQL T-sql query guide: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017
WQL(SQL) for WMI: https://docs.microsoft.com/en-us/windows/win32/wmisdk/wql-sql-for-wmi
Perf guide for Collections: https://docs.microsoft.com/en-us/configmgr/core/plan-design/configs/site-size-performance-guidelines...
Hope this helps and happy collection troubleshooting!
Matt Balzan | Premier Field Engineer | SCCM, Application Virtualisation
DISCLAIMER
The sample files/scripts 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.