Alert Reporting with Power View

SCOM monitors and rules can both generate alerts.  Outside of SCOM administrators and operators, most people won’t care which method resulted in an alert.  This creates a bit of a challenge, because in previous posts I focused on data collection rules for performance and events.  For alerts, instead of dealing with monitors and rules separately, I will collapse them into workflows.  This presents its own problem, since it appears workflow IDs may be recycled between monitors and rules.  I will use DAX and the Monitor Alert Indicator value to work around this.  

Alerts typically carry an expectation that something needs to be fixed.  For this reason, it is useful to customize views for an audience.  I will spend some time using filters to remove distracting data from a view.

This filtered view shows System Center related alerts:

In the interest of clarity I will repeat the data modeling procedure from my earlier posts.  However, I will manipulate the data before creating relationships, because I am going to create a relationship between calculated columns.  For the Power View section I will use SharePoint 2013.     

Overview

A. Add datasets to an Excel 2013 PowerPivot data model

B. Manipulate the data

C. Create relationships

D. Make a Power View report

E. Interact with the report       

Step by step 

A. Add datasets to an Excel 2013 PowerPivot data model

1. Open Excel 2013

2. Click the POWERPIVOT tab

3. Click Manage

    

4. Select From Database then select From SQL Server from the pulldown
   

    

5. Enter a name like SCOM_DW in the Friendly connection name: field

6. Enter the SCOM Data Warehouse server name (with instance and port, if necessary) in the Server name: field

7. Select the name of the SCOM DW (OperationsManagerDW by default) from the Database name: pulldown

8. Click Next

9. On the How to Import the Data dialog select Write a query that will specify the data to import

10. Click Next

11. In the Friendly Query Name field type Alerts

12. Copy and Paste in the following query:    
    --Alerts
    SELECT AL.AlertName
    ,AL.AlertDescription
    ,AL.Severity
    ,AL.Priority
    ,AL.Category
    ,AL.ManagedEntityRowId
    ,AL.MonitorAlertInd
    ,AL.RaisedDateTime
    ,AL.RepeatCount
    ,AL.DWCreatedDateTime
    ,AL.WorkflowRowId
    FROM Alert.vAlert AL
    --WHERE AL.RaisedDateTime > (GETUTCDATE() + 1) -- Dummy condition to return no rows
    WHERE AL.RaisedDateTime > (GETUTCDATE() - 35)

     

13. Click Finish

14. Click Close

15. Click Existing Connections in the Ribbon       
          

     

16. Select SCOM_DW from the PowerPivot Data Connections
            

     

17. Click Open

18. Select Write a query that will specify the data to import and click Next >

19. In the Friendly Query Name type Alert Entities

20. Copy and Paste in the following query:     
    --Alert Entities
    select distinct
    ME.ManagedEntityRowId
    ,ME.Name
    ,ME.DisplayName
    ,ME.Path
    ,ME.ManagedEntityTypeRowId
    from Alert.vAlert AL
    inner join vManagedEntity ME on ME.ManagedEntityRowId = AL.ManagedEntityRowId
    inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
    inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
    order by ME.ManagedEntityTypeRowId

     

21. Click Finish

22. Click Close

