Peter Hinchley

Learning in Public

✪ Using PowerShell to Collect and Search Authentication Audit Data

This post outlines a solution I developed for identifying where specific user accounts in an Active Directory environment are being used. It does this by parsing the security event logs of domain controllers for authentication events, and logging the account name and the host name (or IP address) of the client from which the authentication event was initiated. The results are stored in a SQLite database, and a web front end (developed and hosted entirely in PowerShell) is provided for searching the results.

For the sake of this article, let's assume that all code will be stored under a project folder named C:\Audit. Under this folder we will create four sub-folders:

After creating the project structure, download the latest precompiled SQLite 64-bit binaries, and after extracting the files, copy SQLite.Interop.dll and System.Data.SQLite.dll to C:\Audit\SQLite.

Next, create a file named C:\Audit\Helpers\CreateDB.ps1 with the following code. This script will be used to create the SQLite database for storing the collected audit data. As you can see, the script will create a single table named accounts with three fields, username, hostname, and timestamp.

Add-Type -Path "..\Sqlite\System.Data.SQLite.dll"

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=..\Database\Accounts.db"

$sql = $con.CreateCommand()

$query = @"
CREATE TABLE accounts (
 username text NOT NULL,
 hostname text NOT NULL,
 timestamp text NOT NULL

$sql.CommandText = $query


Now create a script named C:\Audit\index.ps1 with the following content. This script will be used to collect the audit data. We will end up creating a scheduled task that will execute the script every 15 minutes.

You will need to modify the $servers variable to include the names of the domain controllers in your environment that are to be queried.

Add-Type -Path ".\Sqlite\System.Data.SQLite.dll"

$servers = 'dc1', 'dc2'

$sb = {
  $events = @{}

  # 15 minute interval.
  $query = @'
  <Query Id="0" Path="Security">
    <Select Path="Security">*[System[(EventID=4624 or EventID=4768) and TimeCreated[timediff(@SystemTime) &lt;= 900000]]]</Select>

    get-winevent -filterxml $query -ea silent | %{
      $xml = [xml]$_.toxml()

      $created = $xml.Event.System.TimeCreated.SystemTime

      $targetUserName = ($xml.Event.EventData.Data | ? name -eq 'TargetUserName').'#text'
      $ipAddress = ($xml.Event.EventData.Data | ? name -eq 'IpAddress').'#text'
      $ipAddress = $ipAddress.replace('::ffff:', '')

      if ($targetUserName -match '^.*(?<!\$)$' -and $ipAddress -ne '-') {
        $pair = '{0}:{1}' -f $targetUserName, $ipAddress
        if ($events.containskey($pair)) { $events[$pair] = $created }
        else { $events.add($pair, $created) }

  return $events

$events = invoke-command -ComputerName $servers -scriptblock $sb

$params = @()

$query = @"
-- try to update any existing row.
UPDATE accounts
SET username=@username, hostname=@hostname, timestamp=@timestamp
WHERE username=@username AND hostname=@hostname;

-- if no update happened (i.e. the row didn't exist) then insert one.
INSERT INTO accounts (username, hostname, timestamp)
SELECT @username, @hostname, @timestamp
WHERE (Select Changes() = 0);

# reverse resolve ip address before opening database.
$events | %{
  $_.getenumerator() | %{
    $event = $
    $timestamp = $_.value

    $username, $ip = $event -split ':'

    try  { $hostname = [System.Net.Dns]::GetHostByAddress($ip).hostname }
    catch { $hostname = $ip }

    $params += (,($username, $hostname, $timestamp))

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=.\Database\Accounts.db"

$sql = $con.CreateCommand()

$params | %{
  $sql.CommandText = $query
  $sql.parameters.addwithvalue('@username', $_[0]) | out-null
  $sql.parameters.addwithvalue('@hostname', $_[1]) | out-null
  $sql.parameters.addwithvalue('@timestamp', $_[2]) | out-null
  $sql.ExecuteNonQuery() | out-null


The code shown above executes a script block (defined by the $sb variable) on each of the domain controllers to retrieve all audit events from the security event log with event ids 4624 and 4768 that were recorded in the last 900,000 milliseconds (or 15 minutes). The results are then parsed, and the values of TargetUserName and IPAddress properties are extracted.

Any event without a valid client IP address is excluded (i.e. where the IPAddress field is set to -). Any event generated by a computer, as opposed to a user account, is also excluded. This is achieved by skipping entries where the TargetUserName field ends in $. Note: Unfortunately it isn't possible to directly exclude events in this manner with the Get-WinEvent cmdlet, as the XPath query used by the cmdlet does not support syntax of the form "ends with". Finally, the script block collects the results in a hash-table, which is returned to the calling script.

As a side note, a decision was made to use Invoke-Command to locally execute the Get-WinEvent cmdlet on each domain controller, as this proved significantly faster than calling Get-WinEvent with the ComputerName property to remotely collect the data. It also allowed for the results to be filtered and parsed before being sent across the network, which also improved performance.

The results returned from Invoke-Command are iterated over, and an attempt is made to resolve the client IP address associated with each event into a valid hostname using the GetHostByAddress method.

Finally, the results are added to the SQLite database using a parameterised insert query.

At this point, you could run the CreateDB.ps1 script to create an empty database, and then create a scheduled task to execute the index.ps1 script at 15 minute intervals under the context of an account with the privileges necessary for reading the security event logs of the domain controllers. And with any luck the data should start rolling in.

To confirm that everything is working as expected, create another script named C:\Audit\Helpers\ReadDB.ps1 with the following content. When executed, this script will dump the contents of the SQLite database.

Add-Type -Path "..\Sqlite\System.Data.SQLite.dll"

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=..\Database\Accounts.db"

$sql = $con.CreateCommand()

$sql.CommandText = "SELECT * FROM accounts"

$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
$data = New-Object System.Data.DataSet