Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Function that gets the current count and percentage of ConfigMgr Client Versions in the site
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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.