How to create daily reports with System Center Operations Manager 2012 and PowerShell - Part 4
This series of blogs aims to show administrators how to create daily reports summarising the health of Operations Manager using PowerShell. This should reduce the overheads required for daily health reviews of their system environments.
In this fourth and penultimate entry in the blog series explains how PowerShell can be used to extract alert information from Operations Manager for analysis and then output onto an HTML page. Reviewing alerts and resolving either through tuning or root cause analysis is key to ensuring the overall health of your infrastructure, this can obviously be difficult dependent on the amount of alerts received. The following script helps digest this data to help focus on key problem areas to ensure your resources can be assigned appropriately. Subsequent posts will build on this principle to allow more sophisticated system health reporting.
#Outputs header and formatting for Last 24 Hours Alert Count by Severity
$ReportOutput+="<h5>Last 24 Hours Alert Count by Severity</h5>"
#Breaks down alerts by Severity in the last 24 hours and counts
$targetdate= (get-date).AddDays(-1)
$ReportOutput+= get-SCOMalert | where-object {($_.ResolutionState -ne 255) -and ($_.TimeRaised -gt$targetdate)} | group-objectseverity | sort-objectcount -descending | select Count, Name | ConvertTo-HTML
#Outputs header and formatting for Alerts broken down by hour logged
$ReportOutput+="</br>"
$ReportOutput+="<hr size=4 width=50% align=left>"
$ReportOutput+="<h5>Alerts broken down by hour logged</h5>"
#SQL Query breaks down the last 24 hours of alerts and shows the by the hour logged for trend analysis
$SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString ="Server=$DWDBServerNamevar;Database= $DWDBNamevar;Integrated Security=True"
$SqlCmd=New-ObjectSystem.Data.SqlClient.SqlCommand
$SqlCmd.CommandText ="
CREATE TABLE #HourReport (
[Hour Alert Added] varchar (5) NOT NULL,
[Number of Alerts Per Hour] numeric (18) NULL
)on [PRIMARY]
INSERT INTO #HourReport
values ('00:00','0'),
('01:00','0'),
('02:00','0'),
('03:00','0'),
('04:00','0'),
('05:00','0'),
('06:00','0'),
('07:00','0'),
('08:00','0'),
('09:00','0'),
('10:00','0'),
('11:00','0'),
('12:00','0'),
('13:00','0'),
('14:00','0'),
('15:00','0'),
('16:00','0'),
('17:00','0'),
('18:00','0'),
('19:00','0'),
('20:00','0'),
('21:00','0'),
('22:00','0'),
('23:00','0')
SELECT CASE
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '00' THEN '00:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '01' THEN '01:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '02' THEN '02:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '03' THEN '03:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '04' THEN '04:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '05' THEN '05:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '06' THEN '06:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '07' THEN '07:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '08' THEN '08:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '09' THEN '09:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '10' THEN '10:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '11' THEN '11:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '12' THEN '12:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '13' THEN '13:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '14' THEN '14:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '15' THEN '15:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '16' THEN '16:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '17' THEN '17:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '18' THEN '18:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '19' THEN '19:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '20' THEN '20:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '21' THEN '21:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '22' THEN '22:00'
WHEN CONVERT(VARCHAR(2), RaisedDateTime, 114) = '23' THEN '23:00'
END AS [Hour Alert Added], COUNT(*) AS [Number Of Alerts Per Hour]
INTO #HourResults
FROM [Alert].[vAlert]
WHERE RaisedDateTime > dateadd (hh,-23,getutcdate())
GROUP BY CONVERT(VARCHAR(2), RaisedDateTime, 114)
SELECT hr.[Hour Alert Added],CASE
WHEN hp.[Number Of Alerts Per Hour] IS NULL THEN '0'
ELSE hp.[Number Of Alerts Per Hour]
END [Number Of Alerts Per Hour]
FROM #HourReport hr
left OUTER JOIN #HourResults hp
ON hr.[Hour Alert Added] = hp.[Hour Alert Added]
drop table #HourReport
drop table #HourResults
"
$SqlCmd.Connection =$SqlConnection
$SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand =$SqlCmd
$DataSet=New-ObjectSystem.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$OpsAlertsbyHourSQLQuery=$dataSet.Tables[0].rows | Select-object'Hour Alert Added', 'Number of Alerts Per Hour'
$ReportOutput+=$OpsAlertsbyHourSQLQuery | ConvertTo-HTML
#Outputs header and formatting for Top 5 Open Alerts
$ReportOutput+="</br>"
$ReportOutput+="<hr size=4 width=50% align=left>"
$ReportOutput+="<h5>Top 5 Open Alerts</h5>"
#Looks for Top 5 Open Alerts by count , very useful for focusing efforts
$ReportOutput+= get-SCOMalert -Criteria 'ResolutionState < "255"' | Group-ObjectName | Sort-objectCount -desc | select-Object -first 5 Count, Name | ConvertTo-HTML
#Outputs header and formatting for Top 5 Open Alerts
$ReportOutput+="</br>"
$ReportOutput+="<hr size=4 width=50% align=left>"
$ReportOutput+="<h5>Top 5 Open Repeating Alerts</h5>"
#Look for Top 5 open Alerts with high repeat count.
$ReportOutput+= get-SCOMalert -Criteria 'ResolutionState < "255"' | Sort -descRepeatCount | select-object –first 5 Name, RepeatCount, MonitoringObjectPath, Description | ConvertTo-HTML
#Outputs header and formatting for Alerts by Management Pack
$ReportOutput+="</br>"
$ReportOutput+="<hr size=4 width=50% align=left>"
$ReportOutput+="<h5>Alerts by Management Pack</h5>"
#Looks for open Alerts and traces back to originating MP, this can be used for finding areas that need tuning
$SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString ="Server=$OpsDatabaseSQLServer;Database= $OpsDatabaseName;Integrated Security=True"
$SqlCmd=New-ObjectSystem.Data.SqlClient.SqlCommand
$SqlCmd.CommandText ="
use OperationsManager
Select mp.Name, av.MonitoringRuleId, mp.Id INTO #AlerttoMPCount
FROM dbo.AlertView av
JOIN dbo.RuleView rv
ON av.MonitoringRuleId = rv.id
JOIN dbo.ManagementPackView mp
ON rv.ManagementPackId = mp.Id
where av.ResolutionState <> '255'
UNION ALL
Select mp.Name, av.MonitoringRuleId, mp.Id
FROM dbo.AlertView av
JOIN dbo.MonitorView mv
ON av.MonitoringRuleId = mv.id
JOIN dbo.ManagementPackView mp
ON mv.ManagementPackId = mp.Id
where av.ResolutionState <> '255'
Select count (Name) as [Alerts Received],Name as [Management Pack Name]
FROM #AlerttoMPCount
GROUP BY Name
IF OBJECT_ID(N'tempdb..#AlerttoMPCount', N'U') IS NOT NULL
DROP TABLE #AlerttoMPCount;
"
$SqlCmd.Connection =$SqlConnection
$SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand =$SqlCmd
$DataSet=New-ObjectSystem.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$OpsAlertsbyMP=$dataSet.Tables[0].rows | Select-object'Alerts Received', 'Management Pack Name'
$ReportOutput+=$OpsAlertsbyMP | ConvertTo-HTML
$ReportOutput+="<br></br>"
$ReportOutput+="<hr size=4 width=50% align=left>"
#Outputs line for end of report format purposes
$ReportOutput += "</br>"
$ReportOutput += "<hr size=4 width=50% align=left>"
Now run using the command from the previous blog to start the script, replacing parameters as required, as illustrated in this example:
PowerShell.exe Daily-Report.ps1 MSServerName c:\scripts Daily-Report.htm
This will produce a report similar to the below
