Operations Manager Cross-Group Blog

Tips and tricks from the Microsoft Operations Manager Cross-Group Team Members

How to store SYSLOG in the SCOM Data Warehouse

How to store SYSLOG in the SCOM Data Warehouse

  • Comments 10
  • Likes

If you have ever tried to create a Syslog collection rule in the SCOM Authoring Console, this is the module sequence that it creates this:

This technically works, but your SYSLOG events are not very searchable.  The Logging Computer shows up as blank, the Event ID is always zero, and the Rendered Description is blank.  The full data item is stored in the EventData column of the Event.vEventDetail view as XML.  Searching XML in TSQL isn't as easy as a character column, and sorting out duplicate events is made harder by the inclusion of the time stamp in the EventData column.

The solution is to include a System.Event.GenericDataMapper as a Condition Detection Module.  This allows us to select how individual elements of the System.ApplicationLog.SysLogData item output by the Data Source Module are mapped into the System.EventData of the Write Action module

You may note that I almost always remove the Write Action from my collection rules that writes to the Operations Database.  Here is the configuration that I used:

  <EventOriginId>$Target/Id$</EventOriginId>
  <PublisherId>$MPElement$</PublisherId>
  <PublisherName>$Data/EventData/DataItem/PriorityName$</PublisherName>
  <Channel>Syslog</Channel>
  <LoggingComputer>$Data/EventData/DataItem/HostName$</LoggingComputer>
  <EventNumber>$Data/EventData/DataItem/Severity$</EventNumber>
  <EventCategory>0</EventCategory>
  <EventLevel>2</EventLevel>
  <UserName></UserName>
  <Description>$Data/EventData/DataItem/Message$</Description>
  <Params></Params>

There is not an easy way to directly map the eight severity levels of a SYSLOG to the levels of a Windows event.  So I just hard-coded a value of "2" for Warning.  Since the severity is used for the event ID, that met my needs for easy searching.  If you wanted to, you could create three separate collection rules and filter SYSLOG severities 0-3, 4-5, and 6-7 into three separate collection rules where you hard-code the event levels to Error, Warning, and Informational.

