Tuesday, June 23, 2009

SCOM 2007: Health state query

In MOM 2005, it was so easy to pull the overall state of a device from the database, but the SCOM database is a little trickier and has had me doing query after query for some time now. I finally came across this post by SCOM Bag this morning, and refined the query a little, so it looks like this:

select basemanagedentityinternalid, networkname, ipaddress, healthstate, state.lastmodified as lastdate
from basemanagedentity inner join mt_computer
on basemanagedentity.basemanagedentityid = mt_computer.basemanagedentityid
inner join state
on basemanagedentity.basemanagedentityid = state.basemanagedentityid
where IsManaged = 1
and datepart(yyyy, state.lastmodified) = datepart(yyyy, GetDate()) and datepart(mm, state.lastmodified) = datepart(mm, GetDate()) and datepart(dd, state.lastmodified) = datepart(dd, GetDate())

This will pull the state for each device in the database for current date. Not quite perfect yet, but it is a hell of a lot closer to where I want to be so I can rewrite my dashboard for SCOM

No comments:

Related Posts with Thumbnails