SCOM SLA Dashboards with Power BI for O365 – Part 1

First I would like to mention that if you don’t need connectivity to On Premise data you can sign up for a free Power BI account read more about it here.

Before you can jump into building these reports you will need to configure the Service Level Target (SLT) and Service Level Objectives (SLO) in SCOM here is a good walk through by Kevin Green of how to configure the SLT and SLO.

Part 1 – Setting up the data for our dashboard

First we are going to create a table to store our data for this report.

Create Table PowerBIDASLTDashboard(
[ID] [int] Identity(1,1) Not Null,
ManagedEntityRowID int,
ManagementGroupGUID varchar(50),
ManagementGroupDisplayName varchar(50),
ManagedEntityTypeGuid varchar(50),
ManagedEntitiyTypeDisplayName varchar(50),
ManagedEnitiyDisplayName varchar(50),
ManagedEntityPath varchar(50),
Goal int,
DesiredObjective varchar(50),
AggregationMethod varchar(50),
[DateTime] DateTime,
ActualValue varchar(50),
[Weight] int,
TotalDowntimeMilliseconds int,
AggregatedValue decimal,
Rule_MonitorDisplayName varchar(50),
MonitorServiceLevelObjectiveInd int,
STLTimeFilter varchar(20),
Constraint [PK_PowerBIDASLTDashboard] Primary Key
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY])

Next create the following stored procedure.

