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” (http://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:

image

 

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 Managepic004_thumb1

     

  4. Select From Database then select From SQL Server from the pulldown
    pic005_thumb1[1]

     

  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       
    pic007_thumb      

     

  16. Select SCOM_DW from the PowerPivot Data Connections
    pic008_thumb1[1]        

     

  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
    pic009_thumb1

     

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

     

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

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

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

  6. Arrange and resize the datasets      
    image       
         

C. Manipulate the data

  1. Click Data View in the ribbon
    image
  2. Select the Events dataset
    image     
  3. Click an empty field under Add Column
    image         
  4. Enter a DAX statement in the function field for the date
    =DATEVALUE([DateTime])
    image       
  5. Press Enter
  6. Right click CalculatedColumn1 and select Rename Column
    image
  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
    image
  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
    image
  2. Select Files then click Upload Document
    image 
  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
    image

  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

    image

  4. Click the Slicer icon in the ribbon
    image     

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

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

    image       

  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      
    image

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

  13. Expand the Rules and MPs dataset

  14. Drag ManagementPackDefaultName to the LEGEND field
    image

  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
    image

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

  22. Click on a value in the Event_Count column
    image     

  23. Select the Home tab
    image     

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

  25. Click the Decrease Decimal icon twice
    image

  26. Select the matrix
    image     

  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
    image     

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

  32. Select the Layout tab

  33. Select the Chart Title pulldown then select None
    image

  34. Select the Legend pulldown then select None
    image       

  35. Select the Styles tab

  36. Select Accent 3 from the Themes section
    image

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

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

Here is how my example report looks:

image

F. Interact with the report

  1. View the volume of errors and warnings in the system log
    • Click Error in the EventLevelTitle slicer
      image
    • Ctrl-click Warning in the EventLevelTitle slicer
      image       
    • Click System in the EventChannelTitle slicer
      image       
    • Undo the selections by clicking Clear Filter
      image            
  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
      image 
  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
      image
    • Click Pop in to return to the default view
      image       
  4. Drill down to rules then to servers
    • Select an MP in the matrix then click Drill down
      image
    • Double click on the corresponding pie slice
      image       
    • Select a rule in the matrix then click Drill down
      image         
    • Double click on the corresponding pie slice (or entire pie in this case)
      image       
    • Click a pie slice (Ctrl-click to select additional slices) to view events for that server
      image

   

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