How to store SYSLOG in the SCOM Data Warehouse


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 (10)

  1. Anonymous says:

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

  2. Anonymous says:

    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?

  3. Anonymous says:

    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.

  4. Anonymous says:

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

    http://www.microsoft.com/…/details.aspx

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

  5. Anonymous says:

    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$

  6. Anonymous says:

    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.

  7. Anonymous says:

    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

  8. Anonymous says:

    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

  9. Anonymous says:

    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

  10. Anonymous says:

    We provide an example below that uses the Native Syslog Data Source in SCOM and combines it with a Powershell