Powershell – Parse wevtutil XML into a database

powershellwindows-event-logwindows-server-2008xml

I'm by no means an XML expert. Ergo, I'm having trouble importing Windows event XML from wevtutil into an SQL database. Prior to Server 2008, we exported event log data to the database directly using Log Parser 2.2, but logparser hasn't been upgraded to handle Server 2008 event logs gracefully.

Here's what I know how to handle:

<Field1>text</Field1>
<Field2>text</Field2>
<Field3>text</Field3>
<Field4>text</Field4>

Here's the somewhat different format that wevtutil creates when exporting event logs to XML:

<Field1>text</Field1>
<Field2>text</Field2>
<EventData>
    <Data Name='Field3'>text</Data>
    <Data Name='Field4'>text</Data>
</EventData>

Is there a relatively straightforward way to import this stuff into a database, mapping <Data Name='Field3'> to [Field3] and so on? I'm OK with writing a VB script (or whatever) to parse through the data one row at a time and insert it into the database one row at a time, if I really need to reinvent the wheel. However, I would prefer to stand on the shoulders of giants. Any suggestions, other than to check my question for mixed metaphors?

(note: this would be a recurring process, so it can't require GUI-only tools.)

Best Answer

I'm doing exactly this in a script by way of PowerShell. The whole upload-to-database script is about 18K so I'm not going to repost the entire thing here (though I have the generic ideas here). Handling the XML is pretty simple.

The command to get the event data is what you already know.

wevtutil qe Security /r:$DC /q:"*[System[((EventID=$LogonID or EventID=$FLogonID or EventID=$LogoffID or EventID=$LockoutID) and TimeCreated[@SystemTime > '$LUFilterString'] and TimeCreated[@SystemTime < '$NowFilterString'] )]] " > $DC-events.xml

The variables in that should be clear. I'm tracking login, logout, and lockout events. Generating the "NowFilterString" in the funny format wevtutil requires:

$Now=get-date
$Msec=$now.Millisecond
$NowFilterString=$Now.AddSeconds(-1).AddMilliseconds(-$Msec).ToUniversalTime().ToString("O")

I'm truncating the milliseconds down to zero to better handle edge cases.

So now you have an XML file. Now what? To parse that XML file:

get-content ".\$DC-events.xml" | foreach {
    $Event=[xml]$_
    $DateTime=[datetime]$Event.event.System.TimeCreated.GetAttribute("SystemTime")
    codecodecodecode
}

Accessing individual elements is done by:

foreach ($Data in $Event.event.EventData.get_childNodes()) {
            if ($Data.Name -eq "TargetUserName") { $User=$Data."#text"}
            elseif ($Data.Name -eq "IpAddress") {$IP=$Data."#text"}
        }

Or another example

foreach ($Data in $Event.event.EventData.get_childNodes()) {
    if ($Data.Name -eq "TargetUserName") {$User=$Data."#text"}
       elseif ($Data.Name -eq "WorkstationName") {$MachineName=$Data."#text"}
       elseif ($Data.Name -eq "IpAddress") {$IP=$Data."#text"}
       # Ensure only failed logins to the right domain are processed:
       elseif ($Data.Name -eq "TargetDomainName") {$Domain=$Data."#text"}
    }

I hope this helps you figure out XML parsing. Since this is PowerShell, most of these are readily convertible to standard .NET calls.

Related Topic