Parsing the Admin Audit Logs with PowerShell

One of the nice features introduced in Exchange 2010 was Admin Audit Logging. Concerned administrators everywhere rejoiced! This meant that a record of Exchange PowerShell activity, organization wide, was now saved and searchable.

Administrators could query the Admin Audit Log, using the Search-AdminAuditLog Cmdlet, and reveal any CmdLets invoked, the date and time they were executed and the identity of the person who issued the commands. However, the results of the search are a bit cryptic and it didn’t allow for easy bulk manipulation like parsing, reporting or archiving.

The main complaint I heard from customers went something like this: “It’s great that I can see what Cmdlets are run, and what switches were used… but I can’t see the values of those switches!” Well, as it turns out, that data has actually been there the whole time; it’s just been stored in a non-obvious manner.

Consider a scenario where you’ve been informed that many, or all, of the mail users in your organization are reporting the wrong phone number listed in the Global Address List. It seems everyone has the same phone number now, let’s say 867-5309.

Because your organization uses Office 365 Directory Synchronization (DirSync), you know the change had to occur within your on-premises organization and was then subsequently synchronized to Office 365. The Search-AdminAuditLog Cmdlet must, therefore, be run on-premises.

It’s important to remember this concept. If you were investigating a Send Connector configuration change for your Office 365 – Exchange Online tenant, a search would need to be performed against your tenant instead. But let’s get back to our Jenny Phone number issue.

You know that the change was made on the 6^th so you restrict the search to that date.

Search-AdminAuditLog -StartDate "4/6/2015 12:00:00 AM" -EndDate 4/6/2015 11:20:00 AM"

(click on screenshots that might be too small to read)

Reviewing the output, you find that Tommy executed the Set-User Cmdlet but no indication as to what parameter(s) or values were used? What exactly did Tommy run? Where are the details!?

Then, you spot a clue. The ‘CmdletParameters’ and ‘ModifiedProperties’ are enclosed with braces { }. Braces are normally indicative of a hash table. You know a hash table is simply a collection of name-value pairs. You wonder if you’re only seeing the keys or a truncated view in this output. Could more details remain hidden?

Digging a bit deeper, you decide to store the search results to an array, named $AuditLog, which will allow for easier parsing.

$AuditLog = Search-AdminAuditLog -StartDate "4/6/2015 12:00:00 AM" -EndDate "4/6/2015 11:20:00 AM"

Next, you isolate a single entry in the array. This is done by calling the array variable and adding [0] to it. This returns only the first entry in the array.

$AuditLog[0]

To determine the object type of the ‘CmdletParameter’, you use the GetType() method and sure enough, it’s an array list.

$AuditLog[0].CmdletParameters.GetType()

Finally , you return the CmdletParameters array list to reveal all the details needed to conclude your investigation.

$AuditLog[0].CmdletParameters

Considering there are hundreds or thousands of entries in the audit log, how would you generate a full list of all the objects Tommy has changed? Or better yet, report all objects that he changed where ONLY the ‘Phone’ attribute was modified?

Fortunately, you don’t have to expend too much time on this. My colleague, Matthew Byrd recognized this exact problem and he wrote a PowerShell Script that does all the aforementioned steps for you and then some!

The script can be downloaded as an attachment to this blog post and you’ll find it’s well documented and commented throughout. The script includes help (get-help .\Get-SimpleAuditLogReport.ps1) and can be used within Exchange 2010, Exchange 2013 and Office 365 - Exchange Online environments. That said, I’m not going to dissect the script. Instead, I will demonstrate how to use it.

The script simply manipulates or formats the results of the Search-AdminAuditLog query into a much cleaner and detailed output. You form your Search-AdminAuditLog query, then pipe it through the Get-SimpleAuditlogReport script for formatting and parsing.

Here are some usage examples:

This first example will output the results to the PowerShell Screen.

$Search = Search-AdminAuditLog -StartDate "4/6/2015 12:00:00 AM" -EndDate "4/6/2015 11:20:00 AM"
$Search | C:\Scripts\Get-SimpleAuditLogReport.ps1 –agree

You can see that the Get-SimpleAuditLogReport.ps1 script has taken results stored in the $Search variable and attempted to rebuild the original Command run. It isn’t perfect but the goal of the script is to give you a command that you could copy and paste into an Exchange Shell Window and it should run.

Should you expect a lot of data to be returned or wish to save the results for later use, this example will save the results to a CSV file.

Search-AdminAuditLog -StartDate "4/6/2015 12:00:00 AM" -EndDate "4/6/2015 11:20:00 AM"| C:\Scripts\Get-SimpleAuditlogReport.ps1 -agree | Export-CSV -path C:\temp\auditlog.csv

This example uses one of my favorite output objects, Out-GridView, to display the results. This is a nice hybrid CSV/PowerShell output option. The results shown in the Out-GridView window is sortable and filterable. You can select, copy/paste the filtered results into a CSV file. Meanwhile the raw, unfiltered, results are saved to a CSV file for future later use or archival.

Search-AdminAuditLog -StartDate "4/6/2015 12:00:00 AM" -EndDate "4/6/2015 11:20:00 AM"| C:\Scripts\Get-SimpleAuditlogReport.ps1 -agree | Out-GridView –PassThru | Export-Csv -Path c:\temp\auditlog.csv

Here I restrict it to only commands Tommy ran and remove anything that he ran against the discovery mailbox since it is a system mailbox.

Copy/Paste the filtered results into a CSV file. The Out-GridView has no built in export or save feature. To save your filtered results, click on an entry and then ctrl-a / ctrl-c to select all and copy results to your clipboard. Finally, in Excel, paste and you’re done.

There you have it. Admin Audit Log Mastery – CHECK! Thanks to Matthew Byrd’s wonderful script you can get the most out of your audit logs,

Brandon Everhardt