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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 🙂