Back in 2011 Steve Beaumont created an awesome post on SCOM Distributed App Dashboard. I decided to use this post to replicate the dashboard in Power BI!

The SQL Query

To retrieve the information required, we’ll use the below updated SQL script to pull back the information required.

SELECT BaseManagedEntity.DisplayName AS ‘Application’,MAX(State.HealthState) AS ‘Health State’,BaseManagedEntity.TopLevelHostEntityId,CASE WHEN State.HealthState LIKE ‘1’ Then ‘Healthy’ WHEN State.HealthState LIKE ‘2’ Then ‘Warning’ WHEN State.HealthState LIKE ‘3’ Then ‘Critical’ END AS ‘State’,CASE WHEN State.HealthState LIKE ‘1’ Then ‘https://osiprodweuodcspstoa01.blob.core.windows.net/en-us/media/bf6251a9-ac72-4b7d-9c0e-3734020719b7.png’ WHEN State.HealthState LIKE ‘2’ Then ‘https://osiprodweuodcspstoa01.blob.core.windows.net/en-us/media/5c168ec7-e55d-404d-90a4-807eba849eaa.png’ WHEN State.HealthState LIKE ‘3’ Then ‘https://osiprodweuodcspstoa01.blob.core.windows.net/en-us/media/6f881bcc-e285-4b73-b537-fd3b5e0c70b9.png’ END AS ‘Status’