Event Reporting with Power View

I spend most of my time with events troubleshooting specific problems instead of looking for trends. However, event logs contain a wealth of information about what’s happening in an environment. Events can result in alerts, too. I will get into alert analysis in the next post, but alerts and events have very similar behavior. I use the same visualization techniques for both.

In another post, “Performance Reporting with Power View” (<www.scom2k7.com/performance-reporting-with-power-view/>), I used Excel to create report views. This time I switch to SharePoint 2013 for creating views with event data from the SCOM Data Warehouse. The same Power View functions apply, but SharePoint has distinct advantages (rdlx files, Reading Mode, view duplication, export to PowerPoint) which serve larger scale reporting.

Here’s a chronological view of events tiled by management pack:

I’ll take advantage of features added with SQL Server 2012 SP1 (pie charts, drill downs) to simplify analysis. I will make use of the themes to make error counts display in red, too.

By the way, the SCOM DW contains event information (Event Number, Publisher, User Name, Logging Computer, Parameters, Rendered Description) which I haven’t included.  I designed this model for creating high level views to finds trends and tuning opportunities.  I will write about modeling specific technologies and simplifying complex variables in other posts. 

Overview

A. Add datasets to an Excel 2013 PowerPivot data model

B. Create relationships

C. Manipulate the data

D. Upload the Excel workbook to SharePoint 2013 with BI features

E. Make a Power View report

F. 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 Events

12. Copy and Paste in the following query:    
    --Events
    SELECT
    EV.DateTime
    ,EV.EventChannelRowId
    ,EV.EventLevelId
    ,ER.RuleRowId
    ,ER.ManagedEntityRowId
    FROM Event.vEvent EV
    INNER JOIN Event.vEventRule ER on ER.EventOriginId = EV.EventOriginId
    WHERE EV.DateTime > (GETUTCDATE() - 65)
    --WHERE EV.DateTime > (GETUTCDATE() + 1) -- Dummy condition to return no rows      
    Note: It is a good idea to test the date range in the query to find what works best in your environment.

    ---

     

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 Event Entities

20. Copy and Paste in the following query:
    --Event Entities
    select distinct
    ME.ManagedEntityRowId
    ,ME.ManagementGroupRowId
    ,ME.Name
    ,ME.DisplayName
    ,ME.Path
    ,ME.ManagedEntityTypeRowId
    FROM Event.vEvent EV
    inner join Event.vEventRule ER on ER.EventOriginId = EV.EventOriginId
    inner join vManagedEntity ME on ME.ManagedEntityRowId = ER.ManagedEntityRowId
    inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
    inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId

     

21. Click Finish

22. Click Close

23. Repeat steps 15-22 to create datasets Channel, Level, and Rules and MPs using these queries:
    --Channel
    SELECT *
    FROM vEventChannel

    --Level
    SELECT *
    FROM vEventLevel

    --Rules and MPs
    select distinct
    ER.RuleRowId
    ,RU.RuleDefaultName
    ,MP.ManagementPackDefaultName
    ,MP.ManagementPackSystemName
    from Event.vEventRule ER
    inner join vRule RU on RU.RuleRowId = ER.RuleRowId
    inner join vManagementPack MP on MP.ManagementPackRowId = RU.ManagementPackRowId 

B. Create relationships

1. Click Diagram View in the Ribbon
   

    

2. Click EventChannelRowId in the Events dataset and drag a line to EventChannelRowId in the Channel dataset
   

    

3. Click EventLevelId in the Events dataset and drag a line to EventLevelId in the Level dataset
   

4. Click ManagedEntityRowId in the Events dataset and drag a line to ManagedEntityRowID in the Event Entities dataset
   

5. Click RuleRowId in the Events dataset and drag a line to RuleRowId in the Rules and MPs dataset
   

6. Arrange and resize the datasets      
          
        

C. Manipulate the data

1. Click Data View in the ribbon
   
2. Select the Events dataset
        
3. Click an empty field under Add Column
            
4. Enter a DAX statement in the function field for the date
   =DATEVALUE([DateTime])
          
5. Press Enter
6. Right click CalculatedColumn1 and select Rename Column
   
7. Type Date and press Enter
8. Click an empty field under Add Column
9. Enter a DAX statement in the function field for the date and hour
   =TIME(hour([DateTime]),0,0)+[Date]
10. Right click CalculatedColumn1 and select Rename Column
11. Type Date+Hour and press Enter
12. Click an empty field in the section below the data columns
    
13. Enter a DAX statement in the function field to create a metric to count the number of events
    Event_Count:=COUNTROWS(Events)
14. Close the PowerPivot window
15. Save the workbook
16. Close the workbook

D. Upload the Excel workbook to SharePoint 2013 with BI features

1. Navigate to a SharePoint 2013 PowerPivot Gallery
   
2. Select Files then click Upload Document
    
3. Click Browse…
4. Navigate to the Excel workbook location and select the workbook
5. Click Open
6. Click OK

E. Make a Power View report

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

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

3. Expand the Level dataset in the Field List then click the checkbox next to EventLevelTitle

   

4. Click the Slicer icon in the ribbon
        

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

6. Expand the Channel dataset then click the checkbox next to EventChannelTitle

          

7. Click the Slicer icon

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

9. Expand the Events dataset

10. Click the checkboxes next to Date, Date+Hour, and Event_Count

11. Click the Column icon      
    

12. Drag the Date+Hour field from LEGEND to AXIS underneath Date
           

13. Expand the Rules and MPs dataset

14. Drag ManagementPackDefaultName to the LEGEND field
    

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

16. Click the checkboxes next to ManagementPackDefaultName and RuleDefaultName from the Rules and MPs dataset

17. Click the checkbox next to Event_Count from the Events dataset

18. Expand the Event Entities dataset

19. Click the checkbox next to Path

20. Click the Matrix icon
    

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

22. Click on a value in the Event_Count column
         

23. Select the Home tab
         

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

25. Click the Decrease Decimal icon twice
    

26. Select the matrix
         

27. Press Ctrl-C to copy the matrix

28. Press Ctrl-V to paste the matrix

29. Select the Design tab

30. Click the Pie icon
         

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

32. Select the Layout tab

33. Select the Chart Title pulldown then select None
    

34. Select the Legend pulldown then select None
           

35. Select the Styles tab

36. Select Accent 3 from the Themes section
    

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

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

Here is how my example report looks:

F. Interact with the report

1. View the volume of errors and warnings in the system log
   • Click Error in the EventLevelTitle slicer
     
   • Ctrl-click Warning in the EventLevelTitle slicer
            
   • Click System in the EventChannelTitle slicer
            
   • Undo the selections by clicking Clear Filter
                 
2. Show events by hour for the day with the highest event count
   • Double click on the tallest column in the chart
   • Go back to the daily view by clicking Drill up
      
3. Change the chart legend
   • Select the column chart object
   • Drag Path from the Managed Entity dataset to the LEGEND field (this won’t be available in Reading Mode)     
   • Click Pop out to zoom in on the chart
     
   • Click Pop in to return to the default view
            
4. Drill down to rules then to servers
   • Select an MP in the matrix then click Drill down
     
   • Double click on the corresponding pie slice
            
   • Select a rule in the matrix then click Drill down
              
   • Double click on the corresponding pie slice (or entire pie in this case)
            
   • Click a pie slice (Ctrl-click to select additional slices) to view events for that server
     

1. • Go back to the top level of each object by clicking Drill up twice

Comments

• Anonymous
  April 01, 2014
  Fantastic post.Thanks??