Home Power BI #PowerBI Audit log using #Office365 Management API

#PowerBI Audit log using #Office365 Management API

by Prathy Kamasani

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 🙂

You may also like

25 comments

Rashmi 6 December 2019 - 1:18 pm

Hi Prathy,

I want to try access MS O365 api for Power BI activities. I want to check with Postman method in which I need “correct URL” to access those API’s. I am able to access Power BI rest Admin API’s through Postman

Reply
sachin 27 September 2019 - 9:13 pm

Hi Prathy

I ve downloaded your template and was not able to use it because i do not have a pulisherIdentifier. Could you pls explain what that information is. I have rest of the authentication except for that.

Thanks in advance.

Reply
Emily 3 July 2019 - 1:53 am

Hi Parathy, it resulted an error on my end showing “The user or administrator has not consented to use the application with ID name. Send an interactive authorization request for this user and resource.

Reply
Adolfo Socorro 10 January 2019 - 2:53 pm

Hi Prathy, thanks for this. One question: in the results I am getting ReportId values that don’t exist but next to report names that do exist (in the same workspace). Have you experienced this? I noticed when trying to determine how to visualize user counts by report taking into consideration that two workspaces may have reports with the same names. Thanks.

Reply
Prathy Kamasani 11 January 2019 - 9:18 am

I have not experienced that, having said that I have not worked with API lately, things may have changed. One thing I did notice when I looked at API last time was with the New AppWorkspaces; there were blank Report ID’s. Are you working with New AppWorkspaces?

Reply
ksbi 15 May 2018 - 2:53 pm

Hi Prathy,

Thank you for this very useful Post,
Witch kind of permission do we need to access the power bi logs using the API?
When i use the admin account, my report works fine but, when i try a simple user i get an Internal Error.

What is the necessary permission?

Thank you in advance.

Reply
naren 7 May 2018 - 2:46 pm

Hi Prathy,
Thanks for the great help. I have followed as like you are instructed. It seems everything was okay except this API “ServiceComms/Services”. This Api returns “Authorization has been denied for this request.”

Let me know is any additional permission needed in office 365. Please explain briefly

Naren

Reply
naren 3 May 2018 - 2:49 pm

when i try to open in your template, i facing the issue for “https://manage.office.com/api/v1.0/”&TennantID&”/ServiceComms/Services” these api.
I get the message Web.Contents failed to get contents from https://manage.office.com/a… (500): Internal Server Error, I’m unable to get the template to work. Any idea of these error Please share.

Reply
Prathy Kamasani 3 May 2018 - 9:08 pm

Hi Naren,

It could be anything! I would first check if you have rights to view data. Can you see if you can see audit log from Office 365 Admin portal?

If you can then go to query editor and see if any query’s are returning data. Maybe try copying one query and see if you can get results or not; if that works, the template should work too.

Hope that helps

Reply
infoapp 2013 4 May 2018 - 9:10 am

Hi Prathy,
Naren here,Its good to hear back from you.
Service Comms API not working for me from both postman and inside application it shows the 500InternalServerError sometimes and sometimes this error “Authorization has been denied for this request.”. Is there something I’m missing here?.
Hope these attached images might help you to give us some suggestions.
Thanks
Naren.

https://uploads.disquscdn.com/images/1733defc55a9d6ef97503e32161da5f13efa88087a8329f75b5efeabce0e73e5.png https://uploads.disquscdn.com/images/c7fc725924a8a1500121bc7451c874eab3fc9390b32ca154bfb434c74fd7f6f2.jpg https://uploads.disquscdn.com/images/769e4b3662edf4a9f13efe0db50df05376f0639c28f842827ebe0b0b6e666f6b.jpg

Reply
naren 5 May 2018 - 7:25 am

Hi Prathy,

For latest Azure AD portal, is we need to configure an X.509 certificate to enable service-to-service calls?

Thanks
Naren.

Reply
Prathy Kamasani 7 May 2018 - 7:06 am

Hi Naren,

Sorry for slow response. Looks like the account you are using to get data does not have enough rights. You can find some trouble shooting help from this – https://msdn.microsoft.com/en-us/office-365/troubleshooting-the-office-365-management-activity-api
Basically, you need permission to read sensitive data on Office 365.

Once you have the right permissions see if you are getting any RefToken. That’s the step 1. After that try to create a subscription.

This API was a bit flaky for me too, especially creating subscriptions!

