Here's another useful query, this gets the current state and date of last change for a specified object (basemanagedentity). I used this one to get state change information out of SCOM into another management product where there's no connector out there already. You could of course use the notification methods but I found that these could be flaky, dropping > 5 concurrent notifications, no guaranteed delivery etc. The last thing you want in a monitoring system is to not get notification of an important state change!
This query creates a
stored procedure called ‘GetSCOMMonitorState. This procedure takes one argument '@displayname'
and returns current state of the entity as ‘UP’ or ‘DOWN’. It also returns the
last modified date so you can see when it last changed state.
> No apologies for the scrappy SQL, variable sizes etc :-) > As with any script you should modify and check this before using in your production environment > Run usage.... exec GetSCOMMonitorState 'Temperature monitor 3, Comp room 4'
Any questions give me a shout.
-- ##### Stored
SELECT Top 1 cast(Displayname
as varchar(100)) + '_State' as 'Monitor', OpsMgrState =
WHEN 1 THEN 'UP'
FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid
= @displayname order by s.lastmodified desc