Friday, June 1, 2018

Searching the Office 365 activity log for failed logins

image

Inside the Office 365 Security & Compliance center, under the Search & investigation menu option on the left you’ll find Audit log search as shown above.

To run a search simply provide a start and end date and select the Search button at the bottom of the screen. You can refine your search by selecting a list of different activities if you want but here we’ll leave the option set to Show results for all activities.

Once the search results are returned you’ll see lots and lots of items as shown above.

image

If you now select the Filter results button in the top right, each column will now display a box at the top that you can enter text into.

image

You can now go into the column headers and enter further filtering information. Here I have added the text ‘fail’ to the Activity column as shown. This produces two results for failed user logins.

Adding a filter now only shows the matches on the page.

image

You can also export the data into CSV file by selecting the Export results button next to the filter button.

You can either download everything in the audit logs (Download all results) or just your search query (Save loaded results). Here I have select the Save loaded results option.

image

This will then download a CSV file that you can open in Excel and will look like the above.

image

To make these easier to read you should convert the out to a table from the Insert tab and then select the Table icon.

image

Now that you have a table go to the top row of the Operations column and select the arrow to the right of this as shown. This will display the above menu. Uncheck the Select all option at the top of the list in the lower portion of the displayed dialog box.

image

Scroll down this same list and locate the UserLoginFailed option and select it.

This will now basically filter the whole tables of entries to only display those that have a match is UserLoginFailed in the operations column.

image

Which is exactly the result that you see obtained above and the same results we received from the console.

Thus, you can search the audit logs inside Office 365 directly from the portal but you can also export them to Excel to gain more power over how you wish to manipulate and report these events.