cancel
Showing results for 
Search instead for 
Did you mean: 
RicoZhou

How to view the frequency of user usage for all workspaces in Power BI?

Scenario

 

If we want to get the frequency of user usage for all workspaces over a period of time for analysis, how should we do it?

 

Preparation

 

Currently, there are two options to track user activity: The Power BI activity log and the unified audit log.

First we can refer to the documentation to understand the difference between audit logs and activity logs.

 

Unified audit log 

Power BI activity log 

Includes events from Power BI, plus events from SharePoint Online, Exchange Online, Dynamics 365, and other services. 

Includes only the Power BI auditing events. 

Only users with View-Only Audit Logs or Audit Logs permissions have access, such as global admins and auditors. 

Global admins, Power Platform admins, and Power BI admins have access. 

Global admins and auditors can search the unified audit log by using the Microsoft 365 Defender portal and the Microsoft 365 Compliance Center. 

There's no user interface to search the activity log yet. 

Global admins and auditors can download audit log entries by using Microsoft 365 Management APIs and cmdlets. 

Global admins, Power Platform admins, and Power BI admins can download activity log entries by using a Power BI REST API and management cmdlet. 

Keeps audit data for 90 days 

Keeps activity data for 30 days (public preview). 

Keeps audit data, even if the tenant is moved to a different Azure region. 

Doesn't keep activity data when the tenant is moved to a different Azure region. 

 

Part 1: Export audit logs.

 

Global admins and auditors can search the unified audit log by using the Microsoft 365 Compliance Center, or download audit log entries by using Microsoft 365 Management APIs and cmdlets.

 

The following explains how to use the Microsoft 365 Compliance.

a) Access Microsoft 365 compliance: https://compliance.microsoft.com/.
  Or Select Admin portal in Power BI Service -> Audit logs -> Go to Microsoft 365 Admin Center.

 

b) Then select Audit tab, select date range and click search, then click Export to export the audit log to CSV file.

 

1.png

 

2.png

 

Part 2: Export activity logs.

 

(1) Use PowerShell.

 

Please run the following code in PowerShell to install the module, login to Power BI, and export the activity logs. In the export log section, you can add filters, date range, file storage path, etc. You can check the documentation for more details.

 

#Install Administrator Module (running as administrator)

Install-Module -Name MicrosoftPowerBIMgmt.Admin 

#Login to Power BI

Login-PowerBI

#Export the required activity log to JSON format

Get-PowerBIActivityEvent -StartDateTime 2021-12-21T00:00:00 -EndDateTime 2021-12-21T18:00:00 -ActivityType viewreport | Out-File –FilePath C:\Users\Administrator\Desktop\Process.json

 

1.png

 

(2) Use Power BI Rest API

 

Syntax:

GET https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime={startDateTime}&endDateTime={endDateTime}&continuationToken={continuationToken}&$filter={$filter}

 

Permissions:
The user must have administrator rights (such as Office 365 Global Administrator or Power BI Service Administrator) or authenticate by using a service principal.

 

Considerations and limitations:

  • The maximum 200 requests per hour.
  • When using service principals for authentication, you must enable service principal authentication for this read-only admin API, that is, you need add the security group containing the application to "Allow service principals to use read-only Power BI admin APIs" in the Power BI Service's Admin portal.
  • You can't sign into the Power BI portal by using service principal.
  • Power BI admin rights are required to enable service principal in the Admin API settings in the Power BI admin portal.

 

The results of this basic API are returned on an hourly basis, so we need to get the next set of audit activity events by sending the continuation token to the API. The continuation token can be found in the response of the API.

 

Sample Request:

#Run Get Activity Events API.

GET  https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='2021-12-21T01:55:00'&endDateTime='2021-12-21T16:55:00'

 

1.png

 

# Get the continuation token shown above and Send the continuation token to get the next set of audit activity events.

GET https://api.powerbi.com/v1.0/myorg/admin/activityevents?continuationToken='LDIwMjEtMTItMjFUMDE6NTU6MDAsMjAyMS0xMi0yMVQxNjo1NTowMCwxLCw%3D' 

 

1.png

 

(3) Use the Power BI API (Beta) custom connector.

 

PowerShell can only export activity logs for one day at a time, so if you want to get the activity logs for a period, you can try the Power BI API (Beta) connector.

a) Get the .mez file from this link: https://github.com/migueesc123/PowerBIRESTAPI/raw/master/Power%20BI%20API.mez

 

b) Place the connector. mez file in the [Documents]\Power BI Desktop\Custom Connectors folder. If this folder does not exist, create it.

In my example, the full path is “C:\Users\Administrator\Documents\Power BI Desktop\Custom Connectors folder”.

 

1.png

 

c) In Power BI Desktop, select File > Options and settings > Options > Security. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation or warning. Click OK, and restart Power BI Desktop.

 

2.png

 

d) Launch Power BI Desktop and find the Power BI API (Beta) connector.

 

3.png

 

e) After authenticating, go to the functions folder in the Navigator window and look for "Event Activity Log”.

 

1.png

 

f) Enter a date range for the event activity logs. Then click the Invoke button and you can start querying the data from the API.

 

I hope this article can help you.

 

Author: Winniz Zhang

Reviewer: Ula Huang, Kerry Wang

Comments

@RicoZhou 
This is awesome work, thank you!

Is it ok to ask for the Postman collections?

 

Thanks in advance,

Yoav

Polls
What is your favorite Power BI feature release for June 2022?