How to identify ConfigMgr collections that take long to refresh

I’ve put together the below PowerShell script this week to identify collections in ConfigMgr that require the longest time to refresh. If you ever experience a decrease in ConfigMgr collection update performance, you might want to run this script to find potential collections that have a long refresh duration.


Function Get-CMCollectionRefreshDuration
   Get-CMCollectionRefreshDuration displays the duration of Configuration Manager refresh cycles
   Inefficient queries can cause long collection refresh cycles. Use the Get-CMCollectionRefreshDuration
   cmdlet to identify collections with long refresh cycles. 
   Get-CMCollectionRefreshDuration -DataSource sqlsrv01\instance1 -Database CM_DB1

    Collection                   EvaluationStartTime          LastRefreshTime              Duration
    ----------                   -------------------          ---------------              --------
    Collection1                  30.08.2014 09:20:11          30.08.2014 09:22:45          00:02:33
    Collection2                  30.08.2014 09:27:31          30.08.2014 09:30:05          00:02:33
    Collection3                  30.08.2014 07:01:23          30.08.2014 07:03:56          00:02:33
    Collection4                  30.08.2014 07:20:38          30.08.2014 07:23:11          00:02:33
    Collection5                  30.08.2014 07:05:00          30.08.2014 07:07:33          00:02:33 

   Get-CMCollectionRefreshDuration -DataSource sqlsrv01\instance1 -Database CM_DB1 -ShowTopCollections 100

   Lists the top 100 collections
   The name of the SQL Server that hosts the configuration manager database


   The database name of the configuration manager database

.PARAMETER ShowTopCollections
   The number of collections to show that have the longers collection refresh cycle duration

     HelpMessage="Enter the SQL Server datasource name <server\instance>")]
     HelpMessage="The database name of the ConfigMgr database")]
     HelpMessage="The number of top collectons to show")]

    # connecting to SQL server
    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $Connection.ConnectionString = "Data Source=$DataSource;Integrated Security=True"
    Catch [Exception]
        write-output "Unable to connect to $DataSource"
        Write-Output $_.Exception
    $query = "
    SELECT TOP $ShowTopCollections
        CAST([LastRefreshTime] - [EvaluationStartTime] as datetime) as Duration
    FROM [$Database].[dbo].[Collections]
    ORDER BY CAST([LastRefreshTime] - [EvaluationStartTime] as datetime) DESC"

    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $result = $command.ExecuteReader()

    $table = new-object “System.Data.DataTable”

    $colupdateduration = @()
    ForEach($cud in $table)
        $object = New-Object -TypeName PSObject
        $object | Add-Member -MemberType NoteProperty -Name "Collection" -Value $cud.Collectionname
        $object | Add-Member -MemberType NoteProperty -Name "EvaluationStartTime" -Value $cud.EvaluationStartTime
        $object | Add-Member -MemberType NoteProperty -Name "LastRefreshTime" -Value $cud.LastRefreshTime
        $object | Add-Member -MemberType NoteProperty -Name "Duration" -Value ($dur = Get-date $cud.Duration -Format "HH:mm:ss")
        $colupdateduration += $object


Thanks to Roger Zander and Claude Henchoz for the SQL query to find these collections.

Leave a Reply