<# .Synopsis Generates an overview of client health in your Configuration Manager environment as an html email .DESCRIPTION This script dynamically builds an html report based on key client health related data from the Configuration Manager database. The script is intended to be run regularly as a scheduled task. .EXAMPLE To run as a scheduled task, use a command like the following: Powershell.exe -ExecutionPolicy Bypass -WindowStyle Hidden -File "\New-CMClientHealthSummaryReport.ps1" .NOTES More information can be found here: http://smsagent.wordpress.com/free-configmgr-reports/client-health-summary-report/ The Parameters region should be updated for your environment before executing the script Author: Trevor Jones @trevor_smsagent v1.0 - 2016/11/02 - Intial release #> #region Parameters # Database info $script:dataSource = 'sqlsrv-01\INST_SCCM' # SQL Server name (and instance where applicable) $script:database = 'CM_ABC' # ConfigMgr Database name # Email params $EmailParams = @{ To = 'Joe.Blow@contoso.com' From = 'POSH_Reporting@contoso.com' Smtpserver = 'mysmtpserver' Subject = "ConfigMgr Client Health Summary | $(Get-Date -Format dd-MMM-yyyy)" } # Reporting thresholds (percentages) <# >= Good - reports as green in the progress bar >= Warning - reports as amber in the progress bar < Warning - reports as red in the progress bar #> $script:Thresholds = @{} $Thresholds.Good = 90 $Thresholds.Warning = 80 $Thresholds.Inventory = @{} # Inventory thresholds are applicable to HW inventory, SW inventory and Heartbeat (DDR) only $Thresholds.Inventory.Good = 85 $Thresholds.Inventory.Warning = 70 #endregion #region Functions # Function to run a sql query function Get-SQLData { param($Query) $connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;" $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $command = $connection.CreateCommand() $command.CommandText = $Query $reader = $command.ExecuteReader() $table = New-Object -TypeName 'System.Data.DataTable' $table.Load($reader) # Close the connection $connection.Close() return $Table } # Function to set the progress bar colour based on the the threshold value function Set-PercentageColour { param( [int]$Value, [switch]$UseInventoryThresholds ) If ($UseInventoryThresholds) { $Good = $Thresholds.Inventory.Good $Warning = $Thresholds.Inventory.Warning } Else { $Good = $Thresholds.Good $Warning = $Thresholds.Warning } If ($Value -ge $Good) { $Hex = "#00ff00" # Green } If ($Value -ge $Warning -and $Value -lt $Good) { $Hex = "#ff9900" # Amber } If ($Value -lt $Warning) { $Hex = "#FF0000" # Red } Return $Hex } #endregion # Create html header $html = @" "@ # Create has table to store data $Data = @{} #region Get Client Count $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 1) " $Data.ClientCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get No Client Count $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null " $Data.NoClientCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Client Percentage $Data.ClientCountPercentage = [Math]::Round($Data.ClientCount / ($Data.ClientCount + $Data.NoClientCount) * 100) $Data.NoClientCountPercentage = 100 - $Data.ClientCountPercentage $Data.TotalDiscoveredSystems = $Data.ClientCount + $Data.NoClientCount # Set html $html = $html + @"

Discovered Systems with Client Installed

$($Data.ClientCountPercentage)%
Discovered Systems with Client $($Data.ClientCount)
Discovered Systems without Client $($Data.NoClientCount)
Total $($Data.TotalDiscoveredSystems)
"@ #endregion #region Get Active Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where ClientActiveStatus = 1 " $Data.ActiveCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get InActive Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where ClientActiveStatus = 0 " $Data.InactiveCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active Percentage $Data.ActiveCountPercentage = [Math]::Round($Data.ActiveCount / ($Data.ActiveCount + $Data.InactiveCount) * 100) $Data.InActiveCountPercentage = 100 - $Data.ActiveCountPercentage $Data.ActiveInactiveTotal = $Data.ActiveCount + $data.InactiveCount # Set html $html = $html + @"

Active Clients

