Basic Sitecore Audit Trail with Powershell

---

Update: This report has been included in the latest version SPE v5.0: 
https://github.com/SitecorePowerShell/Console/issues/1033

---

Here's a question posted to our internal Sitecore Slack channel yesterday:

The answer was 'no'.

This isn't the first time our clients have requested 'Audit Trail' functionality before, either. And we're talking about the basics here:

• • When did a user log in?
• • When did a user log out?
• • Who published what, and when?
• • When was this item's workflow executed and by whom?

Unfortunately, there isn't an 'out of the box' solution to easily obtain this data.
The most common "solution" has always been to utilize the Sitecore Log Analyzer's Audit tab - which is great for developers, but not for CMS users.

This tool has saved my sanity so many times. 

Others in the community have shared promising solutions in the past, most of which have become unsupported over time. The Sitecore Audit Trail Marketplace module, for example, was last updated in 2015 and only supports up to version 7.5 (fun fact: mainstream support for this version ended in December 2017!).  Additionally, the setup was heavy - requiring a custom database connection. It also, unfortunately,  had it some known issues associated with it.
While the feature set was fairly extensive, a simpler solution still wasn't available.

The Advanced System Reporter also has an audit feature, but again the last time this module was updated was in 2015 and supports up to version 8.0.

And then it hit me.

POWERSHELL!!!!

You've got to know this by now...my default reaction to any problem is to ask myself: "can this be done using Powershell?".

The idea was to create a Powershell report that consumes the accessible Sitecore log files which already contain the data we need.  The report should include all lines marked as 'AUDIT'' and split into columns.  The user should be able to provide a date range to narrow down the audit.  

Powershell would automatically provide the rest: keyword filtering, sorting, exporting, etc. 

Let's script it.

In order to allow a user to configure a date range, we need to create an interactive dialog: 

The result:

If the user clicks cancel, we'll want to abort the whole operation.
Otherwise, we'll proceed and create the properties needed for our final ListView.
We'll call a function named Get-Audit where our logic will process.

Within the Get-Audit function, we first obtain the location of the Sitecore log folder (UPDATE: We'll use $SitecoreLogFolder instead. SPE creates a variable $SitecoreLogFolder and resolves the path for you: https://doc.sitecorepowershell.com/working-with-items/variables).  We'll the pull the log files from the resolved log path and filter out everything but the standard log. files:

We'll make sure the user has selected a date range and filter the list of files using those inputs. If a user didn't set a date range, we'll simply use the original file set - which will display the most recent entries. While we're at it, we'll define a 'regex' string used to filter only AUDIT items, and an array object to hold our line objects:

We'll start a loop based on our file count. From there, we can get the contents of the file.

We need a way to include the date for each line object (specifically for our final sorting), so we'll build out a simple date string which we'll use later.

We'll now loop through each line in the file and check if it's marked as 'AUDIT'. If the condition matches, we'll append the simple date string we created previously to the beginning of the line string and sanitize the string by removing double-spaces. In some cases, the audit line will contain ManagedPoolThread #XX instead of an ID. We'll sanitize this as well.

The line now looks like this:
8/17/2018 8324 00:13:47 INFO AUDIT (sitecore\Anonymous): Logout

We'll use the space between each data point to our advantage and split each property into individual objects. The username requires some general sanitation, but more importantly, we'll build out one more DateTime object which we'll use to sort the lines before returning our array. 

Finally, we sort and return our array for the table to process.

Final Script

Putting it all together, our script looks like this:

When the script runs, we get a pretty clear view of who's been doing what:

As always, feel free to use, modify, and build on it as you see fit. This has only been tested on a handful of environments so far, so bugs are still possible.
If you do spot any issues, feel free to report them in the comments - or make the necessary changes and submit a pull request to https://github.com/strezag/sitecore-audit-trail-powershell.

Happy SitecorePowershelling!