Jaa

How to store SYSLOG in the SCOM Data Warehouse

  • Artikkeli

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

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

  • Anonymous
    January 01, 2003
    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?

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    January 01, 2003
    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$

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    January 01, 2003
    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

  • Anonymous
    February 24, 2015
    We provide an example below that uses the Native Syslog Data Source in SCOM and combines it with a Powershell