/ Powershell

Office 365 audit logs and Powershell (Search-UnifiedAuditLog)

The other day we had yet another reason to search the Audit logs for Office 365. The Audit logs in the Compliance Center under the Office 365 Admin portal are usually enough for most tasks, such as following a User's logins (normal auditing). But when it comes to more advanced log-searching (searching by IP-addresses), or when you want to go further back, the online-tool is just not enough. But of course Powershell is :)

Getting started with Search-UnifiedAuditLog

First of all we need to connect our Powershell session to Office 365 (or more specifically Exchange Online). See my previous post (Some neat Powershell-snippets for connecting to Office 365 and Exchange Online) on how to achieve that.

So after running: Connect-Online the online-cmdlet Search-UnifiedAuditLog becomes available.

Now the first thing to understand is that Search-UnifiedAuditLog as default only returns 100 results. To get more we need to use the -ResultSize 5000 parameter (5000 is max). If we want to get even more logs, and often we do, we have to use the built in paging (more on that in a later post).

Search for users

So now the actual search for Audit-logs. To get info for a user run:

$AuditSearch = Search-UnifiedAuditLog -UserIds "johndoe01" -StartDate 2017-01-06 -EndDate 2017-02-06 -ResultSize 5000 

This will give you up to 5000 items in $AuditSearch and each one looks like this:

RunspaceId   : fbc80846-d40f-4536-bcde-82ae84700897
RecordType   : SharePointFileOperation
CreationDate : 2017-01-25 18:21:49
UserIds      : johndoe01@upn
Operations   : FileAccessed
AuditData    : {"CreationTime":"2017-01-25T18:21:49","Id":"3bed3027-5622-423c-100f-xxxx","Operation":"FileAccessed","OrganizationId":"913f18ec-7f26-4c5f-a816-xxxx","RecordType":6,"UserKey":"xxx@live.com","UserType":0,"Version":1,"Workload":"SharePoint","ClientIP":"","ObjectId":"https:\/\/xxx-my.sharepoint.com\/user photos\/profilephotos\/xxx_sthumb.jpg?t=63607785358","UserId":"johndoe01@upn","EventSource":"SharePoint","ItemType":"File","Site":"7b4f87ab-5fc5-123-8218-xxxx","UserAgent":"Mozilla\/5.0 (Windows NT 10.0; WOW64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/55.0.2883.87 Safari\/537.36","SourceFileExtension":"jpg","SiteUrl":"https:\/\/xxx-my.sharepoint.com?t=63607785358","SourceFileName":"xxx_xx_xx_sthumb.jpg","SourceRelativeUrl":"user photos\/profilephotos"}
ResultIndex  : 1
ResultCount  : 462
Identity     : 3bed3027-5622-423c-100f-xxxx
IsValid      : True
ObjectState  : Unchanged

Basically the object contains some generic attributes such as Operations (what the user did, eg 'FileAccessed'), UserIds (the UPN of the user(s)) and AuditData. The AuditData is JSON and contains even more info about the operation.

We could narrow the search down further by selecting a specific operation to look for. Let's find out what Operations was in the previous search:

$SearchForUser.Operations | select -Unique

Update user.
Change user license.
Create application password for user.

Now let's say we only want to check for UserLoggedIn:

$AuditSearchUserLoggedIn = Search-UnifiedAuditLog -UserIds "johndoe01" -StartDate 2017-01-06 -EndDate 2017-02-06 -ResultSize 5000 -Operations "UserLoggedIn"

This will return less results but with the same attributes as previously.

Getting the AuditData (IP-addresses, operations etc) and export them to a .csv

To get more info from the audit log entries, such as all IP-addresses used by this user, we would have to convert the AuditData from JSON and extract the information we want and export those values to a .csv. There are a couple of ways do this, I will show my favorite one below. It's my favorite because it's easy to read and easy to re-use:

$AuditSearchUserLoggedIn = Search-UnifiedAuditLog -UserIds "johndoe01" -StartDate 2017-01-06 -EndDate 2017-02-06 -ResultSize 5000 -Operations "UserLoggedIn"

$Results = @()

foreach ($Entry in $AuditSearchUserLoggedIn)
    #Initialize a return object
    $return = "" | select Date,IPAddress,Client,Operation
    #Convert the JSON to a PSObject        
    $data = $Entry.AuditData | ConvertFrom-Json
    #Populate the return object
    $return.Date = $data.CreationTime
    $return.IPAddress = $data.ClientIP
    $return.Client = $data.Client
    $return.Operation = $data.Operation
    #Returns the data to outside of the loop
    $Results += $return

#Export to csv
$Results | Export-Csv -Delimiter "," -Encoding UTF8 -NoTypeInformation -Path C:\Exports\loggedInsForjohndoe01.csv

Here is all the information easily available (and exportable) from the AuditData:

CreationTime                  : 2017-02-05T07:54:35
Id                            : e2669489-fd1e-4eb3-8080-xxxx
Operation                     : ForeignRealmIndexLogonInitialAuthUsingADFSFederatedToken
OrganizationId                : 913f18ec-7f26-4c5f-a816-xxxx
RecordType                    : 9
ResultStatus                  : success
UserKey                       : 115390666066xx@xxx.xx
UserType                      : 0
Version                       : 1
Workload                      : AzureActiveDirectory
ClientIP                      :
ObjectId                      : johndoe01@upn
UserId                        : johndoe01@upn
AzureActiveDirectoryEventType : 0
Client                        : Exchange
LoginStatus                   : 0
UserDomain                    : xxx.xx
Get all logins from IP-address(es)

Let's say the account used for the examples above has been hacked and we need to know what other users have logged in from these IP-addresses during this time. It's not that different from the examples above. We will just have to make another foreach (+ one nestled) from the $Results:

#Get all logins from IP-addresses last two days
$IpSearch = @()

foreach ($IPAddress in ($Results.IPAddress | select -Unique)) #Gets only Unique IP-addresses
    $search = Search-UnifiedAuditLog -IPAddresses $IPAddress -EndDate 2017-02-06 -StartDate 2017-02-04 -ResultSize 5000

    #Loop through each of the IP-addresses
    $search | % {
        #Initialize a return object
        $return = "" | select Date,IPAddress,Client,Operation, UserID
        #Convert the JSON to a PSObject      
        $data = $_.AuditData | ConvertFrom-Json

        #Populate the return object with AuditData
        $return.Date = $data.CreationTime
        $return.IPAddress = $data.ClientIP
        $return.Client = $data.Client
        $return.Operation = $data.Operation
        $return.UserId = $data.UserId

        #Returns the data to outside of the loop
        $IpSearch += $return


#Export to CSV
$IpSearch | Export-Csv -Delimiter "," -Encoding UTF8 -NoTypeInformation -Path C:\Exports\AllLoginsOnIPAddressesUsedByjohndoe01.csv

Enjoy ;)

Andreas Selevik

Andreas Selevik

Solution Architect specialized in Windows & Azure Architecture, Office 365, PowerShell, Identity management and automation. Manchester United fanatic, father, husband and a very good winner...

Read More