Miguel Escobar Published January 18, 2020

Power BI Event Activity Log API – Easy way to get it!

Power BI

The Power BI team recently released a new endpoint for the REST API so we can get the Event Activity Log. You can read more about it from their official blog post here (url).

They also made available a PowerShell cmdlets so you can query your data from it but, what if it was easier to get that data straight into Power BI?

That’s exactly what I created – an easier way to get the Power BI Event Activity Log right inside your Power BI Desktop. Before I show you that easier way to get the data, let’s talk about the concept of this new API endpoint (the event activity log).

What Data can I get from event Power BI Event Activity Log?

Let me give you an example of what fields you can actually get from this new endpoint so you can have a better idea:

And in terms of how the data looks like, these are 2 screenshots to give you an idea:

The whole idea is that with this data you can audit what it’s actually happening in your tenant or Power BI workspace level which could be extremely helpful to understand what resources are being used the most and perhaps what resources are simply running just for the sake of running and not adding any value to your solution.

How can I get the data for the Power BI Event Activity Log?

There’s a few ways to get it, but I’ll show you the one that I’ve created through a Power BI Custom Connector and I’ll give you the most important benefits from this solution that you simply can’t get with others.

Get and configure the Custom Connector

The first step is to go get the Power BI REST API Custom Connector and set it up following the documentation found on the official repo here (url).

Launch the Custom Connector and find the Event Activities function

Once you have the Power BI REST API Custom Connector ready, just launch your Power BI Desktop and, after authenticating, in the Navigator window go to the Functions folder and look for the “Event Activity Log” as shown in the next image:

Click on the Transform Data button so you can be taken to the Power Query window.

Entering the date range for your event activity log

This is the first benefit that we get from this Custom Connector that we don’t have with something like the CMDLET. You can select to get data from multiple dates instead of just from one single date.

Just input your start and end date or even use the date pickers and then hit the invoke button so you can start querying the data from the API:

Another hidden benefit of the Custom Connector – is Incremental refresh ready!

Yes! If you’ve read my article about Power BI Incremental Refresh (here) you’ll know that all we need for the incremental refresh to be correctly configured is simply a function that needs a Start and End Date to drive the creation of the partitions.

I can happily say that the function previously mentioned to get the Event Activity Log meets these requirements almost 100%. You still need to use a DateTime parameters and transform those into Date type to be used in the connector, but I’ve tested this, and it simply works!

The Major benefit of this approach – it’s ease of use and integration with Power BI Desktop

While getting the data from PowerShell is quite straightforward, is not as easy as using Power BI Desktop and having the ability to analyze your data right within Power BI Desktop. With other approaches you need to export the response from the API into a file such as a csv, or import the data into another repository.

Do you find this Power BI Custom Connector helpful and would like to see it as a native connector?

Click here to let the Power BI Team know about it and have them certify the connector!

Power BI
Subscribe
Notify of
guest
43 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Frank Tonsen

“The Get-PowerBIActivityEvent cmdlet takes a StartDateTime and an EndDateTime parameter with the same restrictions as the ActivityEvents REST API, meaning the start date and end date must reference the same date value because you can only retrieve the activity data for one day at a time.”

Miguel, is this quote wrong and the ActivityEvents REST API itself is not restricted to one day?

Wallo

Could this be used on the PBI webservice with an scheduled refresh? And do you need tenant level access permissions approved in Azure in order to get all Power BI activities even for workspaces you are not in?

Ignacio

Hi there. I try every step on github (creating app and setting the txt files). When I get data from the custom connector and try to use the Event Logs with my account (i’m office 365 global admin) it shows me a message like “we couldn’t authenticate with the credentials. Try again”.

Am I missing something or the tutorial needs something else from the tenant? I can see you are using the app registration with secret, don’t you need to allow service principal for power bi tenant or something like that?

Patrick

I am also having a similar issue. I can see Workspaces, Gateways but not this funcion and the Power BI Service Administrator……although I’ve just been assigned this role today. Any ideas?

Akanksha Verma

hi Miguel.. thanks for this awesome connector. I already upvoted it at the link you gave. I just have one question. I am able to access the apis for dashboards, dataflows etc. but the Event Activity Log and the topics under Power Bi Service Administrator remain inaccessible. I just get a message saying credentials dont work but they work for other things like reports or dashboards informations. I think its some kind of admin access that I am missing but I am not sure where to add them. What kind of permissions should be given in the power bi App for this to work.

Saurabha J

Hi Miguel,

I checked this connector today and I am getting the same issue as Akanksha.
I am using the latest .mez from the repo.

I have also raised the issue on the repo as well.

Please guide.


Regards
Saurabha

varun devaraj

I have the exact same issue. Also, had a meeting with the infrastructure team on this. what we found from the Microsoft’s documentation is that if you want to pull the activity events you need to be a Power BI Service Administrator (In most cases we will be Capacity admins but not Power BI Service administrator). Depending on your company’s infra team, they make a decision on whether to give service admin roles to someone outside the core infra team.

