Using the Data Warehouse Part 2 – Event Data

Using the Data Warehouse Part 2 – Event Data

  • Comments 1
  • Likes

Following on from the previous blog(now updated with Alert schema) showing how to query the data warehouse for Alert data, this post will cover utilizing Event Data.

Out of the box, we provide 3 main Event reports.

Event Analysis Report
Allows you to show a tabular list of events for a given computer/group of computers and provide filters for Event Source, Event Category, Event Type or Event ID.
Click for link to Event Analysis report

Custom Event Report
Like the Event Analysis reports shows a tabular list of Event but can be configured to only show certain Event fields which can be filtered.

Most Common Events Report
This report is extremely useful when you are trying to fine tune your environment to ensure your operators are spending their time working on priority issues. Filtered by Management Pack you can show the most common events by percentage of total.
Click for link to Most Common Events report

These reports provide a good start in analysing Event data but there may be cases where you need greater control over the information you need to analyse. Perhaps you are just looking for total event counts for a given Event ID across a set of computers.

We can then use the data warehouse to query for this data. Lets start with the schema.

Click here to open Event schema diagram.

Differing from other datasets, the event data does not only get stored against a particular Managed Entity but against the LoggingComputer as shown in the vEventLoggingComputer view. This makes the Event dataset visually easier to understand and therefore query.

The query example below looks for all “Service Terminated(7024)” events across all computers.

SELECT           

      vEvent.DateTime,

      vEventPublisher.EventPublisherName as 'EventSource',

      vEventLoggingComputer.ComputerName as 'Computer',

      vEventLevel.EventLevelTitle as 'Type',

      vEvent.EventDisplayNumber as 'EventID',

      vEventChannel.EventChannelTitle,

      vEventUserName.UserName,

      vEventDetail.RenderedDescription as 'EventDescription'

FROM

      Event.vEvent INNER JOIN

      vEventUserName ON vEvent.UserNameRowId =  

      vEventUserName.EventUserNameRowId INNER JOIN

      vEventCategory ON vEvent.EventCategoryRowId =  

      vEventCategory.EventCategoryRowId INNER JOIN

      vEventPublisher ON vEvent.EventPublisherRowId =

      vEventPublisher.EventPublisherRowId INNER JOIN

      vEventLoggingComputer ON vEvent.LoggingComputerRowId =

      vEventLoggingComputer.EventLoggingComputerRowId INNER JOIN

      vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId INNER JOIN

      vEventChannel ON vEvent.EventChannelRowId =

      vEventChannel.EventChannelRowId INNER JOIN

      Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId

WHERE vEvent.EventDisplayNumber = '7024'

ORDER BY vEvent.DateTime, vEventLoggingComputer.ComputerName

The next query shows how you can see what Operations Manager rules are generating the most Events.

SELECT    

      Count(Event.vEvent.EventOriginID) as TotalEvents,

      vEventPublisher.EventPublisherName AS 'EventSource',

      vEventLoggingComputer.ComputerName AS 'Computer',

      Event.vEvent.EventDisplayNumber AS 'EventID',

      vEventChannel.EventChannelTitle,

      Event.vEventDetail.RenderedDescription AS 'EventDescription',

      vRule.RuleSystemName, vRule.RuleDefaultDescription

FROM         Event.vEvent INNER JOIN

   Event.vEventDetail ON Event.vEvent.EventOriginId = Event.vEventDetail.EventOriginId INNER JOIN

   vEventPublisher ON Event.vEvent.EventPublisherRowId = vEventPublisher.EventPublisherRowId INNER JOIN

   vEventChannel ON Event.vEvent.EventChannelRowId = vEventChannel.EventChannelRowId INNER JOIN

   vEventLoggingComputer ON Event.vEvent.LoggingComputerRowId = vEventLoggingComputer.EventLoggingComputerRowId INNER JOIN

   Event.vEventRule ON Event.vEvent.EventOriginId = Event.vEventRule.EventOriginId INNER JOIN

   vRule ON Event.vEventRule.RuleRowId = vRule.RuleRowId

WHERE     (Event.vEvent.EventDisplayNumber = '7024')

GROUP BY

      vEventPublisher.EventPublisherName,

      vEventLoggingComputer.ComputerName,

      Event.vEvent.EventDisplayNumber,

      vEventChannel.EventChannelTitle,

      Event.vEventDetail.RenderedDescription,

      vRule.RuleSystemName, vRule.RuleDefaultDescription

ORDER BY  'TotalEvents' DESC, 'Computer'

These query examples have not been performance tuned, you should evaluate your dataset size and tune the queries accordingly so as not to affect DW performance.

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included utilities are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Daniel Savage

Program Manager | System Center Operations Manager

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Daniel, very useful post thank you.  I'm trying to use OpsMgr to further extract the XML eventdata that I've collected using a CSV log processing rule.  The three canned reports you mention don't seem to allow processing inside of the XML event data - so I've added to your query to use SQL xquery to extract the information.

    Question: is there a better or easier way already in OpsMgr, as I would like to trend this information as a standard report if possible.  Thanks!

    vEventDetail.EventData.value('(/EventData/DataItem/Params/Param[1])[1]', 'nvarchar(16)'),

         vEventDetail.EventData.value('(/EventData/DataItem/Params/Param[2])[1]', 'int'),

         vEventDetail.EventData as 'EventData'