Share via


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 

 

 

 

Daily-Report.ps1