Retrieve all Open SCOM Alerts from the Operations Manager SQL Database (SQL Query)

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

Use [OperationsManager]
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 🙂

 

 
Comments

QQ. Why use SQL for this instead of the built in SCOM reporting tool? I am trying to understand the benefits and gains if there are any?
Thanks.

Sometimes I like to go to SQL directly just for the pure speed of it, other times you might find cmdlets only expose some of the data that is inside the database. Either way its nice to know we have different ways of getting at our data.

Leave a Reply