#PowerBI Audit log using #Office365 Management API

I love automating things, I have read somewhere, a good developer always tries to automate things, but in my case, I am just lazy, so I try to automate! That is why I started exploring various Power BI related API’s. I was working towards a single resource which I can use to answer questions related to Power BI Tennant. During this process I have written different posts:  Power Bi Template to document the Power Bi Service, HOW TO FIND ALL POWER BI USERS IN AN ORGANISATION? I would recommend reading those posts as they cover how I created Azure Authorization Token a bit more in detail. So, after exploring all those API’s, I still had 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; it has all the data I wanted. But, I didn’t want to do search and export every single time I wanted to do something, and I am rubbish with PowerShell, that’s out of scope for me, there I went for 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 <https://msdn.microsoft.com/en-us/office-365/office-365-management-activity-api-reference>

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, Chris Webb has a fantastic blog post explaining POST Requests using Power BI – https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/

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

    token

To 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 did not 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 am 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
   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"}),
    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"}}),
    #"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"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded GetContent", each ([contentUri] <> null) and ([contentType] = "Audit.General")),
    #"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"}),
    #"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)),
    #"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"}),
    #"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}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Content", "contentUri", "contentId", "contentType", "contentCreated", "contentExpiration", "RecordType", "OrganizationId", "UserKey", "Custom", "Custom.1"}),
    #"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"}),
    #"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

• TennantID – Office 365 Tennant ID
• PubisherIdentifier – I have not used this, but it is 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 are not 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 should not 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 🙂

This entire model has many limitations, first of all, I can not schedule it as I am using two different API’s one to get Token and other to call API, but if you have Power BI Report Server, this can be scheduled. Next, data is always only for 24 hours, this is a limitation 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 – https://msdn.microsoft.com/en-us/office-365/get-started-with-office-365-management-apis . 

Hope this helps someone out there

Keep Smiling,

Prathy 🙂