SCOM and Power View: Alert Duration Analysis

I use the SCOM Data Warehouse model for a variety of administrative and analytical activities. Sometimes, I add a dataset to meet a specific purpose. Besides reviewing the volume and frequency of alerts I might look at their duration (the difference between the time raised and the time closed) in order to tune them. Most commonly, I would increase the notification delay for alerts which quickly resolve themselves or override the severity to make the alert informational.

Power View supports key performance indicators (KPIs) for tables and matrices. For this scenario, I will create some measures and convert the average duration into a KPI. To make the most of the interaction between charts I will use different perspectives to gain an understanding of the alert behavior. In order to get these perspectives I added configuration data to the SCOM DW model as I did with the performance model.

Overview

A. Add Alert State Dataset to SCOM DW Model

B. Create Columns, Measures and KPI for Alert Duration

C. Create Alert Duration Report View

Step by step

A. Add Alert State Dataset to SCOM DW Model

1. Open SCOM DW Model in Excel

2. Click Manage in the Data Model section of the ribbon in the POWERPIVOT menu

   

3. Click Existing Connections in the Get External Data section of the ribbon in the Home menu

   

4. Select the SCOM_DW from the PowerPivot Data Connections

   

5. Click Open

6. Select Write a query that will specify the data to import

7. Click Next >

8. Add the following query

   --Alert State

   SELECT ARS.AlertGuid

          ,ARS.ResolutionState

          ,ARS.TimeInStateSeconds

          ,ARS.TimeFromRaisedSeconds

          ,ARS.StateSetDateTime

          ,ARS.StateSetByUserId

          ,ARS.DWCreatedDateTime

   FROM Alert.vAlertResolutionState ARS

   INNER JOIN Alert.vAlert AL on AL.AlertGuid = ARS.AlertGuid

   WHERE AL.RaisedDateTime >(GETUTCDATE() - 7)

9. Enter Alert State in the Friendly Query Name: field

10. Click Finish

11. Click Close when the import completes successfully

12. Click the Diagram View in the View section of the ribbon

    

13. Resize and arrange the datasets

14. Create a relationship by clicking and dragging the field in the From column to the field in the To column
            

    From	To
    Alert State: AlertGuid	Alerts: AlertGuid

    

B. Create Columns, Measures and KPI for Alert Duration

1. Click Data View from the View section of the ribbon

2. Add columns to the Alert State dataset and rename them            

   DAX	Column Name
   =SWITCH([ResolutionState],255,"Closed",0,"New",249,"Acknowledged",248,"Assigned to Engineering",247,"Awaiting Evidence",254,"Resolved",250,"Scheduled")	ResState_Name
   =[TimeFromRaisedSeconds]/60	TimeFromRaisedMinutes

3. Change the format for the TimeFromRaisedMinutes to Decimal Number in the Formatting section of the ribbon

    

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

   

5. Enter max_dur:=MAX([TimeFromRaisedMinutes]) in the function field (right below the ribbon) to create a measure

   

6. Add additional measures for other statistics

   Measure Name and DAX

   avg_dur:=AVERAGE([TimeFromRaisedMinutes])

   min_dur:=MIN([TimeFromRaisedMinutes])

   alert_count_rs:=DISTINCTCOUNT([AlertGuid])

7. Change the format for max_dur, avg_dur, and min_dur to Decimal Number in the Formatting section of the ribbon

8. Right click on avg_dur and select Create KPI…

   

9. Select Absolute value: under Define target value:

10. Enter 60 in the Absolute value: field

    

11. Enter 5 in the slider field between the red and yellow ranges and 15 in the slider field between the yellow and green ranges

    

12. Click OK

13. Select Refresh All from the Refresh pulldown in the ribbon in the Home menu

    

14. Click Close when the refresh is complete

15. Close the Power Pivot window

16. Click OK in the Power View dialog window

    

C. Create Alert Duration Report View

1. Click Power View From the Insert section of the ribbon in the POWER VIEW menu

2. Enter Alerts Closed in Under 30 Minutes in the Click here to add a title field

3. Adjust the text if desired

4. Expand the Alert State dataset

   

5. Drag the ResState_Name field from the Alert State dataset into the Filters section

6. Select Closed

   

7. Drag the TimeFromRaisedMinutes field from the Alert State dataset into the Filters section

8. Change to the Advanced filter mode, select is less than, enter 30, and click apply filter

   

9. Expand the Entities dataset

10. Drag the TLMEType field from the Entities dataset into the Filters section

11. Select Windows Computer

12. Expand the avg_dur field in the Alert State dataset

13. Select alert_count_rs, avg_duràStatus and avg_dur à Value from the Alert State dataset

14. Expand the Alerts dataset

15. Select AlertName from the Alerts dataset

16. Select FQDN from the Entities dataset

17. Select Matrix from the Table pulldownfrom the Switch Visualization section of the ribbon

    

18. Select Rows – Enable Drill Down One Level at a Time from the Show Levels pulldown in the Options section of the ribbon

    

19. Click on the avg_dur Status column to sort from red to green

    

20. Click in the open report view space

21. Select alert_count_rs from the Alert State dataset

22. Expand the MP Product dataset

23. Select Product from the MP Product dataset

24. Select Pie from the Other Chart pulldown in the Switch Visualization section of the ribbon

    

25. Select None from the Title pulldown in the Labels section of the ribbonin the Layout menu

    

26. Expand the pie chart to half the width of the report view

27. Change the sort order of the pie chart to use alert_count_rs in descending order

28. With the pie chart selected press Ctrl-C then Ctrl-V to cut and paste

29. Click ALL in the Power View Fields section

30. Expand the Server Info dataset

31. Drag the Environment field from the Server Info dataset to the COLOR section

32. Select Remove Field from the pulldown for the Product field in the COLOR section

    

33. Align the pie charts with room for a column chart underneath

34. Click in the open report view space

35. Select alert_count_rs from the Alert State dataset

36. Expand the Alerts dataset

37. Select Date from the Alerts dataset

38. Select Stacked Column from the Column Chart pulldown in the Switch Visualization section of the ribbon

39. Select None from the Title pulldown in the Labels section of the ribbonin the Layout menu

40. Drag the Date+Hour field from the Alerts dataset below the Date field in the AXIS section

41. Drag the Role field from the Server Info dataset to the LEGEND section

42. Resize the column chart

    

43. Click on parts of the charts or legends to highlight areas of interest (for example, the Windows alerts)

    

44. Rename the tab to Short Alerts

45. Save the workbook