Function that gets the current count and percentage of ConfigMgr Client Versions in the site

Get-CMClientVersions.ps1

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

}