In a recent project, I had to query Office 365 audit log API using Power Automate. While this looked like a fairly easy task, there were quite a few learnings along the way. I had to research and test different approaches/methods for this seemingly simple task.
I thought of sharing my experience in an article to help anyone who would have the same issues/concerns while trying to access Office 365 Audit log via Office 365 Management Activity API.
- Permissions
You must have Office 365 Audit Log access first . Here is the list of prerequisites.
If you need to see the audit log records, you can login to Office 365 Compliance Center: https://compliance.microsoft.com/auditlogsearch
2. Authentication
Option 1 – Only applicable if MFA is disabled for the global admin account.
You can use the Center of Excellence Starter Kit Audit log connector . This connector uses basic authentication and if global admin account has multi factor authentication enabled, connection will fail. In this case, you can try the second option.
Option 2 – Use Azure Active Directory for authentication.
This document explains all the necessary steps in order to register an application in Azure AD and then create custom connector to access audit log.
For some reason, this didn’t work for me. I had to go for the third option.
Option 3 – Use Power Automate to authenticate and connect to audit log
This is really simple and below are the steps:
- Create and register app in Azure AD > Note down (ApplicationID, Client Secret, TenantID)
- Authenticate using Power Automate > You will get the access_token in the response
Now we need to parse the response and get the access token. Steps are mentioned here.
Once we have the access_token, we can move to the next stage.
3. CREATING A CONTENT SUBSCRIPTION
First, we need to understand the Office 365 Management Activity API. I will summarize key points so that you can get a basic idea of how it works.
a. This API has 5 content types
- Audit.AzureActiveDirectory
- Audit.Exchange
- Audit.SharePoint
- Audit.General (includes all other workloads not included in the previous content types)
- DLP.All (DLP events only for all workloads)
In order to get a specific activity type, we need to first determine the content type.
i.e for Power BI related activities, we need to select content type as Audit.General
Note – We cannot directly access audit log as we do with Dataverse tables. We need to first create a subscription for the content type. This is a one time operation and we can use Postman or any similar tool for this.
Request – use Postman
POST : https://manage.office.com/api/v1.0/{tenant}/activity/feed/subscriptions/start?contentType=Audit.General
Body can have a few additional parameters you could use to create notifications and send data to webhooks. Read the full documentation to use these parameters.
Since we are not using Webhooks, body is empty in above request. If the subscription is created, you will get a response similar to this:
HTTP/1.1 200 OK Content-Type: application/json; charset=utf-8 { “contentType”: “Audit.General”, “status”: “enabled”}
Now we have successfully created the subscription. We can now query the Office 365 Management Activity API for the Audit.General content type and get relevant activities.
4. GET LOG DETAILS
Let’s go back to the Power Automate flow. Add another HTTP request action and enter below parameters.
Method : GET
URI: https://manage.office.com/api/v1.0/{tenantID}/activity/feed/subscriptions/content?contentType=Audit.General&startTime={startTime}&endTime={endTime}
We need to provide the startTime and endTime values in UTC format. I have used variables in the flow.
startTime : 2020-12-05T16:30 endTime : 2020-12-05T17:30
Sample response :
[
{
“contentUri”: “https://manage.office.com/api/v1.0/common/activity/feed/audit/20201206234909963050165$20201206234909963050165$audit_general$Audit_General$apac0010”,
“contentId”: “20201206234909963050165$20201206234909963050165$audit_general$Audit_General$apac0010”,
“contentType”: “Audit.General”,
“contentCreated”: “2020-12-06T23:49:09.963Z”,
“contentExpiration”: “2020-12-20T23:49:09.963Z”
}
]
As the response, we expect a list of activities however, the response contains a content URI. When we request the API for activities via a subscription. It will create a bundle of activities and send us the link to that bundle. Therefore, we cannot query this data unless we get the records using the Content URI and parse the activity set.
PARSE RESPONSE AND GET CONTENT URI
Add Compose action and insert the response body
Use JSON Parse action and parse the ContentURI:
Use below JSON schema to parse the body
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“contentUri”: {
“type”: “string”
},
“contentId”: {
“type”: “string”
},
“contentType”: {
“type”: “string”
},
“contentCreated”: {
“type”: “string”
},
“contentExpiration”: {
“type”: “string”
}
},
“required”: [
“contentUri”,
“contentId”,
“contentType”,
“contentCreated”,
“contentExpiration”
]
}
}
Once we have the contentUri. We can use GET method to retrieve activity records.
Sample response:
[
{
“CreationTime”: “2015-06-29T20:03:19”,
“Id”: “80c76bd2-9d81-4c57-a97a-accfc3443dca”,
“Operation”: “PasswordLogonInitialAuthUsingPassword”,
“OrganizationId”: “41463f53-8812-40f4-890f-865bf6e35190”,
“RecordType”: 9,
“ResultStatus”: “failed”,
“UserKey”: “1153977025279851686@contoso.onmicrosoft.com”,
“UserType”: 0,
“Workload”: “AzureActiveDirectory”,
“ClientIP”: “134.170.188.221”,
“ObjectId”: “admin@contoso.onmicrosoft.com”,
“UserId”: “admin@contoso.onmicrosoft.com”,
“AzureActiveDirectoryEventType”: 0,
“ExtendedProperties”: [
{
“Name”: “LoginError”,
“Value”: “-2147217390;PP_E_BAD_PASSWORD;The entered and stored passwords do not match.”
}
],
“Client”: “Exchange”,
“LoginStatus”: -2147217390,
“UserDomain”: “contoso.onmicrosoft.com”
}
]
This is an array of audit log items. We can now use this information within Power Platform.
This process is a bit tricky as we have to first understand the Office 365 Management API schema. However, once we understand the methods and their purpose, it’s just a matter of designing the flow. The objective of this article is to address most of the common issues/scenarios.
Note that there are different types of records in an audit log and schema will be different as they will include activity specific parameters.
i.e. if activity is “File Created” then it could have a “path” parameter.
In the next article we can discuss how to select a certain activity and get activity specific parameters.
Happy Learning!
Categories: AzureAD, Power Automate
immersivecoder 
Great guide.
It helped me a lot for a POC I needed.
Thank you!!!
LikeLike