Comments
  • I'm trying to follow your instructions, but I can't seem to get to a dialog box that has a module listing.  When I create or edit a syslog event rule, the tabs I get are: General, Configuration, Product Knowledge, Overrides.  Moreover, on the Configuration tab, the Condition area is greyed out and cannot be edited.  Your screen shots show: General, Modules, Product Knowledge, Options.  Are you using an authoring tool?  I'm new to SCOM and am probably missing something obvious, but I'm clueless as to how to get to the screens you have in this post.

  • I am using the System Center Operations Manager 2007 R2 Authoring Resource Kit which can be found here.

    www.microsoft.com/.../details.aspx

    The management packs that it generates can be used in SCOM 2007 R2 and also SCOM 2012.

  • I've got this working. I decided to leave the action to store events in the operations manager database so that I could see an event view in the monitoring area.

    There seems to be a problem with the Description field. Every time a new event arrives, it overwrites the description field for all similar events from the same network device. Any thoughts about that?

  • Also, do you have an example of how you retrieve this info from the data warehouse?

  • Yes, trying to search the event data XML is nasty and inefficient. SQL queries take FOREVER! I've finally come back to this out of necessity because we now want to search and report on our syslogs. The above example is perfectly shown if you want to be able to search and report on syslogs from your data warehouse. What is particularly important is removing the write action for the Operations Manager database and only using the write action for the Data Warehouse. The condition detection and mapping the event data to event fields only works for the Data Warehouse. Using the condition detection targeted to the OpsMgr Database itself resulted in overwriting the Description field of all similar events any time a new event arrived (for a particular device with the same severity) with the latest event data message.

    I still want to use the OpsMgr console to view the latest syslogs (Our OpsMgr DB stores the last 7 days) so I created two sets of syslog collection rules. One set is as above for data warehouse storage and reporting. Another set of rules is for inserting syslog messages into the OpsMgr database. The main difference is that the condition detection for storing the syslog messages in the OpsMgr database does not replace the Description field with the incoming event message but instead hard codes it to say "See Syslog Event Data Below". If you are viewing the event data in the OpsMgr console, you simply look at the Event Data that is recorded as part of the event.

  • Here is an example of the Condition Detection I am using for Data Warehouse insertion. I made the publisher "Syslog" and Channel the priority name because it made more sense to me. I am using the publisher name in my SQL queries for reporting.


    $Target/Id$
    $MPElement$
    Syslog
    $Data/EventData/DataItem/PriorityName$
    $Data/EventData/DataItem/HostName$
    $Data/EventData/DataItem/Severity$
    0
    4

    $Data/EventData/DataItem/Message$

  • Here is the example of condition detection for storing events in the OpsMgr Database. I'm only looking at these event with an OpsMgr Event View window.


    $Target/Id$
    $MPElement$
    Syslog
    $Data/EventData/DataItem/PriorityName$
    $Data/EventData/DataItem/HostName$
    $Data/EventData/DataItem/Severity$
    0
    4

    See Event Data Below for Syslog Message

  • Here is my initial query for reporting on syslogs from the OpsMgr Data Warehouse. It searches for syslog messages over the last 24 hours. The Data Warehouse uses UTC time to store events so this query uses UTC time for the searching. In SSRS, I am converting event times to my local time zone.

    DECLARE @STARTDATE DATETIME
    DECLARE @ENDDATE DATETIME
    SET @STARTDATE = DATEADD(HOUR, -24, GETUTCDATE())
    SET @ENDDATE = GETUTCDATE()

    SELECT vEvent.DateTime
    ,vEventLevel.EventLevelTitle AS 'Level'
    ,vEvent.EventNumber AS 'Severity'
    ,vEventLoggingComputer.ComputerName AS 'Network Device'
    ,vEventDetail.RenderedDescription AS 'Message'
    FROM [OperationsManagerDW].[Event].[vEvent]
    JOIN [OperationsManagerDW].[dbo].[vEventPublisher] ON vEvent.EventPublisherRowId = vEventPublisher.EventPublisherRowId
    JOIN [OperationsManagerDW].[dbo].[vEventLevel] ON vEvent.EventLevelId = vEventLevel.EventLevelId
    JOIN [OperationsManagerDW].[dbo].[vEventLoggingComputer] ON vEvent.LoggingComputerRowId = vEventLoggingComputer.EventLoggingComputerRowId
    JOIN [OperationsManagerDW].[Event].[vEventDetail] ON vEvent.EventOriginId = vEventDetail.EventOriginId
    WHERE vEvent.DateTime >= @StartDate
    and vEvent.DateTime <= @EndDate
    and vEventPublisher.EventPublisherName = 'Syslog'
    Order by DateTime DESC

  • This query takes care of converting UTC time to your local time zone after searching based on UTC time.

    DECLARE @STARTDATE DATETIME
    DECLARE @ENDDATE DATETIME
    SET @STARTDATE = DATEADD(HOUR, -24, GETUTCDATE())
    SET @ENDDATE = GETUTCDATE()

    SELECT CONVERT(datetime,
    SWITCHOFFSET(CONVERT(datetimeoffset,
    vEvent.DateTime),
    DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS 'DateTime'
    ,vEventLevel.EventLevelTitle AS 'Level'
    ,vEvent.EventNumber AS 'Severity'
    ,vEventLoggingComputer.ComputerName AS 'Network Device'
    ,vEventDetail.RenderedDescription AS 'Message'
    FROM [OperationsManagerDW].[Event].[vEvent]
    JOIN [OperationsManagerDW].[dbo].[vEventPublisher] ON vEvent.EventPublisherRowId = vEventPublisher.EventPublisherRowId
    JOIN [OperationsManagerDW].[dbo].[vEventLevel] ON vEvent.EventLevelId = vEventLevel.EventLevelId
    JOIN [OperationsManagerDW].[dbo].[vEventLoggingComputer] ON vEvent.LoggingComputerRowId = vEventLoggingComputer.EventLoggingComputerRowId
    JOIN [OperationsManagerDW].[Event].[vEventDetail] ON vEvent.EventOriginId = vEventDetail.EventOriginId
    WHERE vEvent.DateTime >= @StartDate
    and vEvent.DateTime <= @EndDate
    and vEventPublisher.EventPublisherName = 'Syslog'
    Order by DateTime DESC

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment