Query SCOM entity state

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.

Usage notes:

        > 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.

Steve 

 

-- ##### Stored Proc GetSCOMMonitorState

Create procedure GetSCOMMonitorState

@displayname varchar (100)

AS

SELECT Top 1 cast(Displayname as varchar(100)) + '_State' as 'Monitor', OpsMgrState =

      CASE HealthState

            WHEN 1 THEN 'UP'

            ELSE 'DOWN'

      END,

s.Lastmodified FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid

AND bme.displayname = @displayname order by s.lastmodified desc