What to Search from your Office 365 Audit Logs?


14 December 2020

This is a guide for quickly accessing and running advanced queries on your Office365 audit logs with your desktop computer and  the free edition of SpectX.  Whether you need to know more about logins, accessing, editing, deleting files - O365 logs are the data to go to.  The query examples we've prepared will give you a broad overview of user activities and zoom into the suspicious. If you're in the incident response phase, you can use the queries to learn the details. 

Query Examples on Office 365 logs

For the purposes of this article, we created a collection of SpectX queries to run on Office365 audit logs.  The default queries run on sample data we've stored in a public S3 bucket - feel free to try them out to get a feel of what an O365 log feels and looks like.  See instructions in step 4 on how to modify the _include file and point SpectX to our own data instead.

Let’s get started - we’ll walk you through the steps on accessing the logs, connecting them with SpectX and asking-answering questions.

Step 1: Enable Audit Logging for O365

The first step is easy: turn on the logs if you already haven’t:
  1. Log in to the Microsoft Security and Compliance Center 
  2. Search for ‘Audit log search’
  3. Click ‘Turn on auditing’.
Microsoft now starts collecting and storing your logs into its cloud universe. In addition to Office 365, you’ll also have access to Azure AD and Azure logs - we’ll talk about these in another blog post. Anyhow, after a few hours, the first records should be available for analysis.

Step 2: Install SpectX

If your use case is ad-hoc analysis, you can connect to Microsoft API from your desktop, using the free edition of SpectX. If your infrastructure is in the cloud, contact us for an enterprise trial and try running the SpectX server edition on an Azure VM. It takes a few minutes to complete the installation and get the first queries running.

Note: The free edition of SpectX supports querying O365 csv exports. Retrieving and querying Office365 logs via the Office 365 management API is a feature available in the free edition of SpectX until 1 April 2021. Contact us for an enterprise trial to connect to your logs via Microsoft API later than this.

Step 3: Access or Export the Logs

Having turned on logging in the first step, the data is now dripping somewhere into Microsoft’s cloud environment but it’s not too easy to access the data for analysis.

The options are:
  • Conduct simple searches using the point-and-click UI in the Security and Compliance Center
  • Exporting the logs manually into a CSV-file from the UI or
  • Export the logs with using Powershell 
  • Fetching the logs via Microsoft API
Here are step-by-step instructions in our docs for all of these options.

Security and Compliance Center UI limitations

The Security & Compliance Center UI lets you create filters from tons of activities going back up to 90 days, look at individual users and a certain time interval. However, the point-and-click UI leaves you helpless as soon as you need more sophisticated queries like applying negative filters (“Show me all admin users but not user X, Y and Z”), or aggregations (“How many times has a unique user logged in from different unique subnets?”). If your organization is generating massive amounts of logs, the 5000-event limit in the search results is also an issue.

Advanced queries and more data - longer than 7 or 30 days?

So, how to get the data out programmatically for more advanced analysis? The most reliable way of creating a full data archive of your AD/O365 logs is to query them regularly via API calls and store the results in a safe place.

As to time limitations, a single API call lets you go back 7 days. Grabbing the data regularly will give you peace of mind on having a fullest possible dataset to investigate in case something happens. SpectX offers a quick and convenient way for achieving this: get SpectX, register SpectX in the Azure management portal and connect SpectX to the logs by creating a new msapi:// protocol datastore. Running queries regularly will cache the raw data into the machine running SpectX where you can keep it as long as the disk capacity and your data retention policies allow.

Another option for getting your hands on the data is to export the records from the GUI or for a better result, use Powershell to automate the export. You’ll then need a tool to play with the .csv’s, preferably one like SpectX with flexible options for working with the Audit Data field that contains nested json.

First Peek via API

Once installed and connected via API, you’ll see three different log collections when browsing your newly created Microsoft API datastore: Azure, Azure AD and Office365. In this article, we’ll take a closer look at Office365 and leave the first two for future posts.

Note that when running the API-query for the first time on a large dataset, the query can take hours to complete - transferring all the records from Microsoft takes time. As data is cached to the local machine, the next queries will be faster, not to mention the follow-up queries.

Broken Audit Data?

A final note on an interesting issue. While doing research for this article, we noticed several unsolved complaints in forums about the AuditData field being broken in the exported csv file. That is, the (nested) JSON gets truncated at 3060 (some say 3062) characters. We could not replicate this issue (ping us if you can!) - our AuditData contained more data than 3062 bytes on several occasions.

Step 4: To the Queries!

Having connected SpectX to your logs, it’s time to start asking questions from the data.
If you don’t know yet what to ask, check out our Office 365 query pack in Github for dozens of queries including events in time, top active admins and new mailboxes created. The easiest way to use the pack is to:
  1. Download the query pack master.zip from Github
  2. Right-click on the resource tree in SpectX > Upload > pick master.zip
  3. Go to the Office365 folder in the resource tree and open (double-click) the _include file 
