Comprehensive SCOM health report that can be run daily

SCOMHealthCheck.ps1

<#
    .SYNOPSIS
    SCOM daily health check report
    .DESCRIPTION
    Runs a number of powershell commands and one SQL script to provide a comprehensive SCOM health report. Only outputs items that require attention.
    .NOTES
    Author: Lee Murray 
    Created: 24/1/2020
    SQL powershell module required
    This script is intended to be run from a management server as a scheduled task
 #> 

# Configure variables
$MailTo = "example@google.com"
$MailFrom = "example2@google.com"
$smtpServer = "smtp.domain.ad"
$MailSubject = "SCOM Daily Healthcheck Report"
$OpsDBServer = "server\instance"
$OpsDB = "OperationsManager"

# Create header for HTML Report
$Head = @"
<style>
body {
background-color: #CCCCCC;
font-family: Verdana,sans-serif; 
font-size: x-small;
}
table {
border-width: 1px;
border-style: solid;
border-color: black;
border-collapse: collapse;
width: 100%;
}
th {
border-width: 1px;
padding: 0px;
border-style: solid;
border-color: black;
background-color:green;
color:white;
padding: 5px;
font-weight: bold;
text-align:left;
}
td {
border-width: 1px;
padding: 0px;
border-style: solid;
border-color: black;
background-color:#F0F0F0;
padding: 2px;
}
</style>
"@

# Import snapin 
Import-Module OperationsManager 
Import-Module SQLPS

# Get status of Management Server Health and input them into report
$NotHealthy = Get-SCOMManagementServer | where {$_.HealthState -ne "Success"} | select Name,HealthState,IsGateway
$Count = ($NotHealthy | Measure-Object).Count

if($Count -gt 0) { 
    $ReportOutput += "<p><H2>Management Servers not in Healthy States</H2></p>"

    $criteria = new-object Microsoft.EnterpriseManagement.Monitoring.MonitoringObjectGenericCriteria("InMaintenanceMode=1")
    $objectsInMM = (Get-SCOMManagementGroup).GetPartialMonitoringObjects($criteria)
        for ($Index = 0; $Index -lt $Count; $Index++) {
            if ($NotHealthy[$index].DisplayName -in $objectsInMM.DisplayName) {
                $NotHealthy[$index] | Add-Member -MemberType NoteProperty -Name MaintenanceMode -Value True
            }
            else {
                $NotHealthy[$index] | Add-Member -MemberType NoteProperty -Name InMaintenanceMode -Value False
            }
        }
    $ReportOutput += $NotHealthy | ConvertTo-HTML -fragment
}

# Get Agent Health Status and put unavailable ones into report (Grey or Un-monitored)
$MSCAgent = Get-SCOMClass -name "Microsoft.SystemCenter.Agent" 
$objects = Get-SCOMMonitoringObject -class:$MSCAgent
$AgentsinUnmonitoredState = $Objects | Where-Object {($_.IsAvailable -eq $false) -and ($_.HealthState -eq "Uninitialized") -and ($_.InMaintenanceMode -eq $False)} | Select DisplayName, HealthState, IsAvailable
$AgentsinGreyState = $Objects | Where-Object {($_.IsAvailable -eq $false) -and (!($_.HealthState -eq "Uninitialized")) -and ($_.InMaintenanceMode -eq $False)} | Select DisplayName, HealthState, IsAvailable

if ($AgentsinUnmonitoredState -or $AgentsinGreyState) {
    $ReportOutput += "<h2>Not Monitored Agents</h2>"
    for ($Index = 0; $Index -lt (($AgentsinGreyState | Measure-Object).Count); $Index++) {
        $AgentsinGreyState[$index] | Add-Member -MemberType NoteProperty -Name IsGreyAgent -Value True
    }  
    for ($Index = 0; $Index -lt (($AgentsinUnmonitoredState | Measure-Object).Count); $Index++) {
        $AgentsinUnmonitoredState[$index] | Add-Member -MemberType NoteProperty -Name IsGreyAgent -Value False
    }    
    $Unmonitored += $AgentsinGreyState
    $Unmonitored += $AgentsinUnmonitoredState
    $ReportOutput += $Unmonitored | ConvertTo-HTML -fragment
}

# Get list of agents in Pending State and put them into report
$Pending = Get-SCOMPendingManagement | sort AgentPendingActionType | select AgentName,ManagementServerName,AgentPendingActionType

if ($Pending.count -gt 0) {
    $ReportOutput += "<h2>Agents in Pending State</h2>"
    $ReportOutput += $Pending | ConvertTo-HTML -fragment
}

