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