How can we get a list of all Open SCOM Alerts from the Operations Manager Database using a SQL Query?
Well there are simpler queries, but this is the query I like to use because it adds a lot more information to the output…
— Shows all Open Alerts with the Resolution State of New
SELECT * FROM dbo.fn_AlertView(‘ENU’, NULL) AS AlertView LEFT OUTER JOIN MT_Computer
ON AlertView.TopLevelHostEntityId = MT_Computer.BaseManagedEntityId
WHERE (AlertView.ResolutionState = ‘0’) AND (AlertView.Severity <> ‘0’)
If you have a better one, please let me know 🙂