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:
- EntryType (string 255)
- Source (string 255)
- Message (string 4000)
- 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!
Thanks! This post was really useful. I was expanded with some additional columns for more detailed information.
ReplyDeleteI 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