Send Windows Event Logs to SQL Database

I’m currently in the process of planning for an AppLocker rollout to all my PCs (about 7,500 of them) due to an increasing amount of malware. You should probably be doing this too. Anyway, a sensible first step is to identify which paths things are running from, which is pretty easy – you just turn AppLocker on in Audit mode. This makes it write messages into its event log telling what has been allowed to run, and what would be blocked from running were it in enforce mode rather than audit mode.

You now have your PCs collecting all this useful info in their event logs. Now you need to collate it centrally and process it. “Aha”, I thought, “this will be a great time to try out using the built in Windows Event Forwarding“. I followed some instructions, and it worked fine on my Windows 8.1 PC, and also on a colleague’s Windows 10 PC. Sadly, it failed on the other 7498 Windows 7 PCs. After a few days of trying to get it to work on them, I gave up and wrote my own version in PowerShell.

I think my version is better, because it allows you to query events in SQL, which is easier for me than trying to extract sensible information directly out of an event log – especially if the info you’re after is in the Messages field. You could of course modify the script and the SQL table to collect whatever fields you want. This is also not restricted to the AppLocker event log, you can collect events from any Windows Event Log.

This is going to be a big post with multiple sections, sorry about that, but it is pretty straightforward.

Overview

• You need an SQL server to store the events that you’re going to collect from the PCs. Maybe use SQL Server 2014 Express – which is free.
• The PCs push the events to the SQL server using an SQL bulk copy, which is pretty efficient.
• The event collection script is written in PowerShell, you should probably have at least version 3 of this on your PCs, and .Net 3.5, ideally PowerShell 4 and .Net 4.5 (at time of writing).
• The collection script is launched via a scheduled task, runs as the Network Service account, does not show on the user’s desktop whilst running, and only requires the Domain Computers group to have access to the SQL database. I’ve configured the scheduled task via a Group Policy Preference.
• The script writes a registry marker when it runs, and on subsequent runs only uploads events that have occurred since its last run. This means you can run it as often as you like an not get duplicate events in your SQL table.
• The script takes two parameters, the event log name to collect from, and the SQL server to send the events to.

PowerShell Script

param(
    [parameter(Mandatory=$true)][string]$LogName,
    [parameter(Mandatory=$true)][string]$SQLServer
)

# Check event log for events written since this script was last run
# or all events if this is the first run of the script
# and then upload them to SQL Server efficiently

# Create a simplified version of the log name for use elsewhere in the script
$LogNameSimplified = $LogName.Replace("/","_")
$LogNameSimplified = $LogNameSimplified.Replace(" ","")
$LogNameSimplified = $LogNameSimplified.Replace("-","")
Write-Host "SQL table name: $LogNameSimplified"

# Registry key to store last run date & time
$RegKey = "HKCU:\Software\RCMTech\EventCollector"
# SQL Database that holds the table for the events
$SQLDatabase = "EventCollection2"

function Get-UserFromSID ($SID){
    # Does what it says on the tin
    $SIDObject = New-Object -TypeName System.Security.Principal.SecurityIdentifier($SID)
    $User = $SIDObject.Translate([System.Security.Principal.NTAccount])
    $User.Value
}

# Initialise LastRun variable, make it old enough that all events will be collected on first run
# Always use ISO 8601 format
[datetime]$LastRunExeDll = "1977-01-01T00:00:00"

if(Test-Path $RegKey){
    # Registry key exists, check LastRun value
    $LastRunValue = (Get-ItemProperty -Path $RegKey -Name $LogNameSimplified -ErrorAction SilentlyContinue).$LogNameSimplified
    if($LastRunValue -ne $null){
        $LastRunExeDll = $LastRunValue
    }
}else{
    # Registry key does not exist, create it, then set the NewsID value and run full script
    Write-Host "Registry key not present"
    New-Item -Path $RegKey -Force | Out-Null
}

# Get the events logged since LastRun date & time
Write-Host ("Collecting events from "+(Get-Date -Date $LastRunExeDll -Format s))
$Events = Get-WinEvent -FilterHashtable @{logname=$LogName; starttime=$LastRunExeDll} -ErrorAction SilentlyContinue
Write-Host ("Found "+$Events.Count+" events")

