Event data mining with PowerShell

On Server 2008 and 2008 R2, if your Domain Controllers aren’t configured to require LDAP signing and disallow simple LDAP binds in plaintext, Active Directory Domain Services logs a warning event on startup, and summary events every 24 hours.

A couple weeks ago, I followed the recommendation to enable logging of unsigned and plaintext LDAP authentication requests. Setting the LDAP Interface Events value to 2 generates a Directory Services event 2889 for each connection.

Now I want to do some analysis of the collected events. The event structure puts the important details, namely the client name and IP address, in the big description text field. It looks like this:

Log Name: Directory Service
Source: Microsoft-Windows-ActiveDirectory_DomainService
Date: 11/3/2010 11:46:38 AM
Event ID: 2889
Task Category: LDAP Interface
Level: Information
Keywords: Classic
Computer: CDC01.campus.ad.uvm.edu
The following client performed a SASL (Negotiate/Kerberos/NTLM/Digest) LDAP bind without requesting signing (integrity verification), or performed a simple bind over a cleartext (non-SSL/TLS-encrypted) LDAP connection.

Client IP address:
Identity the client attempted to authenticate as:

Previously, I’ve exported the logs to CSV format, then used Excel and some text-mangling functions to pull out the important details. But I noted that the two important values were nicely separated in the XML representation of the event:

Event Xml: 
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> 
    <Provider Name="Microsoft-Windows-ActiveDirectory_DomainService" Guid="{0e8478c5-3605-4e8c-8497-1e730c959516}" EventSourceName="NTDS LDAP" /> 
    <EventID Qualifiers="16384">2889</EventID>
    <TimeCreated SystemTime="2010-11-03T15:46:38.219250600Z" /> 
    <Correlation /> 
    <Execution ProcessID="512" ThreadID="3396" /> 
    <Channel>Directory Service</Channel> 
    <Security UserID="S-1-5-7" /> 

That led me to try getting at these values directly using PowerShell. What I wanted to do is pull all the Event ID 2889 entries from the log, select and format four values ( name of the DC, time of the event, client name, and client IP), and output it in a format that I could do more analysis (i.e., CSV).

First, I needed a way to get at the events. Since the Directory Services event log is not part of the Classic logs, I needed to use the Get-WinEvent commandlet.

Within the Event Viewer, I created a filter for the Directory Services event log to show just the events with ID 2889, then copied the XML version to a file:

PS Z:\> 1 gc .\filter-2889.xml 
  <Query Id="0" Path="Directory Service"> 
    <Select Path="Directory Service">*[System[Provider[@Name='Microsoft-Windows-ActiveDirectory_DomainService'] and (EventID=2889)]]</Select> 

Note that the filter identifies the event log as well as the event ID that I want to retrieve. I then stored that filter in an XML variable:

PS Z:\>$filter = gc .\filter-2889.xml

Now I could collect all the 2889 events with a short command:

PS Z:\> $events = get-winevent -filterXML $filter 
PS Z:\> $events.count 

I explored the EventLogRecord type, which is how PowerShell represents each event log entry, and found that I could access the two data elements through the Properties attribute.

What I wanted to be able to do is collect a set of the After some — ok, a lot of — experimentation, I found that I could reference the data elements’ value directly:

PS Z:\> $events[0].properties[0].value

Working from right to left, that means get the value of the first property in the array of properties of the first event in the array of events.

After more experimentation, I settled on using Select-Object with some calculated properties to massage the fields the way I wanted. A calculated property is created as a hash, with a label and an expression to be evaluated. For example, I wanted to include the time that the event was logged in ISO 8601 sortable format, so I created the following hash and stored it in a variable:

PS Z:\> $time = @{ label='Time Created'; Expression={get-date $_.TimeCreated -format s} }

I only wanted the first five characters in the computers’ names:

PS Z:\> $hostname = @{label='Hostname'; Expression={$_.MachineName.Substring(0,5)} }

I could have used a regex to pull the first element from the FQDN that the MachineName stores, but all the hosts I’m working with have the same length.

Next, I needed to get at the two important values, the client name and the IP address. I created the following hashes:

PS Z:\> $ipaddr = @{ label="IP Address"; Expression={$_.properties[0].value} } 
PS Z:\> $client = @{ label="Client"; Expression={$_.properties[1].value} }

Now, I can pipe a few of the collected events through Select-Object, using my calculated properties:

PS Z:\> $events[0..9] | select $hostname, $time, $ipaddr, $client

Hostname  Time Created         IP Address           Client 
--------  ------------         ----------           ------ 
CDC01     2010-11-03T09:41:19  CAMPUS\myhost025... 
CDC01     2010-11-03T09:41:09  CAMPUS\cas-compu... 
CDC01     2010-11-03T09:41:09  CAMPUS\cas-compu... 
CDC01     2010-11-03T09:40:13  CAMPUS\ets-compu... 
CDC01     2010-11-03T09:40:13  CAMPUS\ets-compu... 
CDC01     2010-11-03T09:39:35  CAMPUS\c42c030ec... 
CDC01     2010-11-03T09:38:47  CAMPUS\cas-compu... 
CDC01     2010-11-03T09:38:47  CAMPUS\cas-compu... 
CDC01     2010-11-03T09:34:18  CAMPUS\cas-compu... 
CDC01     2010-11-03T09:34:16  CAMPUS\000a958ea...

The values are appearing the way I want. The last step is to output them in a useful format. In PowerShell, that’s easy to do with Export-CSV. Putting the whole thing together, running directly against the event log this time:

PS Z:\> get-winevent -computer cdc01 -filterXML $filter | select $hostname,$time,$ipaddr,$client | export-csv .\cdc01.csv

Huzzah! Now, figuring this out took me longer than it would have to just dump the filtered event logs directly to CSV, and then use text functions to pull out the details. But now that I’ve documented how I did it, I think I’ll be able to reuse this technique to analyze other event logs. And maybe it will be helpful to someone else, too.

One thought on “Event data mining with PowerShell

  1. Pingback: Geoff @ UVM - Custom event log queries

Leave a Reply