Our approach to resolving this is to create a dataflow which will be done by the Infrastructure team and we will be pulling the output of the dataflow into the report. This will let me keep the security auditors and our team happy. Let me know if anyone has resolved this in a different way.

gaelle

Hello, I’m Power Bi Admini but an errors accured in the service for the call LogActivityEvent : The ‘ExcludePersonalWorkspaces’ column does not exist in the rowset. Table: Fonction appelée.

Good day

Dan

Hi… Would you be able to elaborate further on this or point to some more resources on the technique you described? Is it difficult? Thank you!!!

Nikki

Thank you for the super Connector. It made our life easier to fetch Usage and Inventory metrics around Power BI. I am the Power Platform Administrator and looking to setup the ActivityLog on refresh schedule in Power BI Service. How can I set the start and end dates to be 6 months duration based on the current date? Just want to make sure the date gets updated dynamically.

Mark Paradis

Hey Miguel, this connector is amazing! I love how easy it is to use, but regarding the Incremental Refresh I am having a issue with getting it to refresh in the service.

I have setup RangeStart, RangeEnd and incremental refresh is set to 3 year and 2 days refresh on the table. I can refresh in the desktop but when I publish and refresh starts and runs for about 30 min… but then errors out with this

“Data source errorThe ‘Id’ column does not exist in the rowset.”

Any help this would be HUGE

Dan

Would you be able to provide any helpful guidance or recommended reading on the subject of “implementing your own standard schema and making it error proof?” Thanks!!

Ricardo Pedro

HI Miguel,

Great Connector and very useful. I upgraded to the new mez connector and i am trying o get the logs just for one day.
But it stay as a loop and i don’t see the data being load. IT was working for the previews connector version.
Can you please advice ?
Thank you for your support and help

Sagar Bhargava

Hi Miguel,

Big thanks for the connector. Exactly what I required to report off the PowerBI Tenant Usage. Next step for me is to setup a Incremental refresh for this report and I have created the 2 new date type parameters and filtered the CreationTime column based on them. After I have done the above, the incremental refresh option is still ‘Greyed out’. Below is the query from the Advanced editor. I took the PowerBIRestAPI and created a Table ‘EventActivityLog’ out of it.

Do you know what I may have missed?

let
Source = PowerBIRESTAPI.Navigation(),
Functions = Source{[Key=”Functions”]}[Data],
EventActivityLog = Functions{[Key=”EventActivityLog”]}[Data],
#”Invoked FunctionEventActivityLog1″ = EventActivityLog(Range_Start, Range_End),
#”Convert to Date Time” = Table.TransformColumnTypes(#”Invoked FunctionEventActivityLog1″,{{“CreationTime”, type datetime}}),
#”Convert to Date” = Table.TransformColumnTypes(#”Convert to Date Time”,{{“CreationTime”, type date}}),
#”Filter by Range” = Table.SelectRows(#”Convert to Date”, each [CreationTime] >= Range_Start and [CreationTime] <= Range_End)
in
#"Filter by Range"

Thanks in advance for any help that you can provide on this.

Regards

Sagar Bhargava

Thats it! That was the issue. Thank you. Appreciate your quick reply on this.

jugal

I downloaded the .mez i dont know how to move forward and connect it? i cant find any custom connector or directory level get data on powerBI can you help?

Karthik Reddy

Hello Miguel,

Thanks for providing the custom connector…this is really helpful.
Am powerBI admin in our organization and i have one issue regarding this connector..when i select dataset datasources from PowerBI Service Admin tab i get error

Failed to save modifications to the server. Error returned: ‘OLE DB or ODBC error: [Expression.Error] The field ‘datasourceId’ of the record wasn’t found..
‘.

Any help is greatly appreciated.

Mayank

Hi Miguel, Thanks for the functionality and document over it. I just wanted to know, If we want to use the Activity Log for our product and want to show the usage for Page Level activities by user over a period of time, can we use this functionality and our data team can access the API to fetch the only meaningful data from it? Also, the structure & consistency of the data remains the same for all data files?

Amy

I have all the datasets except Event Activity Log function.?

Krishna

When I select the date range and click on invoke I am getting the error.

eventlogerror.PNG
Mayur

I am only able to get 30days activity log using this API. What must be done to get data of more than 30 days?

Ricky

Hi Miguel

I can not find anything information to on the log term management to ensure that the data is visible beyond the previous 30 days.

What we are trying to do is implement PBI in a business and we would like to track that usage over a period of time example:
Start date as today, would we put the end date as 31st Dec

Would using incremental refresh assist with archiving the older data but only refreshing latest data?

Ifthikhar Ahamed

can you please let me know how can I setup a auto refresh in power cloud

Javiera

Hi!, I’m encountering an issue with the Activity Events API. It’s not displaying all the activities I expect. I have a Python code that successfully iterates and retrieves information up to 30 days ago, storing it in a data frame. However, I have two domains, and it’s only displaying data for one of them. What could be causing this discrepancy?

Thanks!