if($Events.Count -gt 0){
    # Process event data into a DataTable ready for upload to SQL Server
    # Create DataTable
    $DataTable = New-Object System.Data.DataTable
    $DataTable.TableName = $LogNameSimplified
    # Define Columns
    $Column1 = New-Object system.Data.DataColumn TimeCreated,([datetime])
    $Column2 = New-Object system.Data.DataColumn MachineName,([string])
    $Column3 = New-Object system.Data.DataColumn UserId,([string])
    $Column4 = New-Object system.Data.DataColumn Id,([int])
    $Column5 = New-Object system.Data.DataColumn Message,([string])
    # Add the Columns
    $DataTable.Columns.Add($Column1)
    $DataTable.Columns.Add($Column2)
    $DataTable.Columns.Add($Column3)
    $DataTable.Columns.Add($Column4)
    $DataTable.Columns.Add($Column5)
    # Add event data to DataTable
    foreach($Event in $Events){
        $Row = $DataTable.NewRow()
        $Row.TimeCreated = $Event.TimeCreated 
        $Row.MachineName = $Event.MachineName
        $Row.UserId = Get-UserFromSID -SID $Event.UserId
        $Row.Id = $Event.Id
        $Row.Message = $Event.Message
        $DataTable.Rows.Add($Row)
    }

    # Bulk copy the data into SQL Server
    try{
        $SQLConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Data Source=$SQLServer;Integrated Security=SSPI;Database=$SQLDatabase"
        $SQLConnection.Open()
        $SQLBulkCopy = New-Object -TypeName System.Data.SqlClient.SqlBulkCopy -ArgumentList $SQLConnection
        $SQLBulkCopy.DestinationTableName = "dbo.$LogNameSimplified"
        $SQLBulkCopy.BulkCopyTimeout = 60
        $SQLBulkCopy.WriteToServer($Datatable)
        # Create/update the LastRun value - assuming all the above has worked - in ISO 8601 format
        New-ItemProperty -Path $RegKey -Name $LogNameSimplified -Value (Get-Date -Format s) -Force | Out-Null
        Write-Host "Data uploaded to SQL Server"
    }
    catch{
        Write-Host "Problem uploading data to SQL Server"
        Write-Error $error[0]
    }
}

A few points to note on the script:

• Pass it the name of the event log to collect events from, e.g. Microsoft-Windows-AppLocker/EXE and DLL
• If the upload to SQL Server fails, the timestamp marker is not written to the registry and thus the events in the event log will try to be uploaded again on the next run of the script. i.e. you will not be missing events in the SQL table if the SQL server is unavailable when the script runs.
• The log name is simplified to a form that SQL Server is happy with by removing spaces and hyphens, and converting forward slashes to underscores.
• The script assumes the database name of EventCollection
• The script requires you to create a table within this database for each log that you want to collect from, the table name needs to be the simplified version of the log name passed to the script, e.g.
  Microsoft-Windows-AppLocker/EXE and DLL becomes
  MicrosoftWindowsAppLocker_EXEandDLL
• See below for SQL script to create the database and table
• ALWAYS use ISO 8601 format datetime with PowerShell! (especially if you live outside USA)
• I have created a GP Pref to copy the script onto the C drive of all my PCs
• Storing the event data in a DataTable is bit more fiddly than using a simple array of objects, but it makes the bulk copy into SQL Server much easier – you just dump the whole thing across.
• The UserId returned from the event log in in the form of a SID, which is not terribly useful to me, so I wrote the Get-UserFromSID function to change this into a username.

SQL Database and Table Creation

Here’s the SQL Server code, paste into SQL Management Studio and run it.

USE [master]
GO

CREATE DATABASE [EventCollection]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'EventCollection', FILENAME = N'D:\EventCollection.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'EventCollection_log', FILENAME = N'L:\EventCollection_log.ldf' , FILEGROWTH = 10%)
GO

ALTER DATABASE [EventCollection] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [EventCollection].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

USE [EventCollection]
GO

