Tuesday 15 February 2011

Eventlog as a source

Case
I want to use the Windows Eventlog as a source in SSIS.

Solution
You can either read the saved eventlog files (*.evt / *.evtx) with for example this open source component or you can read directly from the eventlog itself. Let's elaborate the last option.

1) Script Component
Go to your Data Flow and add a Script Component. The Type should be Source.
Script Type Source




















2) Add Columns
Edit the Script Component and go to the Inputs and Outputs tab. Add four columns at the output section:
  1. EntryType (string 255)
  2. Source (string 255)
  3. Message (string 4000)
  4. TimeGenerated (database timestamp)
Add output columns



















This example uses only four columns, but there are more columns available in the eventlog.

3) The Script
Go to the first tab and hit the Edit Script... button. This script is in the default C# language. SSIS will create three methods: PreExecute, PostExecute and CreateNewOutputRows. Remove the first two, because we don't need them. Replace your method with this one and add the using row at the top.
// C# code
using System;
using System.Data;
using System.Diagnostics;       // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        // Get all events from the Application(/System/Security) log from the local server (.)
        EventLog myEvenLog = new EventLog("Application", ".");

        // Create variable to store the entry
        EventLogEntry myEntry;

        // Loop trough all entries (oldest first)
        for (int i = 0; i < myEvenLog.Entries.Count; i++)
        {
            // Get single entry
            myEntry = myEvenLog.Entries[i];

            // Add new records
            this.Output0Buffer.AddRow();

            // Fill columns
            this.Output0Buffer.EntryType = myEntry.EntryType.ToString();
            this.Output0Buffer.Source = myEntry.Source;
            this.Output0Buffer.TimeGenerated = myEntry.TimeGenerated;
            // Take a max of 4000 chars
            this.Output0Buffer.Message = myEntry.Message.Substring(0, (myEntry.Message.Length > 4000 ? 3999 : myEntry.Message.Length - 1));
        }
    }
}

Note 1: with variables you can avoid hard coded strings in your Script Component.

Note 2: You should add some error handling to your script (Try Catch) to avoid unexpected errors (authorization, too large texts, etc.).


4) The result
Add a random transformation component after your source and add a dataviewer between them to see the result.
The result















Note: It's also possible to write to the eventlog with .net code, but that is not a best practice. Use the SSIS script event logging instead!

2 comments:

  1. Thanks! This post was really useful. I was expanded with some additional columns for more detailed information.

    ReplyDelete
  2. I would not suggest using this in an environment that audits logon/logoffs on the computer you are pulling events from. I was attempting to use this to parse the security log and ended up exponentially increasing the size. Each event-get logs a separate logon/logoff event for the user in question.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...