$($Data.ActiveCountPercentage)%
Active Clients $($Data.ActiveCount)
Inactive Clients $($Data.InActiveCount)
Total $($Data.ActiveInactiveTotal)
"@ #endregion #region Get Active/Pass Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where ClientStateDescription = 'Active/Pass' " $Data.ActivePassCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get Active/Fail Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where ClientStateDescription = 'Active/Fail' " $Data.ActiveFailCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get Active/Unknown Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where ClientStateDescription = 'Active/Unknown' " $Data.ActiveUnknownCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active/Pass Percentage $Data.ActivePassCountPercentage = [Math]::Round($Data.ActivePassCount / ($Data.ActivePassCount + $Data.ActiveFailCount + $Data.ActiveUnknownCount) * 100) $Data.ActiveNotPassCountPercentage = 100 - $Data.ActivePassCountPercentage # Set html $html = $html + @"

Active Clients Health Evaluation

$($Data.ActivePassCountPercentage)%
Active/Pass $($Data.ActivePassCount)
Active/Fail $($Data.ActiveFailCount)
Active/Unknown $($Data.ActiveUnknownCount)
"@ #endregion #region Get Active DDR Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveDDR = 1 and ClientActiveStatus = 1 " $Data.ActiveDDRCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get InActive DDR Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveDDR = 0 and ClientActiveStatus = 1 " $Data.InActiveDDRCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active DDR Percentage $Data.ActiveDDRCountPercentage = [Math]::Round($Data.ActiveDDRCount / ($Data.ActiveDDRCount + $Data.InActiveDDRCount) * 100) $Data.InActiveDDRCountPercentage = 100 - $Data.ActiveDDRCountPercentage # Set html $html = $html + @"

Active Clients Heartbeat (DDR)

$($Data.ActiveDDRCountPercentage)%
Active DDR $($Data.ActiveDDRCount)
Inactive DDR $($Data.InActiveDDRCount)
"@ #endregion #region Get Active HW Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveHW = 1 and ClientActiveStatus = 1 " $Data.ActiveHWCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get InActive HW Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveHW = 0 and ClientActiveStatus = 1 " $Data.InActiveHWCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active HW Percentage $Data.ActiveHWCountPercentage = [Math]::Round($Data.ActiveHWCount / ($Data.ActiveHWCount + $Data.InActiveHWCount) * 100) $Data.InActiveHWCountPercentage = 100 - $Data.ActiveHWCountPercentage # Set html $html = $html + @"

Active Clients Hardware Inventory

$($Data.ActiveHWCountPercentage)%
Active HW Inventory $($Data.ActiveHWCount)
Inactive HW Inventory $($Data.InActiveHWCount)
"@ #endregion #region Get Active SW Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveSW = 1 and ClientActiveStatus = 1 " $Data.ActiveSWCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get InActive SW Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActiveSW = 0 and ClientActiveStatus = 1 " $Data.InActiveSWCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active SW Percentage $Data.ActiveSWCountPercentage = [Math]::Round($Data.ActiveSWCount / ($Data.ActiveSWCount + $Data.InActiveSWCount) * 100) $Data.InActiveSWCountPercentage = 100 - $Data.ActiveSWCountPercentage # Set html $html = $html + @"

Active Clients Software Inventory

$($Data.ActiveSWCountPercentage)%
Active SW Inventory $($Data.ActiveSWCount)
Inactive SW Inventory $($Data.InActiveSWCount)
"@ #endregion #region Get Active PolicyRequest Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActivePolicyRequest = 1 and ClientActiveStatus = 1 " $Data.ActivePRCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Get InActive PolicyRequest Count $Query = " Select count(ResourceID) as 'Count' from v_CH_ClientSummary where IsActivePolicyRequest = 0 and ClientActiveStatus = 1 " $Data.InActivePRCount = Get-SQLData -Query $Query | Select -ExpandProperty Count # Calculate Active PolicyRequest Percentage $Data.ActivePRCountPercentage = [Math]::Round($Data.ActivePRCount / ($Data.ActivePRCount + $Data.InActivePRCount) * 100) $Data.InActivePRCountPercentage = 100 - $Data.ActivePRCountPercentage # Set html $html = $html + @"