# Get Alerts specific to Management Servers and put them in the report
$ManagementServers = Get-SCOMManagementServer
$Alerts = New-Object System.Collections.arraylist

foreach ($ManagementServer in $ManagementServers) { 
    $Alerts.Add((Get-SCOMAlert -Criteria ("NetbiosComputerName = '" + $ManagementServer.ComputerName + "'") | where {$_.ResolutionState -ne '255' -and $_.MonitoringObjectFullName -Match 'Microsoft.SystemCenter'} | select TimeRaised,Name,Description,Severity,NetbiosComputerName)) | Out-Null
} 

if (($Alerts | Measure-Object).Count -gt 0) {
    $ReportOutput += "<h2>Management Server Alerts</h2>" 
    foreach ($ManagementServer in $ManagementServers) {
        if ($Alerts.NetbiosComputerName -contains $ManagementServer.ComputerName) {
            $ReportOutput += "<h3>Alerts on " + $ManagementServer.ComputerName + "</h3>"
            $ReportOutput += ($Alerts | where {$_.NetbiosComputerName -match $ManagementServer.ComputerName}) | Select TimeRaised,Name,Description,Severity  | ConvertTo-HTML -fragment  
        }
    }
}

# Find Overrides that have been stored in the default management pack and put them into the report
$DefaultOverrides = (Get-SCOMManagementPack | where {$_.DisplayName -match "Default Management Pack"}).GetOverrides() 

if($DefaultOverrides.Count -gt 2) { 
    $ReportOutput += "<h2>Overrides in Default Management Pack</h2>"

     for ($Index = 0; $Index -lt ($DefaultOverrides.Count); $Index++) {
        if ($DefaultOverrides[$Index].Rule) {
            $Rule += get-SCOMrule | where {$_.Id -eq $DefaultOverrides[$Index].Rule.Id} | select-object DisplayName,Description,@{name="RuleMonitor";value="Rule"}
        }
        elseif ($DefaultOverrides[$Index].Monitor) {
            $Monitor += get-SCOMmonitor | where {$_.Id -eq $DefaultOverrides[$Index].Monitor.Id} | select-object DisplayName,Description,@{name="RuleMonitor";value="Monitor"}
        }
    }
    if ($Rule) {
        $ReportOutput += $Rule | ConvertTo-HTML -fragment
        $ReportOutput += "<br />"
    }
    if ($Monitor) {
        $ReportOutput += $Monitor | ConvertTo-HTML -fragment
        $ReportOutput += "<br />" 
    }
}

# List Management Packs updated in last 24 hours
$MPDates = (Get-Date).adddays(-1)
$UpdatedMPs = Get-SCOMManagementPack | Where {$_.LastModified -gt $MPDates -and $_.DisplayName -notmatch "Overrides" -and $_.DisplayName} | Select-Object DisplayName, LastModified | Sort LastModified 

if ($UpdatedMPs) {
    $ReportOutput += "<h2>Management Packs Updated Last 24 Hours</h2>"
    $ReportOutput += $UpdatedMPs | ConvertTo-Html -fragment
}

# List Overrides created in last 24 hours
$Overrides = Get-SCOMOverride | where {$_.LastModified -gt $MPDates -and $_.Name -notmatch "SecureOverride"} | Select Name,Property,Value,@{Name="Type";Expression={$_.XmlTag}},Rule,Monitor,Discovery

if ($Overrides.count -gt 0) {
    $ReportOutput += "<h2>Overrides Created Last 24 Hours</h2>"
    $Rules = Get-SCOMRule
    $Monitors = Get-SCOMMonitor
    $Discoveries = Get-SCOMDiscovery

        for ($Index = 0; $Index -lt ($Overrides.Count); $Index++) {
            if ($Overrides[$Index].Type -match "Rule") {
                $Overrides[$Index] | Add-Member -MemberType NoteProperty -Name Object -Value ($Rules | where {$_.Id -eq $Overrides[$Index].Rule.Id}).DisplayName
            }
            elseif ($Overrides[$Index].Type -match "Monitor") {
                $Overrides[$Index] | Add-Member -MemberType NoteProperty -Name Object -Value ($Monitors | where {$_.Id -eq $Overrides[$Index].Monitor.Id}).DisplayName
            }
            elseif ($Overrides[$Index].Type -match "Discovery") {
                $Overrides[$Index] | Add-Member -MemberType NoteProperty -Name Object -Value ($Discoveries | where {$_.Id -eq $Overrides[$Index].Discovery.Id}).DisplayName
            }
        }
    $ReportOutput += $Overrides | Select Type,Object,Property,Value | ConvertTo-Html -fragment
}

