When upgrading your ConfigMgr site, or installing an update that creates a new ConfigMgr client package, it can be helpful to monitor the rollout of the new client version in your environment.
I put together this PowerShell function which uses my New-WPFMessageBox function to graphically display the count and percentage of client versions in the ConfigMgr site. The data comes from a SQL query, so you’ll need minimum db_datareader access to your ConfigMgr database with your logged-in account, as well as the New-WPFMessageBox function.
By default, it shows only active systems, but you can include inactive systems by checking the box.
| Function Get-CMClientVersions { | |
| # Requires the "New-WPFMessageBox" function available at https://gist.github.com/SMSAgentSoftware/0c0eee98a673b6ac34f5215ea6841beb | |
| # Requires minimum "db_datareader" SQL role in the ConfigMgr database | |
| # Usage: Get-CMClientVersions -SQLServer "SQLServer" -Database "Database" | |
| [CmdletBinding()] | |
| Param | |
| ( | |
| [Parameter(Mandatory=$True,Position=0)] | |
| [string]$SQLServer, | |
| [Parameter(Mandatory=$True,Position=1)] | |
| [string]$Database | |
| ) | |
| # Define SQL Queries | |
| $WithInactiveQuery = " | |
| Select | |
| sys.Client_Version0 as 'Client Version', | |
| count(sys.ResourceID) as 'Count', | |
| cast(count(sys.ResourceID) * 100.0 / ( | |
| select | |
| count(*) | |
| from v_R_System | |
| inner join dbo.v_CH_ClientSummary on v_R_System.ResourceID = dbo.v_CH_ClientSummary.ResourceID | |
| ) as numeric(36,2))as 'Percent' | |
| from v_R_System sys | |
| inner join dbo.v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID | |
| Group by sys.Client_Version0 | |
| Order by sys.Client_Version0 desc | |
| " | |
| $ActiveQuery = " | |
| Select | |
| sys.Client_Version0 as 'Client Version', | |
| count(sys.ResourceID) as 'Count', | |
| cast(count(sys.ResourceID) * 100.0 / ( | |
| select | |
| count(*) | |
| from v_R_System | |
| inner join dbo.v_CH_ClientSummary on v_R_System.ResourceID = dbo.v_CH_ClientSummary.ResourceID | |
| where dbo.v_CH_ClientSummary.ClientActiveStatus = 1 | |
| ) as numeric(36,2))as 'Percent' | |
| from v_R_System sys | |
| inner join dbo.v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID | |
| where ch.ClientActiveStatus = 1 | |
| Group by sys.Client_Version0 | |
| Order by sys.Client_Version0 desc | |
| " | |
| # Create a datagrid | |
| $DataGrid = New-Object System.Windows.Controls.DataGrid | |
| $DataGrid.IsReadOnly = $True | |
| $DataGrid.FontSize = 20 | |
| $DataGrid.CanUserAddRows = "False" | |
| $DataGrid.GridLinesVisibility = "None" | |
| $DataGrid.FontFamily = "Candara" | |
| $DataGrid.Margin = 5 | |
| $DataGrid.Padding = 5 | |
| $DataGrid.BorderThickness = 0 | |
| $DataGrid.HorizontalAlignment = "Stretch" | |
| $DataGrid.VerticalAlignment = "Stretch" | |
| $DataGrid.Width = "NaN" | |
| $DataGrid.Height = "NaN" | |
| # Create a data source and bind it to the datagrid | |
| $DataContext = New-Object System.Collections.ObjectModel.ObservableCollection[Object] | |
| $Binding = New-Object System.Windows.Data.Binding | |
| $Binding.Path = "[0].DefaultView" | |
| $Binding.Mode = [System.Windows.Data.BindingMode]::OneWay | |
| $Binding.Source = $DataContext | |
| [void]$DataGrid.SetBinding([System.Windows.Controls.DataGrid]::ItemsSourceProperty,$Binding) | |
| # Create a checkbox for optionall including inactive systems in the results | |
| $CheckBox = New-Object System.Windows.Controls.CheckBox | |
| $CheckBox.Content = "Include inactive systems" | |
| $CheckBox.FontSize = 16 | |
| $CheckBox.FontFamily = "Candara" | |
| $CheckBox.HorizontalAlignment = "Center" | |
| $CheckBox.VerticalContentAlignment = "Center" | |
| $CheckBox.Padding = 5 | |
| $CheckBox.Add_Checked({ | |
| Invoke-SQLQuery –DataContext $DataContext –Query $WithInactiveQuery –SQLServer $SQLServer –Database $Database | |
| }) | |
| $CheckBox.Add_UnChecked({ | |
| Invoke-SQLQuery –DataContext $DataContext –Query $ActiveQuery –SQLServer $SQLServer –Database $Database | |
| }) | |
| # Create a stackpanel | |
| $StackPanel = New-Object System.Windows.Controls.StackPanel | |
| $StackPanel.AddChild($CheckBox) | |
| $StackPanel.AddChild($DataGrid) | |
| # Function to query SQL Server | |
| function Invoke-SQLQuery | |
| { | |
| [CmdletBinding()] | |
| Param | |
| ( | |
| [string]$SQLServer, | |
| [string]$Database, | |
| [Parameter(ValueFromPipeline=$true)] | |
| [string]$Query, | |
| [int]$ConnectionTimeout = 5, | |
| [int]$CommandTimeout = 120, | |
| $DataContext | |
| ) | |
| # Define connection string | |
| $connectionString = "Server=$SQLServer;Database=$Database;Integrated Security=SSPI;Connection Timeout=$ConnectionTimeout" | |
| # Open the connection | |
| Try | |
| { | |
| $connection = New-Object –TypeName System.Data.SqlClient.SqlConnection | |
| $connection.ConnectionString = $connectionString | |
| $connection.Open() | |
| # Execute the query | |
| $command = $connection.CreateCommand() | |
| $command.CommandText = $Query | |
| $command.CommandTimeout = $CommandTimeout | |
| $reader = $command.ExecuteReader() | |
| } | |
| Catch | |
| { | |
| $CustomError = $_.Exception.Message | |
| If ($CustomError -match '"') | |
| { | |
| $CustomError = $CustomError.Replace('"',"'") | |
| } | |
| $Params = @{ | |
| Title = "Error" | |
| TitleFontSize = "20" | |
| TitleFontWeight = "Bold" | |
| TitleBackground = "Red" | |
| TitleTextForeground = "White" | |
| Content = "Could not connect to SQL Server: $CustomError" | |
| FontFamily = "Candara" | |
| Sound = 'Windows User Account Control' | |
| } | |
| New-WPFMessageBox @Params | |
| Return | |
| } | |
| # Load results to a data table | |
| $table = New-Object –TypeName 'System.Data.DataTable' | |
| $table.Load($reader) | |
| # Close the connection | |
| $connection.Close() | |
| # Return result | |
| If ($DataContext[0] -eq $null) | |
| { | |
| $DataContext.Add($Table) | |
| } | |
| Else | |
| { | |
| $DataContext[0] = $table | |
| } | |
| } | |
| # Display the message box | |
| $Params = @{ | |
| Title = "ConfigMgr Client Versions" | |
| TitleFontSize = "28 " | |
| TitleBackground = "LightSeaGreen" | |
| FontFamily = "Candara" | |
| Content = $StackPanel | |
| OnLoaded = {Invoke-SQLQuery –DataContext $DataContext –Query $ActiveQuery –SQLServer $SQLServer –Database $Database} | |
| } | |
| New-WPFMessageBox @Params | |
| } |
Nice picture but where is the script 🙂