Active Clients Policy Request

$($Data.ActivePRCountPercentage)%
Active Policy Request $($Data.ActivePRCount)
Inactive Policy Request $($Data.InActivePRCount)
"@ #endregion #region Get Client Versions $Query = " Select sys.Client_Version0 as 'Client Version', count (sys.ResourceID) as 'Count' from v_R_System sys inner join v_CH_ClientSummary ch on sys.ResourceID = ch.ResourceID where ch.ClientActiveStatus = 1 Group by sys.Client_Version0 Order by sys.Client_Version0 desc " $Data.ClientVersions = Get-SQLData -Query $Query $Data.TotalForClientVersions = [int]0 $data.ClientVersions | foreach { $Data.TotalForClientVersions = $Data.TotalForClientVersions + $_.Count } # Set html $html = $html + @"

Client Versions

Version Count Percent
"@ $Data.ClientVersions | foreach { $Percentage = [Math]::Round($_.Count / $Data.TotalForClientVersions * 100) $PercentageRemaining = (100 - $Percentage) $html = $html + @"
$($_.'Client Version') $($_.Count) $($Percentage)%
"@ } #endregion #region Get No Client Systems $Data.NoClient = @{} # no client - unknown OS $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null and Operating_System_Name_and0 like 'unknown%' " $Data.NoClient.UnknownOS = Get-SQLData -Query $Query | Select -ExpandProperty Count # no client windows OS $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null and Operating_System_Name_and0 like '%Windows%' " $Data.NoClient.WindowsOS = Get-SQLData -Query $Query | Select -ExpandProperty Count # no client other OS $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null and Operating_System_Name_and0 not like '%Windows%' and Operating_System_Name_and0 not like 'unknown%' " $Data.NoClient.OtherOS = Get-SQLData -Query $Query | Select -ExpandProperty Count # no client and no last logon timestamp in last 7 days $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) >= 7 " $Data.NoClient.GTLast7 = Get-SQLData -Query $Query | Select -ExpandProperty Count # no client and last logon timestamp within last 7 days $Query = " Select count(ResourceID) as 'Count' from v_R_System where (Client0 = 0 or Client0 is null) and Unknown0 is null and (DATEDIFF(day,Last_Logon_Timestamp0, GetDate())) < 7 " $Data.NoClient.LTLast7 = Get-SQLData -Query $Query | Select -ExpandProperty Count # Set html $html = $html + @"

Systems with No Client

Category Count Percent
"@ $html = $html + @"
Windows OS $($Data.NoClient.WindowsOS) $([Math]::Round($Data.NoClient.WindowsOS / $Data.NoClientCount * 100))%
Other OS $($Data.NoClient.OtherOS) $([Math]::Round($Data.NoClient.OtherOS / $Data.NoClientCount * 100))%
Unknown OS $($Data.NoClient.UnknownOS) $([Math]::Round($Data.NoClient.UnknownOS / $Data.NoClientCount * 100))%
Last Logon > 7 days $($Data.NoClient.GTLast7) $([Math]::Round($Data.NoClient.GTLast7 / $Data.NoClientCount * 100))%
Last Logon < 7 days $($Data.NoClient.LTLast7) $([Math]::Round($Data.NoClient.LTLast7 / $Data.NoClientCount * 100))%
"@ #endregion #region Windows Client Installation Failures $Query = " select count(cdr.MachineID) as 'Count', cdr.CP_LastInstallationError as 'Error Code' from v_CombinedDeviceResources cdr where cdr.IsClient = 0 and cdr.DeviceOS like '%Windows%' group by cdr.CP_LastInstallationError " $InstallErrors = Get-SQLData -Query $Query # Translate error codes to friendly names and add the percentage value $TotalErrors = 0 $InstallErrors | foreach { $TotalErrors = $TotalErrors + $_.Count } $Data.InstallFailures = $InstallErrors | Select Count,'Error Code',@{n='Error Description';e={([ComponentModel.Win32Exception]$_.'Error Code').Message}},@{n='Percentage';e={[Math]::Round($_.Count / $TotalErrors * 100)}} | Sort Count -Descending # Set html $html = $html + @"

