QuickTricks: Get a count of alerts per day in OpsMgr 2007 R2 and 2012 or estimating ticket volume in Service Manager (#SCOM, #SCSM, #SYSCTR, #SQL)

As part of a dashboard project I was working on in OpsMgr 2007 R2 I created a series of SQL queries which can gather information from the OperationsManager database (not recommended) or from the OperationsManagerDW (recommended). These are available for download at:

http://www.systemcentercentral.com/Downloads/DownloadsDetails/tabid/144/IndexID/86822/Default.aspx 

Recently we had a request to identify an estimated number of tickets which would be generated in Service Manager 2012 from Operations Manager if we applied a specific criteria: generate tickets for all critical, high and medium priority alerts. There are reports which can list all alerts which match criteria such as this but those list the actual alerts and would need to be counted to identify how many per day. So I started with the SQL query below: (please note this is currently designed to query from the OperationsManager database which his not recommended for dashboards or other queries which would be executed regularly against the database)

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay

FROM Alert WITH (NOLOCK)

WHERE TimeRaised is not NULL AND TimeAdded > (GetDate()-5) 

GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)

ORDER BY DayAdded DESC

I updated this query to added the criteria required as shown below, highlighted change in Red.

SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay

FROM Alert WITH (NOLOCK)

WHERE TimeRaised is not NULL AND TimeAdded > (GetDate()-5) and  Severity=2 and (Priority=1 or Priority=2)

GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)

ORDER BY DayAdded DESC

Sample results are shown below:

2012.08.26    132

2012.08.25    213

2012.08.24    34

2012.08.23    99

2012.08.22    108

2012.08.21    59

Bonus QuickTrick:

Looking for a quick way to see how many active alerts you have in OpsMgr 2012 over the last 7 days? Microsoft included this as a pre-built view in the Monitoring Pane –> Operations Manager –> Management Group Health Trend as shown below. This shows the trend of the number of active alerts per day, and the agent health state trend.

image

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.