In this blog, we discuss applying an audit log with easy solutions. Make sure you have access (permission) to compliance Microsoft 365.
How to Export Audit log for Each User
Log in: https://compliance.microsoft.com/
Click on Audit in menu:
Fill in the Date and time range in a general report. For a specific report you can fill in the other fields:
You should then receive some results:
A unified audit log gives the ability for activities performed in different Microsoft 365 services (30 Apps) such as Azure Active Directory, Data connectors, Microsoft teams, power BI, etc.
The activities from a specific service displayed in “Audited activities” is this link: Search the audit log in the Microsoft Purview compliance portal – Microsoft Purview (compliance) | Microsoft Docs
You can log a maximum of 50,000 events per download (audit entries per download). An average user creates 300-400 event per day. For example, a company with 1000 user and 400,000 daily audit events (400,000/50,000= 8 downloads require). How overcome to this limitation? Split up time is the easiest solution.
You can retain your audit log per user with the E3/E1 or E5 licenses. With E3/E1 license, it can save for 3 months and with E5 it can save for 1 year. Audit retention rules to store some audit data longer (requires E5 + additional license per user).
At the end of this part, we got Audit log 5 of users.
How to Merge all CSV Files
Open a command prompt or type cmd.exe in search. Then use “Copy *.csv combine.csv”
You will now have a file with the name you had decided upon combined with the audit log which has the users on it:
Once “combine.csv” is ready for analysis
How to Split CSV file Format “AuditData” Column to “Multiple Column”
you must decide which type of format file is the best. For a less complicated, convenient format: choose B. You can also choose A; however, it is a more complicated way.
A: Create a long code in Visual Basic excel and also change format file to *.xslm. The coding is challenging because you must consider all situations in Auditlog for accurate form. For example:
{“AppAccessContext
“:{“AADSessionId”
:”https:\/\/google.com
UserKey”:”i:0h.f|mem,etc.
B: Excel with embedded “JSON” format (JavaScript Object Notation)
We recommend this Microsoft Article: Export, configure, and view audit log records – Microsoft Purview (compliance) | Microsoft Docs and follow till 7th step. the article has a Note and Tip that we explain briefly in not important.
The Microsoft Audit log has 2 types of reports: Standard and Premium. If your report is a standard report, there is no need to worry about Note and Tip. So, if a standard report is less or beyond 1,000 rows, all columns have similar properties. It is recommended to not use Expand. It will complicate your analysis.
How to Extract What We Need to Audit
As you see a JSON format has a lot of columns.
If you are going to investigate your data, it is better to make decision about which type of activity is your goal. second question that activity is in what app of Microsoft. For example, exploring in email activities your users, you must choose Exchange app Microsoft 365.
Workload Column is an applicable step for first select one of apps Microsoft 356. According to your needs, you can select the operation column for extracting what you want. Here is a Microsoft article for advising your audit activity categories: Search the audit log in the Microsoft Purview compliance portal – Microsoft Purview (compliance) | Microsoft Docs
A completed report has been prepared in a scenario case for better understanding about importance Audit log. For instance, you want to investigate how many of user delete their files from their email. We chose the Exchange app of Workload column. The next step is to Hard Delete the operation column chosen. At the end for easy understanding, we used a Pivot table and chart on excel:
If you want to consider auditing the Premium Events (like sending of emails), the E5 License must be applied to the user.
Would you like to learn more about our services? Email [email protected] or call 289-803-9730. We would be happy to share more details about our self-service or fully managed eDiscovery services!
