MOM 2005 SQL Query: Count Alerts by Severity between dates
The following query uses the SDKAlertView (SQL View) to show the number of alerts by severity between a users specified time period. This can be used on both the OnePoint and SystemCenter Reporting Databases.
-- Created by Scott Moss
-- Count Alerts between specified dates. OnePoint Or SystemCenterReporting DBs.
Declare @StartDateTime DateTime
Declare @EndDateTime DateTime
-- >>>>>>>>>>>>>>>
SET @StartDateTime = '07/07/2007 12:00:00 PM' -- type a date time in UTC time i.e. 3/29/2006 12:00:00 AM
SET @EndDateTime = '07/08/2007 12:00:00 PM' -- type a date time in UTC time i.e. 3/30/2006 12:00:00 AM
-- <<<<<<<<<<<<<<<
SELECT Severity = Case
When Severity = 10 Then 'Success'
When Severity = 20 Then 'Informational'
When Severity = 30 Then 'Warning'
When Severity = 40 Then 'Error'
When Severity = 50 Then 'Critical Error'
When Severity = 60 Then 'Security Issue'
When Severity = 70 Then 'Service Unavailable'
Else 'Unknown'
End,
COUNT(*) AS EventCount
FROM SDKAlertView
WHERE (TimeRaised BETWEEN @StartDateTime AND @EndDateTime)
GROUP BY Severity
ORDER BY EventCount DESC
-- END SQL CODE