By default, the queries run on SpectX’ sample data. Modify the stream names in the include file to run the queries on your data. 
1. replace the $datastore value with the name of your SpectX O365 datastore you created in Step 3
2. replace the value of the streams @O365_exchange and @Office365_ad with one of the ranges, for example @Office365_ad = @O365_3d;
The patterns (schemas) in the query pack are designed for data retrieved via the API. To analyze the .csv export, navigate to the file using Input Data browser and press on 'Prepare Query'.

Query Examples on Office365 Exchange logs

Here are a couple of query examples for getting a quick overview of what the admins and users are up to when using mailboxes.
Quick overview. Frequency of Exchange events in 1-hour intervals

Top operations in Exchange. 
@Office365_exchange
| select(Operation, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(1000)
Updates to inbox rules. This is interesting because hijacked accounts often get new rules. 
@Office365_exchange
| filter(Operation like "%InboxRule%")
| select(CreationTime,
        Parameters[Name],
        UserId,
        Operation,
        Parameters
)
A user complaining an email is missing? Check deleted emails:
@Office365_exchange
| filter(RecordType = "3")
Top active admins. Who’s doing all the hard work?
@Office365_exchange
| filter(UserType = "Admin")
| select(UserId, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| limit(1000)
Explore more queries in the query pack for Exchange logs. 

Query examples on Office365 AD logs

Who added external users to which group and when? 
@Office365_ad
| filter(Operation = "Add member to group.")
| select(CreationTime,
        UserId,
        GroupDisplayName:ModifiedProperties['Group.DisplayName'][NewValue],
        TargetUsers:ARRAY_REMOVE_NULLS(ARRAY_AGG(fields[Target][1][ID]))
)
| group(GroupDisplayName)
| select(*,UsersCount:ARRAY_LEN(TargetUsers))
| filter(string(TargetUsers) contains '#EXT#')
How many times has a user logged in? How many times have they succeeded? Failed?
@Office365_ad
| select_replace(ClientIP:IPADDR(ClientIP))
| filter(RecordType = "15")
| select(
        Total:COUNT(*),
        Succeeded:COUNT(ResultStatus = 'Succeeded' AND (fields[LogonError] IS NULL OR fields[LogonError] = 'None' )),
        Failed:COUNT(ResultStatus = 'Failed' OR (ResultStatus = 'Succeeded' AND (fields[LogonError] IS NOT NULL AND fields[LogonError] != 'None' ))),
        UserKey
)
| group(UserKey)
| sort(Failed DESC)
 Useragents per users
@Office365_ad
| filter(RecordType = "15")
| select(UserKey, cnt:count(*),ExtendedProperties[UserAgent])
| group(UserAgent,UserKey)
| select(UserKey, cnt:count(*),UserAgents:REMOVE_NULLS(ARRAY_AGG(UserAgent)))
| group(@1)
| sort(cnt DESC)
| filter(cnt > 2)
Top logon errors
@Office365_ad
| filter(RecordType = "15")
| select(
        Successful_login:ResultStatus = 'Succeeded' AND (fields[LogonError] IS NULL OR fields[LogonError] = 'None' AND lower(string(fields[RequestType])) not contains 'logout' ),
        fields[LogonError],*
)
| select(LogonError, cnt:count(*))
| group(@1)
| sort(cnt DESC)
| filter_out(LogonError IS NULL)
Logins by subnets
@Office365_ad
| group(lower(UserKey),ClientIP)
| select(
        login_count:count(*),
        Succeeded:count(ResultStatus = 'Succeeded' AND (fields[LogonError] IS NULL OR fields[LogonError] = 'None' AND lower(string(fields[RequestType])) not contains 'logout' )),
        Failed:count(ResultStatus = 'Failed' OR (ResultStatus = 'Succeeded' AND (fields[LogonError] IS NOT NULL AND fields[LogonError] != 'None' ))),
        UserKey,UserId,
        subnets:ARRAY_UNIQ(ARRAY_AGG(IP_TRUNC(ClientIP,24,64))),
        Logins:ARRAY_AGG(ClientIP + ' '+cc(ClientIP) + ' ' + UPPER(ResultStatus))
)
| group(lower(UserKey))
| select(subnets_count:ARRAY_LEN(subnets),*)
| sort(subnets_count DESC)

Conclusion

Office 365 logs are a blessing to get visibility into what’s happening in your organisation. The main challenge is getting your hands on a fullest possible set of raw logs. Once you’ve been able to set up the collection and retention, the queries will give you valuable insights on trends and outliers. 

Back to articles