# Get servers with missing performance data
$Query = @'
if object_id('tempdb..#temptable') IS NOT NULL
 DROP TABLE #temptable

SELECT     distinct bmetarget.Name into #temptable
 FROM        OperationsManager.dbo.BaseManagedEntity AS BMESource WITH (nolock) INNER JOIN
 OperationsManager.dbo.Relationship AS R WITH (nolock) ON
 R.SourceEntityId = BMESource.BaseManagedEntityId INNER JOIN
 OperationsManager.dbo.BaseManagedEntity AS BMETarget WITH (nolock) ON
 R.TargetEntityId = BMETarget.BaseManagedEntityId inner join mtv_computer d on bmetarget.name=d.[DisplayName]
 and d.IsVirtualNode_B880813A_A3C6_D422_463E_E5739F81BDCA is null
 WHERE     (bmetarget.fullname like 'Microsoft.Windows.Computer%')

if object_id('tempdb..#healthstate') IS NOT NULL
 DROP TABLE #healthstate

select  megv.path, megv.inmaintenancemode, megv.ismanaged, megv.isavailable, megv.healthstate into #healthstate
 from managedentitygenericview as megv with (nolock) inner join managedtypeview as mtv with (nolock)
 on megv.monitoringclassid=mtv.id
 where mtv.name ='microsoft.systemcenter.agent'

if object_id('tempdb..#perfcpudata') IS NOT NULL
 DROP TABLE #perfcpudata

select Path, 'CPU' as 'Cat' into #perfcpudata
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and (objectname ='Processor Information' OR objectname = 'Processor') and countername='% Processor Time'

if object_id('tempdb..#perfmemdata') IS NOT NULL
 DROP TABLE #perfmemdata

select Path,'Memory' as 'Cat' into #perfmemdata
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
 and objectname ='Memory' and countername='Available MBytes'

if object_id('tempdb..#perfdiskdata') IS NOT NULL
 DROP TABLE #perfdiskdata

select Path,'Disk' as 'Cat' into #perfdiskdata
 from PerformanceDataAllView pdv with (NOLOCK)
 inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
 inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
 where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
 and objectname ='LogicalDisk' and countername='% Free Space' and instancename='C:'

if object_id('tempdb..#temptable1') IS NOT NULL
 DROP TABLE #temptable1
 create table #temptable1 (
 name nvarchar(250),
 cat nvarchar(20),
 val nvarchar(2)
 )
 insert into #temptable1
 select name, 'CPU' as 'cat', '1' as 'val'
from #temptable where name not in
 (select path from #perfcpudata)

insert into #temptable1
 select name, 'Memory' as 'cat', '1' as 'val'
from #temptable where name not in
 (select path from #perfmemdata)

insert into #temptable1
 select name, 'Disk' as 'cat', '1' as 'val'
from #temptable where name not in
 (select path from #perfdiskdata)

if object_id('tempdb..#output') IS NOT NULL
 DROP TABLE #output
 create table #output (
 Name nvarchar(250),
 CPU nvarchar(2),
 Memory nvarchar(2),
 Disk nvarchar(2)
 )

insert into #output
 select distinct tt.name ,'0','0','0'
 from #temptable1 as tt, #healthstate as hs
 where tt.name=hs.path collate SQL_Latin1_General_CP1_CI_AS
 and hs.isavailable=1
 and hs.ismanaged=1
 and hs.healthstate is not null
 and hs.inmaintenancemode !=1

update #output set cpu=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat='CPU')
update #output set memory=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat='Memory')
update #output set disk=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat='Disk')

select * from #output
'@

$MissingData = Invoke-Sqlcmd -Query $Query -ServerInstance $OpsDBServer -Database $OpsDB

if ($MissingData.count -gt 0) {
    $ReportOutput += "<h2>Servers with missing performance data</h2>"
    $ReportOutput += "<p>A 1 indicates performance data is missing for that catagory</p>"
    $Props = Get-Member -InputObject $MissingData[0] -MemberType Property | Select-Object -ExpandProperty Name | Sort-Object -Descending
    $ReportOutput += $MissingData | Select-Object -Property $Props | ConvertTo-Html  -fragment
}

# Write an 'all is well' message if there is nothing to action
if (!$ReportOutput) {
    $ReportOutput = "Nothing to see here, SCOM is healthy"
}

# Take all $ReportOutput and combine it with $Head to create completed HTML output
$Body = ConvertTo-HTML -head $Head -body "$ReportOutput" | Out-String

# Send Output as email
Send-MailMessage -To $MailTo -From $MailFrom -SmtpServer $smtpServer -Subject $MailSubject -Body $Body -BodyAsHtml