Skip to main content Skip to footer

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

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!

Categories:

21 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?

Miguel Escobar

Hey! I believe that quote is correct. It was taken from the official documentation right ?
The API does have that limitation, but I circumvent that limitation with some [M]agic at the custom connector level.
Let me know what you think of the connector !

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?

Miguel Escobar

Hey!
Yes – you can schedule a refresh of a Power BI Custom Connector in the Service.
You can find a blog post that I did about that here:
https://www.poweredsolutions.co/2018/04/23/refreshing-a-power-bi-custom-connector-in-the-cloud/

In terms of requirements that are inherited from the API, I highly recommend that you check out the official Power BI REST API documentation and also the official blog post about this new endpoint from Microsoft here:

https://powerbi.microsoft.com/en-us/blog/the-power-bi-activity-log-makes-it-easy-to-download-activity-data-for-custom-usage-reporting/

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?

Miguel Escobar

Hey! Are the rest of the objects inside the connector working for you? Is this the only function that’s giving you trouble ?

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?

Miguel Escobar

The best way would be to use a tool like fiddler to figure out what type of response you’re getting from the API. Do other tools like power shell work for you ? Or do they give you the same error?

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.

Miguel Escobar

Hey!
Thanks for the kind words. I highly suggest that you checkout the past issues in the GitHub repo. I believe there have been a few others that have had this issue regarding missing permissions for the authenticated user.

There’s also a few other collaborators that have added amazing code to the connector and you’ll see them in the repo

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.

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.

Miguel Escobar

Hey!
You can check out these two articles for further reference on the Event Activity Log function and how you can set up incremental refresh for a query in Power BI:
https://www.poweredsolutions.co/2020/01/18/power-bi-event-activity-log-api-easy-way-to-get-it/
https://www.poweredsolutions.co/2018/05/08/first-thoughts-on-power-bi-incremental-refresh/

It’s also a good idea for you to check out the official article from Microsoft on Incremental refresh:
https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh

Best!

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

Miguel Escobar

The response that you get from the REST API doesn’t have a completely defined schema. It’s basically all of the positive signals done in a particular period all sent to you. What the error is telling you is that there’s an “id” column that simply doesn’t exist within a particular timeperiod. This means that sometimes you might get A LOT of fields, and some others you might not get any fields because you didn’t have any logged event signals.

You’d need to implement your own standard schema and make it error proof so you don’t get these sort of issues. If you have Power BI Premium or PPU, you can connect to the dataset and check which of the partitions is having this issue as well.

Hope this helps!

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

Miguel Escobar

Hey! That’s difficult to say. My suggestion would be for you to trace the calls that Power BI Desktop is making with the custom connector and see if the issue is with Power BI not making the calls to the API or perhaps that it might take a long time because your tenant has a lot of data to be queried.
Can’t say that I’ve had this issue, but def try troubleshooting using the suggestions above. My tool of choice for these sort of situations is Fiddler.

Best!

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.