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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
Function Get-CMCollectionRefreshDuration { <# .Synopsis Get-CMCollectionRefreshDuration displays the duration of Configuration Manager refresh cycles .DESCRIPTION Inefficient queries can cause long collection refresh cycles. Use the Get-CMCollectionRefreshDuration cmdlet to identify collections with long refresh cycles. .EXAMPLE 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 .EXAMPLE Get-CMCollectionRefreshDuration -DataSource sqlsrv01\instance1 -Database CM_DB1 -ShowTopCollections 100 Lists the top 100 collections .PARAMETER DataSource The name of the SQL Server that hosts the configuration manager database <servername\instance> .PARAMETER 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 #> [CmdletBinding()] Param ( [Parameter(Mandatory=$false, Position=0, HelpMessage="Enter the SQL Server datasource name <server\instance>")] [string]$DataSource="server01\instance1", [Parameter(Mandatory=$false, Position=1, HelpMessage="The database name of the ConfigMgr database")] [string]$Database="CMDB1", [Parameter(Mandatory=$false, Position=2, HelpMessage="The number of top collectons to show")] [ValidateRange(1,10000)] [int]$ShowTopCollections="5" ) Begin{ # connecting to SQL server Try{ $Connection = New-Object System.Data.SqlClient.SqlConnection $Connection.ConnectionString = "Data Source=$DataSource;Integrated Security=True" $Connection.Open() } Catch [Exception] { write-output "Unable to connect to $DataSource" Write-Output $_.Exception Throw } $query = " SELECT TOP $ShowTopCollections [CollectionName], [EvaluationStartTime], [LastRefreshTime], 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” $table.Load($result) $Connection.Close() } Process{ $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 } } End{ $colupdateduration } } |
Thanks to Roger Zander and Claude Henchoz for the SQL query to find these collections.