Analyse AppLocker Logs for Exceptions

If you’re planning to rollout AppLocker you might want to run it in Audit mode first, to see where things are being run from.

You might want to store those logs centrally, see my previous post for how to get distributed Windows Event Logs into SQL Server.

So now you’ve got a table full of paths to executables, and you need to process that to give you a list of exceptions – things which would be blocked – remove any duplicates, and collate the data across all users.

Conveniently, I have written a PowerShell script to do just this:

$SQLServer = "sql-event01.rcmtech.co.uk"
$SQLDB = "EventCollection"
$SQLTable = "MicrosoftWindowsAppLocker_EXEandDLL"
# Open connection to SQL DB
$SQLDBConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=$SQLServer;Database=$SQLDB;Integrated Security=SSPI"
$SQLDBConnection.Open()
# Get data
$SQLCommand = $SQLDBConnection.CreateCommand()
$SQLSelect = "SELECT MIN(TimeCreated) TimeCreated, MIN(MachineName) MachineName, MIN(UserId) UserId, MIN(Id) Id, Message
    FROM [$SQLDB].[dbo].[$SQLTable]
    WHERE (Message NOT LIKE '%%programfiles%') AND (Message NOT LIKE '%%system32%') AND (Message NOT LIKE '%%windir%')
    GROUP BY Message"
$SQLCommand.CommandText = ($SQLSelect)
$SQLReader = $SQLCommand.ExecuteReader()
$SQLResultsTable = New-Object System.Data.DataTable
$SQLResultsTable.Load($SQLReader)
$SQLReader.Close()
$SQLDBConnection.Close()
Write-Host ("SQL query returned "+($SQLResultsTable | Measure-Object -Line).Lines+" results")
#$SQLResultsTable | Export-Csv -Path $OutFile -NoTypeInformation -Force

$Results = New-Object -TypeName System.Collections.ArrayList
foreach($Line in $SQLResultsTable){
    $Location = $Line.Message -replace "USERS\\.*?\\","USERS\xxx\"
    $Location = $Location -replace "USERS\d\$\\.*?\\","USERSx$\xxx\"
    $Location = $Location -replace "was allowed.*",""
    $Results.Add($Location) | Out-Null
}
$Results = $Results | Select-Object -Unique
$Results = $Results | Sort-Object
Write-Host ("After generalisation there are now "+$Results.Count+" results")
$Results | Out-GridView -OutputMode Multiple -Title "Unique Results" | ConvertTo-Csv -NoTypeInformation | clip
Write-Host "Any selected entries have been placed on the clipboard" -ForegroundColor Gray

So what am I doing?

  • Open a connection to the SQL Server, you’ll need to change the variables at the top as appropriate
  • Run a SELECT query that removes duplicate Message entries whilst retaining the first instance of the other columns that go with the remaining Message. Those columns are actually not displayed in the final script output, but it was a fun learning process to work out how to do this, so I left it in!
  • The SELECT query also excludes any paths that are part of the default AppLocker Executable rules:
    • All files located in the Program Files folder
    • All files located in the Windows folder
  • The results of this are then processed by stripping out usernames from paths such as %OSDRIVE%\USERS\a-person\APPDATA
  • I then strip out usernames from users’ personal network drives, which in my case are paths such as \\fileserver\users1$\a-person\personal. This is done with a simple regular expression, as are the next few bits of string replacement.
  • I then strip out all the text from the end, starting “was allowed”. This is needed because the third default AppLocker rule is to allow members of the local administrators group to run anything from anywhere, and some of my users are currently local administrators on their PCs. Thus you can get the same path being reported as both “was allowed to run.” and “was allowed to run but would have been prevented from running if the AppLocker policy were enforced.”.
  • Each processed message line is then placed into a results array.
  • The array is then sorted to leave only unique paths
  • Finally, these are displayed in a GridView.
  • Any selected lines are placed onto the clipboard if you click OK.

This seems to work pretty well. My SQL table has around 75,000 rows in it, and I end up with 191 unique “problem” paths being displayed by this script.

This entry was posted in PowerShell, Security, Windows and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s