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
1 comment:
Even if the opsDB is not documented, it is pretty straightforward with a bit of exercise to figure out where things are stored AND pull data out of it.
But remember it is not supported to build applications/solutions on top of it.
The database should really be exclusively read/written by the Management Servers... not by you.
If you really feel you MUST query the db directly, use at least the "WITH (NOLOCK)" clause to avoid deadlocking the db (dirty reads - http://www.codinghorror.com/blog/archives/001166.html ) - or you might cause errors to SCOM's normal processing.
Post a Comment