CREATE TABLE [dbo].[MicrosoftWindowsAppLocker_EXEandDLL](
	[TimeCreated] [datetime] NULL,
	[MachineName] [varchar](50) NULL,
	[UserId] [varchar](50) NULL,
	[Id] [int] NULL,
	[Message] [varchar](500) NULL
) ON [PRIMARY]

GO

You also need to grant the Domain Computers group permission to bulk copy into the table.

From SQL Management Studio:

1. Go to Security – Logins
2. Create a new Login for the Domain Computers group, on the User Mapping page tick the EventCollection database.
3. Go to Databases – EventCollection – Security – Users
4. Double-click the Domain Computers group, go to the Securables page
5. Click Search… – All objects of the types… – OK
6. Tick Tables – OK
7. Ensure the table is selected in the Securables section, then in the Explicit permission tab tick:
   1. Insert: Grant
   2. Select: Grant
8. Click OK.

Note that I am not a SQL Server expert, so whilst this all works it may be missing many optimisations. I believe SQL bulk copies do not cause much transaction log activity, but you could always set your database to simple recovery mode anyway.

Scheduled Task Configuration

I’ve configured this via a Group Policy Preference. Here’s what I did.

1. Create a new GPO, or edit an existing one. In the Group Policy Management Editor go to Computer Configuration, Preferences, Control Panel Settings, Scheduled Tasks.
2. Right-click, New – Scheduled Task (At least Windows 7). Leave the settings at their defaults except as detailed below.
3. General tab
   1. Action: Replace
   2. Name: Collect AppLocker Events EXE DLL
   3. When running the task, use the following user account: NT AUTHORITY\Network Service
4. Triggers tab – you can use any trigger you like, personally I’m doing it once a day based on time and day of the week
   1. Click New…
   2. Begin the task: On a schedule
   3. Settings: Weekly
   4. Start: <today’s date> 15:00:00
   5. Recur every: 1 weeks on: Monday Tuesday Wednesday Thursday Friday
   6. Delay task for up to (random delay): 1 hour (stops your SQL server being overwhelmed with all the collections happening at once)
   7. Stop task if it runs longer than: 30 minutes (this is just a safety net in case the script errors badly/hangs)
   8. Enabled needs to be ticked
5. Actions tab
   1. Click New…
   2. Action: Start a program
   3. Program/script: %WindowsDir%\System32\WindowsPowerShell\v1.0\powershell.exe (note that GPPrefs use their own “environment variables, hence %WindowsDir% and not %WinDir%. Hit F3 to view & insert GPPref variables)
   4. Add arguments(optional): -ExecutionPolicy Bypass -File “C:\Program Files\RCMTech\CollectEvents.ps1” (see my note earlier about using a GPPref to copy the script locally onto the PCs)
6. Settings tab
   1. Allow task to be run on demand: ticked (useful for testing, and why not anyway)
   2. Run task as soon as possible after a scheduled start is missed: ticked (in case the PC is switched off when the task is scheduled to run)
7. Common tab
   1. Remove this item when it is no longer applied: ticked

Note that running a scheduled task as a specific user is no longer possible via GPPref due a security flaw. Network Service is a good choice in this situation anyway. It causes the connection to the SQL server to be using the credentials of the computer’s own Active Directory account, e.g. RCMTech\MYPC$ which means you don’t need to give your users access to the database. This is good from a data protection point of view as the resulting database contains personally identifiable information.

Group Policy Preference to copy script onto target machines

1. Computer Configuration, Preferences, Windows Settings, Files.
2. Right-click, New – File
3. General tab
   1. Source File(s): \\rcmtech.co.uk\NETLOGON\LocalScripts\*.*
   2. Destination Folder: %ProgramFilesDir%\RCMTech
   3. Suppress errors on individual file actions: ticked
4. Common tab
   1. Remove this item when it is not longer needed: ticked
   2. Item-level targeting:
      1. the folder \\rcmtech.co.uk\NETLOGON\LocalScripts exists

All done

Once all the above is in place, you’re good to go. Now you just need to do something with all that event data sat in your SQL database.