USE [OperationsManagerDW]
GO
/****** Object:  StoredProcedure [dbo].[PowerBIReport]    Script Date: 2/4/2015 12:54:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[PowerBIReport]
     @StartDate                     datetime
    ,@EndDate                       datetime
    ,@ServiceLevelAgreementManagedEntityRowId int
    ,@ServiceLevelObjectiveGuid     uniqueidentifier
    ,@AggregationTypeId             int
    ,@LanguageCode                  varchar(3) ='ENU'
    ,@SLTTimeFilter                    varchar(20)
AS
BEGIN
  SET NOCOUNT ON
  
  DECLARE 
     @ErrorInd        bit
    ,@ErrorMessage    nvarchar(max)
    ,@ErrorNumber     int
    ,@ErrorSeverity   int
    ,@ErrorState      int
    ,@ErrorLine       int
    ,@ErrorProcedure  nvarchar(256)
    ,@ErrorMessageText nvarchar(max)

  SET @ErrorInd = 0
  
  DECLARE
     @Level int
    ,@RowCount int
    ,@ManagementGroupRowId int
    ,@ContainmentRelationshipTypeRowId int
  
  BEGIN TRY
    -- build a list of all containment relationships
    CREATE TABLE #ContainmentRelationshipType (
       RelationshipTypeRowId  int PRIMARY KEY
    )
    
    SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId
    FROM RelationshipType
    WHERE RelationshipTypeSystemName = 'System.Containment'
    
    IF (@ContainmentRelationshipTypeRowId IS NOT NULL)
    BEGIN
      INSERT #ContainmentRelationshipType (RelationshipTypeRowId)
      SELECT RelationshipTypeRowId
      FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
    END

    -- build a list of entities contained in a given Sla entity    
    CREATE TABLE #ContainedManagedEntity (
       ManagedEntityRowId             int   NOT NULL
      ,[Level]                        int   NOT NULL
    )
    
    INSERT #ContainedManagedEntity (
       ManagedEntityRowId
      ,[Level]
    )
    VALUES (
       @ServiceLevelAgreementManagedEntityRowId
      ,0
    )

    -- recursively walk down containment relationship chain
    SET @Level = 1
    SET @RowCount = 1
    
    WHILE (@RowCount > 0)
    BEGIN
      INSERT #ContainedManagedEntity (
         ManagedEntityRowId
        ,[Level]
      )
      SELECT
         r.TargetManagedEntityRowId
        ,@Level
      FROM #ContainedManagedEntity source_me
            JOIN vRelationship r ON (source_me.ManagedEntityRowId = r.SourceManagedEntityRowId)
            JOIN #ContainmentRelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
      WHERE (source_me.Level = @Level - 1)
        AND EXISTS (SELECT * 
                    FROM vRelationshipManagementGroup rmg
                    WHERE (rmg.RelationshipRowId = r.RelationshipRowId)
                      AND (rmg.FromDateTime < ISNULL(@EndDate, '99991231'))
                      AND (ISNULL(rmg.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
                   )
      
      SET @RowCount = @@ROWCOUNT
      SET @Level = @Level + 1
    END

    -- build a list of Slo entities
    CREATE TABLE #ServiceLevelObjectiveManagedEntity (
       ServiceLevelObjectiveRowId               int NOT NULL
      ,ManagementGroupRowId                     int NOT NULL
      ,ServiceLevelObjectiveManagedEntityRowId  int NOT NULL
    )
    
    SELECT @ManagementGroupRowId = (SELECT ManagementGroupRowId FROM vManagedEntity WHERE ManagedEntityRowId = @ServiceLevelAgreementManagedEntityRowId)
    
    INSERT #ServiceLevelObjectiveManagedEntity (
       ServiceLevelObjectiveRowId
      ,ManagementGroupRowId
      ,ServiceLevelObjectiveManagedEntityRowId
    )
    SELECT DISTINCT
       slo.ServiceLevelObjectiveRowId
      ,@ManagementGroupRowId
      ,me.ManagedEntityRowId
    FROM #ContainedManagedEntity me
          CROSS JOIN vServiceLevelObjective slo
          JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slo.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)
          CROSS APPLY dbo.ManagedEntityDerivedTypeHierarchy(slompv.TargetManagedEntityTypeRowId, 0) as t
    WHERE EXISTS (SELECT *
                  FROM vTypedManagedEntity tme 
                  WHERE (t.ManagedEntityTypeRowId = tme.ManagedEntityTypeRowId)
                    AND (me.ManagedEntityRowId = tme.ManagedEntityRowId)
                    AND (tme.FromDateTime < ISNULL(@EndDate, '99991231'))
                    AND (ISNULL(tme.ToDateTime, '99991231') > ISNULL(@StartDate, '19000101'))
                 )
      AND (slo.ServiceLevelObjectiveGuid = @ServiceLevelObjectiveGuid)
      AND NOT EXISTS (SELECT *
                      FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
                            JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
                      WHERE (slompv_newer.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)
                        AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
                        AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
                     ) -- this is the latest SLO definition for this MG
                 
    DROP TABLE #ContainmentRelationshipType
    DROP TABLE #ContainedManagedEntity
    
    -- result table
    CREATE TABLE #ServiceLevelObjectiveManagedEntityDetail (
         ServiceLevelObjectiveRowId       int       NOT NULL
        ,ManagementGroupRowId             int       NOT NULL
        ,ServiceLevelObjectiveManagedEntityRowId int NOT NULL
        ,[DateTime]                       datetime  NOT NULL
        ,ActualValue                      float     NOT NULL
        ,Weight                           int       NOT NULL
        ,TotalDowntimeMilliseconds        int       NULL
    )

    -- details data get SP looks for #ServiceLevelObjectiveManagedEntity
    -- table to pull up MEs it needs to work with
    INSERT #ServiceLevelObjectiveManagedEntityDetail (
         ServiceLevelObjectiveRowId
        ,ManagementGroupRowId
        ,ServiceLevelObjectiveManagedEntityRowId
        ,[DateTime]
        ,ActualValue
        ,Weight
        ,TotalDowntimeMilliseconds
    )
    EXEC sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet]
         @StartDate
        ,@EndDate
        ,@AggregationTypeId
        
      DROP TABLE #ServiceLevelObjectiveManagedEntity
      
      Insert INto PowerBIDASLTDashboard Select 
         ManagedEntityRowId = slome.ServiceLevelObjectiveManagedEntityRowId
        ,ManagementGroupGuid = (SELECT ManagementGroupGuid FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
        ,ManagementGroupDisplayName = (SELECT ManagementGroupDefaultName FROM ManagementGroup WHERE ManagementGroupRowId = @ManagementGroupRowId)
        ,met.ManagedEntityTypeGuid
        ,ManagedEntityTypeDisplayName = ISNULL(met_ds.[Name], met.ManagedEntityTypeDefaultName)
        ,ManagedEntityDisplayName = me.DisplayName
        ,ManagedEntityPath = me.Path
        ,Goal = 
          CASE
          WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN mslompv.Goal
          WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.Threshold
        END
        ,DesiredObjective = 
          CASE
          WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'over'
          WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.DesiredObjective
        END
        ,AggregationMethod = 
          CASE
          WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN 'avg'
          WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN pslompv.AggregationMethod
        END
        ,slome.[DateTime]
        ,slome.ActualValue
        ,slome.Weight
      ,slome.TotalDowntimeMilliseconds
      ,AggregatedValue =
        CASE
          WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN tempAggrValues.AvgValue
          WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN 
                CASE pslompv.AggregationMethod
                    WHEN 'avg' THEN tempAggrValues.AvgValue
                    WHEN 'min' THEN tempAggrValues.MinValue
                    WHEN 'max' THEN tempAggrValues.MaxValue
                END
        END
        ,Rule_MonitorDisplayName =
        CASE
          WHEN slompv.MonitorServiceLevelObjectiveInd = 1 THEN ISNULL(vDisplayStringMonitor.Name, m.MonitorDefaultName) 
          WHEN slompv.PerformanceServiceLevelObjectiveInd = 1 THEN ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName)        
        END        
        ,slompv.MonitorServiceLevelObjectiveInd
        ,SLTTimeFilter = @SLTTimeFilter
      FROM 
       #ServiceLevelObjectiveManagedEntityDetail slome
            JOIN vManagedEntity me ON (slome.ServiceLevelObjectiveManagedEntityRowId = me.ManagedEntityRowId)
            JOIN vManagedEntityType met ON (me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
            JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
            JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
             LEFT JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (mslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
            LEFT JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (pslompv.ServiceLevelObjectiveManagementPackVersionRowId = slompv.ServiceLevelObjectiveManagementPackVersionRowId)
          LEFT JOIN vDisplayString met_ds ON (met.ManagedEntityTypeGuid = met_ds.ElementGuid) AND (met_ds.LanguageCode = @LanguageCode)
          JOIN (
            SELECT 
                ServiceLevelObjectiveRowId = slome1.ServiceLevelObjectiveRowId
                ,ServiceLevelObjectiveManagedEntityRowId
                ,AvgValue = sum(slome1.ActualValue * slome1.Weight)/sum(slome1.Weight)
                ,MinValue = min(slome1.ActualValue)
                ,MaxValue = max(slome1.ActualValue)
            FROM #ServiceLevelObjectiveManagedEntityDetail slome1
            GROUP BY slome1.ServiceLevelObjectiveManagedEntityRowId, slome1.ServiceLevelObjectiveRowId
        ) as tempAggrValues  ON (slome.ServiceLevelObjectiveManagedEntityRowId = tempAggrValues.ServiceLevelObjectiveManagedEntityRowId)
        LEFT JOIN vRule ON (pslompv.RuleRowId = vRule.RuleRowId)
        LEFT JOIN vMonitor AS m ON mslompv.MonitorRowId = m.MonitorRowId
        LEFT JOIN vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND 
                                                        vDisplayStringRule.LanguageCode = @LanguageCode
        LEFT JOIN vDisplayString AS vDisplayStringMonitor ON m.MonitorGuid = vDisplayStringMonitor.ElementGuid AND 
                                                        vDisplayStringMonitor.LanguageCode = @LanguageCode
        WHERE NOT EXISTS (SELECT *
                          FROM vServiceLevelObjectiveManagementPackVersion slompv_newer
                                JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
                          WHERE (slompv_newer.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)
                            AND (mgmpv.ManagementGroupRowId = @ManagementGroupRowId)
                            AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)
                         ) -- this is the latest SLO definition for this MG


      DROP TABLE #ServiceLevelObjectiveManagedEntityDetail
  END TRY
  BEGIN CATCH
    SELECT 
       @ErrorNumber = ERROR_NUMBER()
      ,@ErrorSeverity = ERROR_SEVERITY()
      ,@ErrorState = ERROR_STATE()
      ,@ErrorLine = ERROR_LINE()
      ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
      ,@ErrorMessageText = ERROR_MESSAGE()

    SET @ErrorInd = 1
  END CATCH

Cleanup:

  -- cleanup
  IF (OBJECT_ID('#ContainmentRelationshipType') IS NOT NULL)
    DROP TABLE #ContainmentRelationshipType
    
  IF (OBJECT_ID('#ContainedManagedEntity') IS NOT NULL)
    DROP TABLE #ContainedManagedEntity
    
  IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntity') IS NOT NULL)
    DROP TABLE #ServiceLevelObjectiveManagedEntity
    
  IF (OBJECT_ID('#ServiceLevelObjectiveManagedEntityDetail') IS NOT NULL)
    DROP TABLE #ServiceLevelObjectiveManagedEntityDetail

  -- report error if any
  IF (@ErrorInd = 1)
  BEGIN
    DECLARE @AdjustedErrorSeverity int

    SET @AdjustedErrorSeverity = CASE
                                   WHEN @ErrorSeverity > 18 THEN 18
                                   ELSE @ErrorSeverity
                                 END
    
    RAISERROR (777971002, @AdjustedErrorSeverity, 1
      ,@ErrorNumber
      ,@ErrorSeverity
      ,@ErrorState
      ,@ErrorProcedure
      ,@ErrorLine
      ,@ErrorMessageText
    )
  END
END

Now run the following query to get the information about the SLTs and SLOs that are configured in SCOM, we will use this information to build another stored procedure.

With temp_sla AS (

Select sla.ServiceLevelAgreementRowId, sla.ServiceLevelAgreementGuid, (Select Mg.ManagementGroupRowId From vManagementGroup mg) as  'ManagementGroupRowId', (Select Mg.ManagementGroupGuid From vManagementGroup mg) as  'ManagementGroupGuid'
From vServiceLevelAgreement sla

 ) 

 
 Select distinct
 ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId 
          ,ServiceLevelAgreementDisplayName = ISNULL(sla_ds.[Name], sla.ServiceLevelAgreementDefaultName)
          ,ServiceLevelAgreementTargetManagedEntityTypeDisplayName = ISNULL(sla_target_met_ds.[Name], met.ManagedEntityTypeDefaultName)
          ,ManagementGroupDisplayName = mg.ManagementGroupDefaultName
          ,met.ManagedEntityTypeRowId
          ,me.DWCreatedDateTime
          ,me.ManagedEntityRowId
          ,slo.ServiceLevelObjectiveGuid
    FROM temp_sla
        JOIN ManagementGroupManagementPackVersion mgmpv ON (temp_sla.ManagementGroupRowId = mgmpv.ManagementGroupRowId)
        JOIN ServiceLevelAgreementManagementPackVersion slampv ON (slampv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
        JOIN ServiceLevelAgreement sla ON (sla.ServiceLevelAgreementRowId = slampv.ServiceLevelAgreementRowId)
        JOIN ManagedEntityType met ON (slampv.TargetManagedEntityTypeRowId = met.ManagedEntityTypeRowId)
        JOIN ManagementGroup mg ON (mgmpv.ManagementGroupRowId = mg.ManagementGroupRowId)
        Join ManagedEntity me ON Me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId
        Join ServiceLevelObjectiveManagementPackVersion slompv ON slompv.ServiceLevelAgreementRowId = sla.ServiceLevelAgreementRowId
        Join ServiceLevelObjective slo ON slo.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId 
        AND (slampv.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
        LEFT JOIN vDisplayString sla_ds ON (sla.ServiceLevelAgreementGuid = sla_ds.ElementGuid) AND (sla_ds.LanguageCode = 'ENU')
        LEFT JOIN vDisplayString sla_target_met_ds ON (met.ManagedEntityTypeGuid = sla_target_met_ds.ElementGuid) AND (sla_target_met_ds.LanguageCode = 'ENU')
    WHERE NOT EXISTS (SELECT *
                      FROM vServiceLevelAgreementManagementPackVersion slampv_newer
                            JOIN vManagementGroupManagementPackVersion mgmpv ON (slampv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)
                      WHERE (slampv_newer.ServiceLevelAgreementRowId = temp_sla.ServiceLevelAgreementRowId)
                        AND (mgmpv.ManagementGroupRowId = temp_sla.ManagementGroupRowId)
                        AND (slampv_newer.ServiceLevelAgreementManagementPackVersionRowId > slampv.ServiceLevelAgreementManagementPackVersionRowId)
                        )

The results will look something like this

clip_image002

This provides the mapping for each SLT to SLOGUID and ManagedEntityRowID, if you are using more than one SLO per SLT you may need to modify the query a bit more, I am only reporting on one SLO so this query works for my dashboards.

In the next blog post I will show you how to use the stored procedure in this post to populate the table we created and then build a Power BI dashboard with your SCOM SLA data.

2 comments

    1. Part two is in progress but I want to port the solution to the Power BI designer/powerbi.com, currently it is built on Power BI for O365.

Leave a Reply

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