Windows Client Installation Failures

Error Code Error Description Count Percent
"@ $Data.InstallFailures | foreach { $html = $html + @"
$($_.'Error Code') $($_.'Error Description') $($_.Count) $($_.Percentage)%
"@ } #endregion #region Computer reboots # Active systems with a known Last BootUp Time $Query = " select Count(ch.ResourceID) as 'Count' from v_CH_ClientSummary ch left join v_GS_OPERATING_SYSTEM os on os.ResourceId = ch.ResourceId where os.LastBootUpTime0 is not null and ch.ClientActiveStatus = 1 " $Data.ActiveLastBootUpTotal = Get-SQLData -Query $Query | Select -ExpandProperty Count # Computer reboot dates $Query = " select '7 days' as TimePeriod,Count(sys.Name0) as 'Count',1 SortOrder from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(day,-7, GETDATE()) and ch.ClientActiveStatus = 1 UNION select '14 days' as TimePeriod,Count(sys.Name0) as 'Count',2 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(day,-14, GETDATE()) and ch.ClientActiveStatus = 1 UNION select '1 month' as TimePeriod,Count(sys.Name0) as 'Count',3 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(month,-1, GETDATE()) and ch.ClientActiveStatus = 1 UNION select '3 months' as TimePeriod,Count(sys.Name0) as 'Count',4 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(MONTH,-3, GETDATE()) and ch.ClientActiveStatus = 1 UNION select '6 months' as TimePeriod,Count(sys.Name0) as 'Count',5 from v_R_System sys inner join v_GS_OPERATING_SYSTEM os on os.ResourceId = sys.ResourceId inner join v_CH_ClientSummary ch on ch.ResourceID = sys.ResourceID where os.LastBootUpTime0 < DATEADD(MONTH,-6, GETDATE()) and ch.ClientActiveStatus = 1 Order By SortOrder " $Data.ComputerReboots = Get-SQLData -Query $Query # Set html $html = $html + @"

Computers Not Rebooted

Time Period Count Percent*
"@ $html = $html + @"
$($Data.ComputerReboots[0].TimePeriod) $($Data.ComputerReboots[0].Count) $([Math]::Round($Data.ComputerReboots[0].Count / $Data.ActiveLastBootUpTotal * 100))%
$($Data.ComputerReboots[1].TimePeriod) $($Data.ComputerReboots[1].Count) $([Math]::Round($Data.ComputerReboots[1].Count / $Data.ActiveLastBootUpTotal * 100))%
$($Data.ComputerReboots[2].TimePeriod) $($Data.ComputerReboots[2].Count) $([Math]::Round($Data.ComputerReboots[2].Count / $Data.ActiveLastBootUpTotal * 100))%
$($Data.ComputerReboots[3].TimePeriod) $($Data.ComputerReboots[3].Count) $([Math]::Round($Data.ComputerReboots[3].Count / $Data.ActiveLastBootUpTotal * 100))%
$($Data.ComputerReboots[4].TimePeriod) $($Data.ComputerReboots[4].Count) $([Math]::Round($Data.ComputerReboots[4].Count / $Data.ActiveLastBootUpTotal * 100))%
* Percentage is calculated from the total number of active clients that have a known last bootup time ($($Data.ActiveLastBootUpTotal))
"@ #endregion #region Client Health Thresholds $Query = " SELECT * FROM v_CH_Settings where SettingsID = 1 " $Data.CHSettings = Get-SQLData -Query $Query # Set html $html = $html + @"

Client Status Settings

Setting Days
"@ $html = $html + @"
Heartbeat Discovery $($data.CHSettings.DDRInactiveInterval)
Hardware Inventory $($data.CHSettings.HWInactiveInterval)
Software Inventory $($data.CHSettings.SWInactiveInterval)
Policy Requests $($data.CHSettings.PolicyInactiveInterval)
Status History Retention $($data.CHSettings.CleanUpInterval)
"@ #endregion # Close html document $html = $html + @" "@ # Send email Send-MailMessage @EmailParams -Body $html -BodyAsHtml