#PowerBI Audit log using #Office365 Management API
- Feb 13, 2018
- 7 min read
Updated: Nov 14, 2025
I love automating things. I've read somewhere that a good developer always tries to automate things, but in my case, I'm just lazy, so I automate! That's why I started exploring various Power BI-related APIs. I was working towards a single resource I could use to answer questions about the Power BI tenant. During this process, I've written different posts: Power Bi Template to document the Power Bi Service,HOW TO FIND ALL POWER BI USERS IN AN ORGANISATION? I'd recommend reading those posts as they cover how I created the Azure authorisation token in more detail. After exploring all those APIs, I still had a few unanswered questions like:
What’s happening on my tenant?
Who is doing what?
Users interest in?
Should I disable export or print etc.?
Does anyone ever users dashboards?
Using workspaces is a good idea?
Who updated the App?
Many more
These questions guided me to Power BI Admin Auditing, which has all the data I wanted. But I didn't want to search and export every single time I wanted to do something, and I'm rubbish with PowerShell — that's out of scope for me. So I went searching for another API...
Office 365 Management API:
Office 365 management API enables you to do many management tasks using API, like Audit search.
Office 365 Management API and Power BI:
I don’t know why I play with API’s, most of API service providers are very good at sending pointless error information, and I had no special luck with Office 365 API. Among all Office 365 API was a bit forgiving one. The root URL of the API is https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/{operation}. All API requests require an Authorization just like Power BI API. This page https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference covers all information about requests and various parameters.
The Activity API supports the following operations:
Start a subscription to begin receiving notifications and retrieving activity data for a tenant.
Stop a subscription to discontinue retrieving data for a tenant.
List current subscriptions
List available content and the corresponding content URLs.
Receiving Notifications sent by a webhook when new content is available
Retrieving content by using the content URL.
List notifications sent by a webhook.
Retrieve resource friendly names for objects in the data feed identified by guides.
From <
To get data from API, you first need to enable a subscription. For this, we need to POST requests using Power BI. This is where I stumbled most,
Calling office 365 API using Power BI/ Power Query
I first created several parameters: TennantID, ClientID,ClientSecret,UserName,Password,publisher identifier. The first thing is to get authorization token. Getting started with Office 365 management API explains how authentication works
let
// App Details
client_id = client_id,
client_secret = client_secret,
// Authentication
// URI's
redirect_uri = "http://localhost:13526/redirect",
token_uri = "https://login.windows.net/common/oauth2/token",
authorize_uri = "https://login.windows.net/common/oauth2/authorize",
resource = "https://manage.office.com",
// User Details
username = username,
password = password,
tokenResponse = Json.Document(
Web.Contents(
token_uri,
[
Content = Text.ToBinary(
Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
username = username,
password = password,
resource = resource,
grant_type = "password",
redirect_uri = redirect_uri
])
),
Headers = [Accept = "application/json"],
ManualStatusHandling = {400}
]
)
),
access_token = tokenResponse[access_token],
token = tokenResponse[access_token]
in
tokenTo make sure I have a valid connection, I create a query to pull all services on my Office 365
let
getgroups_uri = "https://manage.office.com/api/v1.0/" & TennantID & "/ServiceComms/Services",
GetGroups = Json.Document(
Web.Contents(
getgroups_uri,
[
Headers = [
Authorization = "Bearer " & O365RefToken
]
]
)
),
value = GetGroups[value],
#"Converted to Table" = Table.FromList(
value,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{"Id", "DisplayName", "Features"},
{"Id", "DisplayName", "Features"}
)
in
#"Expanded Column1"Then starting a subscription. URL to start subscription is https://manage.office.com/api/v1.0/”&TennantID&”/activity/feed/subscriptions/start?contentType=Audit.PowerBI. Below query didn't work at all for a while and it started working suddenly, I genuinely don’t know why. But below is the query I used to start the subscription. When I run this script, it returns an error, but subscription was any way enabled.
let
getgroups_uri = "https://manage.office.com/api/v1.0/" & TennantID & "/activity/feed/subscriptions/start?contentType=Audit.General",
content = "{
""webhook"": {
""address"": ""https://webhook.myapp.com/o365/"",
""authId"": ""o365activityapinotification"",
""expiration"": """"
}
}",
Auth = "Bearer " & O365RefToken,
GetGroups = Web.Contents(
getgroups_uri,
[
Headers = [
#"Authorization" = Auth,
#"Content-Type" = "application/json;charset=utf-8"
],
Content = Text.ToBinary("")
]
),
#"Imported JSON" = Json.Document(GetGroups, 65001)
in
#"Imported JSON"You can view enabled subscriptions using following script
let
getgroups_uri = "https://manage.office.com/api/v1.0/" & TennantID & "/activity/feed/subscriptions/list",
GetGroups = Json.Document(
Web.Contents(
getgroups_uri,
[
Headers = [
Authorization = "Bearer " & O365RefToken
],
ManualStatusHandling = {400}
]
)
),
#"Converted to Table" = Table.FromList(
GetGroups,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{"contentType", "status", "webhook"},
{"contentType", "status", "webhook"}
)
in
#"Expanded Column1"Office 365 Management API for Power BI Audit Logging:
I'm interested only in Power BI Audit, but you can use this to view other Audit logs too. As per API Schema, all Power BI Activities has RecordType 20, So I used following script to pull all Power BI Content.
let
// Get subscriptions list
getgroups_uri = "https://manage.office.com/api/v1.0/" & TennantID & "/activity/feed/subscriptions/list",
GetGroups = Json.Document(
Web.Contents(
getgroups_uri,
[
Headers = [
Authorization = "Bearer " & O365RefToken
],
ManualStatusHandling = {400}
]
)
),
// Convert to table and expand subscriptions
#"Converted to Table" = Table.FromList(
GetGroups,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{"contentType", "status", "webhook"},
{"contentType", "status", "webhook"}
),
// Process content types
contentType1 = #"Expanded Column1"[contentType],
#"Converted to Table1" = Table.FromList(
contentType1,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Renamed Columns" = Table.RenameColumns(
#"Converted to Table1",
{{"Column1", "Content"}}
),
// Get content data
#"Invoked Custom Function" = Table.AddColumn(
#"Renamed Columns",
"GetContent",
each GetContent([Content])
),
#"Replaced Errors" = Table.ReplaceErrorValues(
#"Invoked Custom Function",
{{"GetContent", null}}
),
#"Expanded GetContent" = Table.ExpandTableColumn(
#"Replaced Errors",
"GetContent",
{"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"},
{"contentUri", "contentId", "contentType", "contentCreated", "contentExpiration"}
),
// Filter for Audit.General content
#"Filtered Rows" = Table.SelectRows(
#"Expanded GetContent",
each ([contentUri] <> null) and ([contentType] = "Audit.General")
),
// Get URI data
#"Invoked Custom Function1" = Table.AddColumn(
#"Filtered Rows",
"Fn_GetURIData",
each try Fn_GetURIData([contentUri]) otherwise null
),
#"Added Custom" = Table.AddColumn(
#"Invoked Custom Function1",
"Custom",
each List.Count(Table.ColumnNames([Fn_GetURIData]))
),
#"Removed Errors" = Table.RemoveRowsWithErrors(
#"Added Custom",
{"Custom"}
),
// Filter for Power BI workload
#"Added Custom1" = Table.AddColumn(
#"Removed Errors",
"Custom.1",
each Table.Contains([Fn_GetURIData], [Workload = "PowerBI"])
),
#"Filtered Rows1" = Table.SelectRows(
#"Added Custom1",
each ([Custom.1] = true)
),
// Expand audit data
#"Expanded Fn_GetURIData" = Table.ExpandTableColumn(
#"Filtered Rows1",
"Fn_GetURIData",
{
"Id", "RecordType", "CreationTime", "Operation", "OrganizationId",
"UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent",
"Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName",
"WorkspaceId", "ObjectId", "DatasetId", "ReportId"
},
{
"Id", "RecordType", "CreationTime", "Operation", "OrganizationId",
"UserType", "UserKey", "Workload", "UserId", "ClientIP", "UserAgent",
"Activity", "ItemName", "WorkSpaceName", "DatasetName", "ReportName",
"WorkspaceId", "ObjectId", "DatasetId", "ReportId"
}
),
// Type conversions
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Fn_GetURIData",
{
{"Content", type text},
{"contentUri", type text},
{"contentId", type text},
{"contentType", type text},
{"contentCreated", type datetime},
{"contentExpiration", type datetime},
{"Id", type text},
{"RecordType", Int64.Type},
{"CreationTime", type datetime},
{"Operation", type text},
{"OrganizationId", type text},
{"UserType", Int64.Type},
{"UserKey", type text},
{"Workload", type text},
{"UserId", type text},
{"ClientIP", type text},
{"UserAgent", type text},
{"Activity", type text},
{"ItemName", type text},
{"WorkSpaceName", type text},
{"DatasetName", type text},
{"ReportName", type text},
{"WorkspaceId", type text},
{"ObjectId", type text},
{"DatasetId", type text},
{"ReportId", type text},
{"Custom", Int64.Type},
{"Custom.1", type logical}
}
),
// Clean up columns
#"Removed Columns" = Table.RemoveColumns(
#"Changed Type",
{
"Content", "contentUri", "contentId", "contentType",
"contentCreated", "contentExpiration", "RecordType",
"OrganizationId", "UserKey", "Custom", "Custom.1"
}
),
// Extract user name from email
#"Duplicated Column" = Table.DuplicateColumn(
#"Removed Columns",
"UserId",
"UserId - Copy"
),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Duplicated Column",
"UserId - Copy",
Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv),
{"UserId - Copy.1", "UserId - Copy.2"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"UserId - Copy.1", type text},
{"UserId - Copy.2", type text}
}
),
#"Removed Columns1" = Table.RemoveColumns(
#"Changed Type1",
{"UserId - Copy.2"}
),
#"Replaced Value" = Table.ReplaceValue(
#"Removed Columns1",
".",
" ",
Replacer.ReplaceText,
{"UserId - Copy.1"}
),
// Final column renames
#"Renamed Columns1" = Table.RenameColumns(
#"Replaced Value",
{
{"UserId - Copy.1", "Name"},
{"WorkSpaceName", "WorkSpace Name"},
{"ReportName", "Report Name"},
{"DatasetName", "Dataset Name"},
{"CreationTime", "Creation Time"}
}
)
in
#"Renamed Columns1"
And we can create beautiful reports like this
Power BI Template:
I also created a template; you can download the template here – https://1drv.ms/u/s!Avm7gbgZtlMlvkMAQEGMyQdLsfHu
When you open the template it will ask for loads of the parameters:
Get data for last 24 months – A boolean value to pull data for last 24 hours or the given date. If this value is true, Power BI excludes values given in the next two parameters
AuditlogStartTime & AuditLogEndTime -Start Time of the Audit, Audit start and end has to be with 24 hours apart as per API documentation. and date has to be in below format
YYYY-MM-DD YYYY-MM-DDTHH:MM YYYY-MM-DDTHH:MM:SS
PubisherIdentifier – I haven't used this, but it's recommended to use this for API calls
ClientID – Azure AD Client ID
ClientSecret – Azure AD App Client Secret
UserName& Password – User who has permission to view Office 365 Audit log
So does it answer my questions:
Well, I still have many unanswered questions, but this does help me in many ways. Using this template I can see the user behaviour, not only how many views etc. but interesting information about user activities. For example, in a certain workspace, if most of the users are exporting the report, then users aren't happy with the way I presented the data. Ideally, I don’t encourage users to be added to the workspace, using this I can see who are adding members to groups. I can see when a Power BI App created or updated. I can see how many users are printing the report, if many people are printing reports, probably I shouldn't use many bookmarks, drill through functionality and add more Titles.
Summary:
There are many things I learned doing this process, PowerQuery Web.Contents can be used to POST and ZoomCharts introduced a logo underneath the visual to encourage users to buy their visuals 🙂
from API and I can go back to 7 days only. Users need access to the Azure AD App and make sure Azure AD App has access to Office 365 Management API access. This page explains all steps –
Hope this helps someone out there
Keep Smiling,
Prathy 🙂
Comments