Accessing Office 365 Audit log API in Power Platform can be tricky – Here’s why..

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.

  1. 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:

  1. Create and register app in Azure AD > Note down (ApplicationID, Client Secret, TenantID)
  2. Authenticate using Power Automate > You will get the access_token in the response
*resource value should be : https://manage.office.com

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

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: