Operations Manager Distributed Application Health Visual In Power BI (With Awesome Status Icons!)

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’

FROM            State
INNER JOIN BaseManagedEntity ON State.BaseManagedEntityId = BaseManagedEntity.BaseManagedEntityId
WHERE (BaseManagedEntity.FullName LIKE ‘Service_%’)
AND (State.HealthState BETWEEN 1 AND 4)
AND (BaseManagedEntity.Name IS NULL)
OR  (State.HealthState BETWEEN 1 AND 3)
AND (BaseManagedEntity.Name IS NULL)
AND (BaseManagedEntity.DisplayName = ‘Active Directory Topology Root’)
OR  (State.HealthState BETWEEN 1 AND 3)
AND (BaseManagedEntity.Name IS NULL)
AND (BaseManagedEntity.DisplayName = ‘Certificate Services’)
OR  (State.HealthState BETWEEN 1 AND 3)
AND (BaseManagedEntity.Name IS NULL)
AND (BaseManagedEntity.DisplayName = ‘ConfigMgr 2007 Site Hierarchy’)
OR  (State.HealthState BETWEEN 1 AND 3) AND (BaseManagedEntity.Name IS NULL)
AND (BaseManagedEntity.DisplayName = ‘Operations Manager Management Group’)
OR  (State.HealthState <> ‘0’) AND (BaseManagedEntity.DisplayName = ‘Exchange’)
GROUP BY BaseManagedEntity.DisplayName, State.HealthState, BaseManagedEntity.TopLevelHostEntityId

The Status case statement is using images from the Microsoft Office Service Status web page. We’ll use these for the status image of the table.
Creating the Power BI Query

You’ll need the Power BI Desktop which you can download HERE.
1. In the Power BI Desktop, get data and select a blank query.
2. The Query Editor will load and you will see a “Query1” on the left. Right click it and select Advanced Editor.
3. Copy and paste the below into a the blank status (replacing the Server and Database variables if not default). 
let       
    Server = “<SCOM SERVER>”,
    Database = “OperationsManager”,
    Source = Sql.Database(Server, Database, [Query=”
/*
SQL Query
SCOM Query
*/
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’
FROM            State 
INNER JOIN BaseManagedEntity ON State.BaseManagedEntityId = BaseManagedEntity.BaseManagedEntityId
WHERE (BaseManagedEntity.FullName LIKE ‘Service_%’) 
AND (State.HealthState BETWEEN 1 AND 4) 
AND (BaseManagedEntity.Name IS NULL) 
OR  (State.HealthState BETWEEN 1 AND 3) 
AND (BaseManagedEntity.Name IS NULL) 
AND (BaseManagedEntity.DisplayName = ‘Active Directory Topology Root’) 
OR  (State.HealthState BETWEEN 1 AND 3) 
AND (BaseManagedEntity.Name IS NULL) 
AND (BaseManagedEntity.DisplayName = ‘Certificate Services’) 
OR  (State.HealthState BETWEEN 1 AND 3) 
AND (BaseManagedEntity.Name IS NULL) 
AND (BaseManagedEntity.DisplayName = ‘ConfigMgr 2007 Site Hierarchy’) 
OR  (State.HealthState BETWEEN 1 AND 3) AND (BaseManagedEntity.Name IS NULL) 
AND (BaseManagedEntity.DisplayName = ‘Operations Manager Management Group’) 
OR  (State.HealthState <> ‘0’) AND (BaseManagedEntity.DisplayName = ‘Exchange’)
GROUP BY BaseManagedEntity.DisplayName, State.HealthState, BaseManagedEntity.TopLevelHostEntityId
/* End SQL Query */
“])
in
    #”Source”
3. If you’ve not connected before, you will need to provide credentials to connect to the SCOM database.
4. Once loaded you should see a nice table with the Application, Health State, State and Image.
5. Close the Query Editor and select yes to apply now.
6. Once the query has run, it will return you back to the design and you will see the query has loaded.
7. Select the Status column and change the data category to image URL.
8. Tick the Application, State and Status and you’ll have a nice health status for your distributed app :).

Leave a Reply

Your email address will not be published. Required fields are marked *