Get ConfigMgr Client Versions with PowerShell

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.

img1

img2

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
}

One thought on “Get ConfigMgr Client Versions with PowerShell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.