Wednesday 11 May 2011

SCOM Distributed App Health Dashboard

Since I've been doing a bit of work recently on System Center Dashboards I thought I'd quickly post this snippet that I've just done.

SELECT  distinct   BaseManagedEntity.DisplayName, case State.HealthState when 1 then 'Healthy' when 2 then 'Warning' when 3 then 'Critical' when 4 then 'Not Monitored' else 'Unknown' end as Status
FROM State INNER JOIN
BaseManagedEntity ON State.BaseManagedEntityId = BaseManagedEntity.BaseManagedEntityId
where (BaseManagedEntity.FullName like 'Service_%'
or BaseManagedEntity.DisplayName = 'Active Directory Topology Root'
or BaseManagedEntity.DisplayName = 'Certificate Services'
or BaseManagedEntity.DisplayName = 'ConfigMgr 2007 Site Hierarchy'
or BaseManagedEntity.DisplayName = 'Operations Manager Management Group')
and BaseManagedEntity.Name is NULL
or (BaseManagedEntity.DisplayName = 'Exchange Service' and State.HealthState <> '0')

This gives a result that looks like this:

If you take out the case statement so it returns 1,2 & 3's instead of Healthy, Warning & Critical then this SQL snippet can be used to create a RAG scorecard in the System Center Dashboard that produces a result like this:


Also (for my reference) Kevin Holman has a ton of useful SCOM SQL queries:
http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx

7 comments:

Kevin Greene said...

Hi Steve,

I'm lovin this post on SCOM DA dashboarding! I've done quite a bit with the SCCM and SCSM dashboards within SCOM for my clients but didn't have the SQL 'Kung Fu' to tie in DA's into it until I saw your blog post today.

I am trying to get the scorecard working the way you have in your diagram but it keeps giving me an error of :

'There are no numeric data in the defined query to show on a graph'

I'm sure I am missing something simple but if you could provide me with more information or even the SQL query that you used for the scorecard, I'd be much obliged.

Thanks in advance,

Kevin!

Kevin Greene said...

Hi again Steve,

I've just managed to figure it out so no need to come back to me with the solution. I've posted it below for anyone else that is reading this:

SELECT distinct BaseManagedEntity.DisplayName, State.HealthState
FROM State INNER JOIN
BaseManagedEntity ON State.BaseManagedEntityId = BaseManagedEntity.BaseManagedEntityId
where (BaseManagedEntity.FullName like 'Service_%'
or BaseManagedEntity.DisplayName = 'Active Directory Topology Root'
or BaseManagedEntity.DisplayName = 'Certificate Services'
or BaseManagedEntity.DisplayName = 'ConfigMgr 2007 Site Hierarchy'
or BaseManagedEntity.DisplayName = 'Operations Manager Management Group')
and BaseManagedEntity.Name is NULL
or (BaseManagedEntity.DisplayName = 'Exchange Service' and State.HealthState <> '0')

Thanks again for the excellent post,

Kevin.

Steve Beaumont said...

Hi Kevin,
I was wondering what was wrong there for a while, but then I twigged too.

As I mentioned in the post, when you're adding the SQL to the dashboard, you need to chop the case statements out so that the dashboard has the numerical values to work with, sorry, should have put that bit in bold!

Glad you liked it btw!

Cheers,
SB

Kevin Greene said...

Hi Steve,

I liked your solution that much that I put together my own SCOM dashboarding series of blog posts.

http://kevingreeneitblog.blogspot.com/2011/07/scom-dashboards-intro.html

I've given you the credit of course for the Distributed Application Dashboard and also linked to your blog.

Hope you don't mind!

Kev.

Steve Beaumont said...

I don't mind at all Kevin, and I must say, what an amazing set of posts you've done!!

I'll create a new post to reference yours if that's ok?

Cheers,
SB

Kevin Greene said...

I don't mind at all Steve, I appreciate the comments and thanks again for your original post!

Keep up the good work!

Anonymous said...

tI love your guys input on this. I am currently working on a project to make a dash similar to yours with SCOM 2007 R2. I am having a problem though which is puzzling me. I am getting multiple healthstate for each distributed application i add to the query. One that i know is critical, i added and it shows in the preview data window 3x and heathstate 0,1,3.

Any idea why?