SCCM: Housekeeping Collections

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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.

CATCH UP FROM EARLIER BLOG (SCCM COLLECTION DASHBOARD)

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.

HOW TO START TROUBLESHOOTING COLLECTIONS

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.

HOW TO MAINTAIN COLLECTIONS

What is the Member Count and when was it last updated? Monitor the trend for this collection or ask the creator of the collection to see if you can retire it.
Is the collection outdated or not being used anymore? Export or remove it!
Some collections could be migrated from an earlier version of SCCM, which will probably never get used again.
Are the incremental updates set on the collection to a suitable time frame?
Do these collections contain less than 5 or no devices at all? Get rid of 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 }

}

{

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:

Export ALL Collection results
Export a SPECIFIC Collection
Set Refresh Type to NONE to ALL Collection results
Remove ALL Collection results

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!)

HOW TO REVIEW LIMITING COLLECTIONS

What is the limiting collection (LC)?
Is this LC being properly targeted at your collections in question?

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

HOW TO REVIEW LIMITING COLLECTIONS RELATIONSHIPS

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

HOW TO REVIEW WQL QUERIES IN COLLECTIONS

Find out what is the purpose of the WQL script running on the collection.
Can it be fine-tuned to run more efficiently? Run it in SSMS and check out the timing results.
What is the actual purpose of the collection? Does it really need to be there or can you use the built-in reports?
Why is the incremental updates being used for the collection? Try to run important collections for up-to-date results during out of band hours – fine-tune the schedule so as to avoid running it during core business hours.

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

Collection ID CS1010C6 never completes evaluation

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:

USEFUL SQL QUERIES TO QUICKLY TROUBLESHOOT YOUR COLLECTIONS

--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%'

COLLECTION BEST PRACTICES AND RECOMMENDATIONS

Remove the uninstall collections from the incremental evaluation cycle. Run a schedule that runs outside core business hours.

Ensure that the uninstall collections do not list every device on the estate.

For the uninstall collections create a query to list the devices that have the application installed and the device/user is no longer a member of the AD group.

Investigate collections that have not updated their membership for over two weeks and remove them from the incremental evaluation cycle.

AVOID running Manual Evaluations on the All Systems collection in all hierarchal sites.

Create limiting collections to use as a parent instead of the All Systems (e.g. all desktop and laptop clients, all Servers, Windows 10 Thin Clients etc.)

Remove any large collections memberships except the new limiting collections.

Remove [or archive] collections that are no longer required, app install collections with no members.

Check to see if the collection really needs to be incremental – enforce a process to police this.

Check the queries on the collections are not contributing to the long evaluation time. (Use the troubleshooting script mentioned above).

Check the Temp DB on your site and see if the file size is ballooning – this is a dead give away that your performance is being affected by poorly managed collections.

Get rid of old collection queries that check for installed software (and are most probably running on incremented frequencies) and design compliance configuration items as this will definitely take the load away from your site servers.

Do live reporting via CM Pivot, more info here: https://docs.microsoft.com/en-us/sccm/core/servers/manage/cmpivot

Don’t create any unnecessary collections for reporting and instead use the built-in reports and insights in the SCCM console.

If the number of Resource IDs multiplied by the Number of Incrementally-enabled collections > 10 000 000 then Collection Eval starts Full Evaluation

(In this case, look for "spCollBeginIncEvaluation: Too many changes, marking incremental collections for full evaluation" in CollEval.log)

Do not enable incremental updates on collections targeting views with frequently changing data, for example: v_CH_ClientSummary.

Incremental updates do not work with CI compliance tables.

Reduce your number of incremental collections to less than 200.

Finally, all collections incur a full membership calculation, starting with All Systems and All Users, at 4AM local server time every day. This also occurs on any SMS_Executive service startup. So, if the customer runs an SCCM backup nightly at midnight, which stops SMS_Executive for part of the run time, and that completes at 2AM, all collections update at 2AM *and then again* at 4AM. Having just a SQL backup in place for weeknights avoids the service restart and the unneeded/redundant calculation of all collection membership.

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#collections

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.

CATCH UP FROM EARLIER BLOG (SCCM COLLECTION DASHBOARD)

HOW TO START TROUBLESHOOTING COLLECTIONS

HOW TO MAINTAIN COLLECTIONS

HOW TO REVIEW LIMITING COLLECTIONS

HOW TO REVIEW LIMITING COLLECTIONS RELATIONSHIPS

HOW TO REVIEW WQL QUERIES IN COLLECTIONS

USEFUL SQL QUERIES TO QUICKLY TROUBLESHOOT YOUR COLLECTIONS

COLLECTION BEST PRACTICES AND RECOMMENDATIONS

Leave a Reply Cancel reply