Prathy

Reply
naren 7 May 2018 - 2:36 pm

Hi Prathy,
Thanks for the great help. I have followed as like you are instructed. It seems everything was okay except this API “ServiceComms/Services”.

Let me know is any additional permission needed in office 365. Please explain briefly

Naren

naren 8 May 2018 - 4:49 am

Hi Prathy,
Thanks for the great help. I have followed as like you are instructed. It seems everything was okay except this API “ServiceComms/Services”.

Let me know is any additional permission needed in office 365. Please explain briefly

Thanks
Naren

https://uploads.disquscdn.com/images/79b2732654faa0e5c45eb430464813a02597285387ea90ccca56a4b49d605b61.jpg

naren 9 May 2018 - 5:44 am

Hi Prathy,
Thanks for the great help. Please ignore all my last comments.
I have done as per you are instructed, everything was working fine but no data reflected in Power query. After applying query changes got something like the following error.

Naren

https://uploads.disquscdn.com/images/a2b680a6415d6d69372d2ed5f4d0d19e15eb4621eee6ea3a5efe084cf01c7ab0.jpg

Prathy Kamasani 11 May 2018 - 11:01 am

Hi Naren,
disable load for subscriptions query. as it is a post request, you will get an error.

naren 5 May 2018 - 8:23 am

Hi Prathy,

For latest Azure AD portal, is we need to configure an X.509 certificate to enable service-to-service calls?
Thanks,
Naren.

Reply
Rajendra 20 March 2018 - 11:28 am

Great help. Thx.

Reply
prathyusha kamasani 2 March 2018 - 2:58 pm

prathy testing comments

Reply
cann0nf0dder 21 February 2018 - 11:30 am

Please ignore my last comment. I seemed to have got past that problem now. If I go into Advance editor, and goto O365RefToken and click refresh, I get a token value back. However, The Services still doesn’t work. I get an error message now saying “Formula Firewall:Query ‘Services’ (step ‘Expanded Column1′) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Which I have no idea what that actually means.

Sometimes I get the message Web.Contents failed to get contents from “https://manage.office.com/api/v1.0//ServiceComms/Service’ (500): Internal Server Error

I’m unable to get the template to work.

Reply
Prathy Kamasani 2 March 2018 - 11:41 am

Hi, For me Office 365 API is very flaky, I did receive errors initially but eventually, it worked. Can you go to query editor and see if subscriptions were enabled or not, also make sure have required permissions to view the content.

Reply
cann0nf0dder 21 February 2018 - 10:42 am

Hi, This blog post looks exactly what I need for my reporting needs. However, after downloading your template, and entering all my details, I get a Privacy Levels box appear. My options are to Ignore the privacy levels, or set up public, Organizational, or Private for the following URLs manage.office.com and login.windows.net. I’m not sure what I should be setting these too. When I chose to ignore the privacy levels. The next step when it tries to get the data, The Services error message states The field ‘access_token’ of the record wasn’t found. Any idea how to get past this?

Reply
cann0nf0dder 20 March 2018 - 3:59 pm

Please ignore my last comment. I seemed to have got past that problem now. If I go into Advance editor, and goto O365RefToken and click refresh, I get a token value back. However, The Services still doesn’t work. I get an error message now saying “Formula Firewall:Query ‘Services’ (step ‘Expanded Column1′) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Which I have no idea what that actually means.

Sometimes I get the message Web.Contents failed to get contents from “https://manage.office.com/api/v1.0//ServiceComms/Service’ (500): Internal Server Error

I’m unable to get the template to work.

Reply
Prathy Kamasani 20 March 2018 - 3:59 pm

Hi, For me Office 365 API is very flaky, I did receive errors initially but eventually, it worked. Can you go to query editor and see if subscriptions were enabled or not, also make sure have required permissions to view the content.

Reply
cann0nf0dder 20 March 2018 - 3:59 pm

Hi, This blog post looks exactly what I need for my reporting needs. However, after downloading your template, and entering all my details, I get a Privacy Levels box appear. My options are to Ignore the privacy levels, or set up public, Organizational, or Private for the following URLs manage.office.com and login.windows.net. I’m not sure what I should be setting these too. When I chose to ignore the privacy levels. The next step when it tries to get the data, The Services error message states The field ‘access_token’ of the record wasn’t found. Any idea how to get past this?

Reply

Leave a Reply