Get alerts from Management Servers using SQL

There’s a lot of ways we can retrieve a list of alerts from SCOM, but nothings quicker than going to the Database directly. So here’s a bit of SQL that can grab all alerts from a Management Server, but formats the output a bit nicer. So for example rather than outputting the severity as a 0, 1 or 2…it outputs the english word, Informational, Warning or Critical. Same with the Monitor or Rule. Just a bit easier to read than working out if a 0 or a 1 is a monitor or rule.

SELECT CS.Agent.NetworkName AS [Server Name],

Case AlertView.ResolutionState
When 0 then ‘New’
When 255 then ‘Closed’
When 1-254 then ‘Other’
End as [Resolution State],

Case AlertView.Severity
When 0 then ‘Informational’
When 1 then ‘Warning’
When 2 then ‘Critical’
End as [Severity],

Case AlertView.IsMonitorAlert
When 0 then ‘Rule’
When 1 then ‘Monitor’
End as [Monitor or Rule],

AlertView.TimeRaised AS [Time Raised],
AlertView.LastModified AS [Last Modified],
AlertView.AlertStringName AS [Alert Name],
AlertView.RepeatCount as [Repeat Count]

FROM CS.Agent INNER JOIN
BaseManagedEntity ON CS.Agent.NetworkName = BaseManagedEntity.Path INNER JOIN
AlertView ON BaseManagedEntity.Path = AlertView.MonitoringObjectDisplayName

WHERE (AlertView.ResolutionState = 0) AND AlertView.LanguageCode = (select distinct LanguageCode from LanguagePack where LanguageIsDefault = 1) AND CS.Agent.ManagementServerInd = 1 AND CS.Agent.GatewayInd = 0 AND BaseManagedEntity.FullName LIKE ‘%CSMAgent%’

ORDER BY [Server Name] ASC, [Severity] ASC, [Time Raised]

 

Enjoy.

 
Comments

All your posts are really helpful, i am actively following your valuable suggestion.

Leave a Reply