23. Repeat steps 15-22 to create datasets Workflows and MPs using these queries:
    --Workflows
    (SELECT RU.RuleDefaultName as WorkflowName
    ,RU.ManagementPackRowId
    ,AL.MonitorAlertInd
    ,AL.WorkflowRowId
    FROM Alert.vAlert AL
    inner join vRule RU on RU.RuleRowId = AL.WorkflowRowId
    where AL.MonitorAlertInd = '0'
    and AL.RaisedDateTime > (GETUTCDATE() - 35))
    union
    (SELECT MON.MonitorDefaultName as WorkflowName
    ,MON.ManagementPackRowId
    ,AL.MonitorAlertInd
    ,AL.WorkflowRowId
    FROM Alert.vAlert AL
    inner join vMonitor MON on MON.MonitorRowId = AL.WorkflowRowId
    where AL.MonitorAlertInd = '1'
    and AL.RaisedDateTime > (GETUTCDATE() - 35))

    --MPs
    SELECT MP.ManagementPackRowId
    ,MP.ManagementPackDefaultName
    ,MP.ManagementPackSystemName
    FROM vManagementPack MP
    WHERE MP.ManagementPackRowId in (
    SELECT distinct RU.ManagementPackRowId
    FROM Alert.vAlert AL
    inner join vRule RU on RU.RuleRowId = AL.WorkflowRowId
    WHERE AL.MonitorAlertInd = '0'
    and AL.RaisedDateTime > (GETUTCDATE() - 35)
    )
    OR MP.ManagementPackRowId in (
    SELECT distinct MON.ManagementPackRowId
    FROM Alert.vAlert AL
    inner join vMonitor MON on MON.MonitorRowId = AL.WorkflowRowId
    WHERE AL.MonitorAlertInd = '1'
    and AL.RaisedDateTime > (GETUTCDATE() - 35)
    )

B. Manipulate the data

1. Click Data View in the ribbon
   

    

2. Select the Alerts dataset       
   

    

3. Click an empty field under Add Column
            

    

4. Enter a DAX statement in the function field for the date
   =DATEVALUE([RaisedDateTime])  

    

5. Press Enter

6. Right click CalculatedColumn1 and select Rename Column
   

    

7. Type RaisedDate and press Enter

8. Repeat steps 3-7 for the following DAX statements and column names

   DAX	Column Name
   =TIME(hour([RaisedDateTime]),0,0)+[RaisedDate]	RaisedDate+Hour
   =SWITCH([Severity],2,"Critical",1,"Warning",0,"Information")	Sev_Name
   =SWITCH([Priority],2,"High",1,"Medium",0,"Low")	Pri_Name
   =[WorkflowRowId]*10+[MonitorAlertInd]	WFID+MAI

   1.  

9. Click an empty field in the section below the data columns
    

    

10. Enter a DAX statement in the function field to create a metric to count the number of alerts
    Alert_Count:=COUNTROWS(Alerts)

11. Select the Workflows dataset
    

    1.  

12. Click an empty field under Add Column         

13. Enter a DAX statement in the function field to combine WorkflowRowId with MonitorAlertInd
    =[WorkflowRowId]*10+[MonitorAlertInd]

    1.  

14. Press Enter

15. Right click CalculatedColumn1 and select Rename Column

16. Type WFID+MAI and press Enter

17. Close the PowerPivot window

18. Save the workbook

19. Close the workbook

    1. 1. 1. 1.  

C. Create relationships

1. Click Diagram View in the Ribbon
   

    

2. Click WFID+MAI in the Alerts dataset and drag a line to WFID+MAI in the Workflows dataset      
   

    

3. Click ManagementPackRowId in the Workflows dataset and drag a line to ManagementPackRowID in the MPs dataset     
   

    

4. Click ManagedEntityRowId in the Alerts dataset and drag a line to ManagedEntityRowID in the Alert Entities dataset     
   

    

5.      

6. Arrange and resize the datasets      
    

D. Make a Power View report

1. Click the Create Power View Report icon next to the workbook 
   

    

2. Enter Alerts in the Click here to add a title textbox

3. Expand the Alerts dataset in the Field List then click the checkbox next to Sev_Name
   

    

    

4. Click the Slicer icon in the ribbon       
    

    

5. Click in the open area of the report to deselect any objects      
   

    

6. Click the checkbox next to Pri_Name in the Field List

7. Click the Slicer icon

8. Select the Sev_Name slicer

9. Click Increase Font Size twice
   

   1.  

10. Select the Pri_Name slicer

11. Click Increase Font Size twice

12. Click in the open area of the report to deselect any objects

13. Click the checkboxes next to Alert_Count and Category

