WQL Query Optimization & Collection Evaluation Run Time

As an engineer working with CM, it is likely you will find yourself running SQL reports to identify collections that may potentially be utilizing sub-optimal query-based membership rules. If these queries have been poorly written, it can waste valuable system resources. If you modify and optimize these queries, it would behoove you to validate (and quantify) the reduction in query run time. One way to do this would be to take a look at the collection evaluation full run time. If the run time has not reduced significantly, this query should be reevaluated.

The collection evaluation full run time is able to be viewed in CM, but for efficiency’s sake, I decided to write a script to generate a .CSV report based on a list of collection IDs. This script also handles the conversion from milliseconds to seconds for convenience. Please take note that this must be ran from your desired CM site, and that must be specified at <Insert Site Location>.

# Identify location of script
$ScriptPath = Split-Path ($MyInvocation.MyCommand.Path) -Parent

# Set collection ID list location
$AllCollIDs = Get-Content "$ScriptPath\IDList.txt"

# Install/check for ConfigurationManager module
try {
    Import-Module ConfigurationManager -ErrorAction 'Stop'
    Set-Location <Insert Site Location>
}
catch [System.IO.FileNotFoundException] {
    throw 'The ConfigurationManager module cannot be found.'
}

# Run through list of IDs
$Output = foreach ($ID in $AllCollIDs) {

    # Get duration from CollEval & convert to seconds
    $Length = [Math]::Round((Get-CMCollectionFullEvaluationStatus -ID $ID | Select-Object -ExpandProperty Length)/1000)

    # Create table
    [PSCustomObject]@{
        Name =   $ID
        Length = $Length
    }
}

# Revert to local
Set-Location $env:SystemDrive

# Write output to .CSV at parent directory
$Output | Export-Csv -Path "$ScriptPath\CollEvalRunTime_Output.csv" -NoTypeInformation