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 🙂