14. Click the Pie icon
         

    1. 1.  

15. Change the sort order for the pie chart by clicking on the sort by values
    

    1. 1.  

16. Select the Layout tab

17. Select the Chart Title pulldown then select None
    

18. Click in the open area of the report to deselect any objects

19. Expand the Alert Entities and MPs datasets in the Field List

20. Select Alert_Count from the Alerts dataset and ManagementPackDefaultName from the MPs dataset

21. Click the Matrix icon
     

     

22. Select AlertName and AlertDescription from the Alerts dataset and DisplayName and Path from the Alert Entities dataset

23. Move AlertDescription to the bottom of the ROWS field
    

    1.  
    1.  

24. Drag Sev_Name from the Alerts dataset into the COLUMNS field
    

    1.  

25. Select the Show Levels pulldown then select Enable drill down on rows
           

     

26.     

27. Select the Home tab
         

     

28. Click on a value in the Total column
     

    1.  

29. Click the Comma Style icon in the Number section of the ribbon
         

     

30. Click the Decrease Decimal icon twice
    

     

31.     

32. Click the Text Box icon
    

    1.  

33. Enter Alert Summary in the new text box and position it over the matrix

34. Click in the open area of the report to deselect any objects

35. Select Alert_Count and RepeatCount from the Alerts dataset

36. Click the Matrix icon

37. Select AlertName from the Alerts dataset and DisplayName and Path from the Alert Entities dataset

38. Select the Show Levels pulldown then select Enable drill down on rows

39. Select the Home tab

40. Click on a value in the RepeatCount column

41. Click the Comma Style icon in the Number section of the ribbon

42. Click the Decrease Decimal icon twice

43. Repeat steps 39-41 for the Alert_Count column

44. Click the Text Box icon

45. Enter High Repeat Count Alerts in the new text box and position it over the matrix

46. Click the Show Filters icon in the upper right part of the matrix object
    

     

47.      

48. Expand RepeatCount in the Filters section
     

    1.  
    1.  

49. Click Advanced Filter Mode
    

    1. 1.  

50. Select is greater than or equal to in the top condition pulldown

51. Enter 10 in the value field

52. Click apply filter

53. Click in the open area of the report to deselect any objects

54. Drag RaisedDate from the Alerts dataset into the Filters section
    

    1. 1. 1.  

55. Drag ManagementPackDefaultName from the MPs dataset into the Filters section

56. Select the Styles tab

57. Select Accent 8 from the Themes section      
         

     

58. Select the Background pulldown and click the background on the bottom right
         

59. Arrange and resize the report objects so they don’t overlap

60. Select File –> Save

61. Enter a name without spaces in the File name: field then click Save

Here is my example report:

E. Interact with the report

1. Identify alerts and their sources with Critical severity and High priority

   • Click Critical in the Sev_Name slicer     

   • Click High in the Pri_Name slicer

   • Drill down on the management pack in the alert summary to find the alert name

   • Drill down on the alert name to identify the display name of the alert entity

   • Drill down on the alert entity to find the path

   • Go back to the Management Pack by clicking Drill up three times

   • Undo the selections by clicking Clear Filter for the slicers

      

2. Review alerts by Category
   • Click on a slice of the category pie chart or one of the legend items
   • Click in the open area of the pie chart object to clear the selection
     •  
3. Investigate alerts with high repeat counts

   • Click the RepeatCount column header to sort by value

   • Drill down on the alert name with the highest repeat count

   • Drill down on the display name to find the path

   • Go back to the alert name by clicking Drill up twice             

      

4. Change the filter settings for the report
   • Expand the ManagementPackDefaultName filter
   • Select one or more management packs from the List Filter
   • Clear the list by clicking the Clear filter icon
   • Expand the RaisedDate filter
   • Click the Advanced Filter Mode icon
   • Change the date ranges with the conditions
   • Click apply filter
   • Clear the conditions by clicking the Clear filter icon