Miguel Escobar Published January 18, 2020
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.
“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?
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 !
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?
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.thepoweruser.com/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/
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?
Hey! Are the rest of the objects inside the connector working for you? Is this the only function that’s giving you trouble ?
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?
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?
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.
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
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
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.
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
this is because your schema has fixed columnnames (specifically the ‘ExcludePersonalWorkspaces’ column) which doesn’t exist in the table when you finish doing the refresh of your query which ends up in this error. I highly encourage you to check your query and make sure that you’re creating a query that is capable of receiving a dynamic response where some columns might be present sometimes and not in other times because of how the activity is being logged at your tenant level.
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!!!
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.
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.thepoweruser.com/2020/01/18/power-bi-event-activity-log-api-easy-way-to-get-it/
https://www.thepoweruser.com/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!
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
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!
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!!
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
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!
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
Hey!
I think that the incremental refresh requires a datetime parameter in order for it to work.
Here’s an article that I wrote a couple of years ago:
https://www.thepoweruser.com/2018/05/08/first-thoughts-on-power-bi-incremental-refresh/
Thats it! That was the issue. Thank you. Appreciate your quick reply on this.
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?
For more information on how to create your own custom connectors folder, please visit the official documentation from Microsoft Power BI Custom Connectors.
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.
Hey!
I didn’t personally work on that component of the connector, so I wouldn’t be able to tell you exactly what could be causing it. Nevertheless, I highly encourage you to post your issue on the GitHub repo so that all contributors to that repo can provide their insights and help you. Maybe you’ve found a bug in the connector, or maybe there was a change with the API.
We use the GitHub repo to track the issues as well as for any sort of questions that anyone might have around the connector.
Best!
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?
Hey! I’m not 100% sure as I only enabled the function, but I’m not super deep into what type of events you can get from the endpoint.
From a purely conceptual standpoint, what the function does is just try to take the start and end date provided by the user and then one call for every date within that date range. The call looks like this:
https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='"& _{0} &"'&endDateTime='" & _{1}&"'"You can check the full code for that specific function from the link below to better understand what it does:
https://github.com/migueesc123/PowerBIRESTAPI/blob/6405c320938ef7705a46f3e22edb0d5a2f4ebdbe/Power%20BI%20API/Power%20BI%20API/PBIAPI.pq#L137-L179
I have all the datasets except Event Activity Log function.?
Hey!
Sorry for the late reply. It should be in the folder called “Functions”. You can try checking the GitHub repo to get the latest version of the connector.
Best!
When I select the date range and click on invoke I am getting the error.
Hey! would you mind posting the issue on the GitHub repo, please?
If you can add some repro steps (step by step) that would be great! I just tried the function and it appears to be working as intended, so i’m not entirely sure how to repro that error. If you could also post your M code in that github issue, that would help a lot. It might be something to do with the way that you’re calling the function, but it’s difficult to tell without looking at the repro steps or your M code.
best!
I am only able to get 30days activity log using this API. What must be done to get data of more than 30 days?
Hey!
You’ll need to get in touch with the Microsoft team behind the Power BI REST API to get more information about it. I’m not aware of a way to retrieve data past 30 days using this REST API, but again I’m not the owner of the data source so I could’ve missed something when implementing things.
If you find any bugs or misses, please report them in the GitHub repo of the project.
Best!
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?
In principle, it should. It just depends on how you set up the incremental refresh and how you create the partitions, but I’ve heard of other folks who have implemented it in the past with great success.
can you please let me know how can I setup a auto refresh in power cloud
Hey!
I’m not sure I follow the question. If you’re having issues trying to schedule a refresh of a particular dataset, I’d recommend reaching out to the Power BI support team by creating a support ticket.
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!
Hey! I’m not that familiar with the REST API. Sorry. I’d recommend posting this question in the Power BI Developer forum https://community.fabric.microsoft.com/t5/Developer/bd-p/Developer
Hi Miguel,
Thanks you for sharing this – it’s a great connector. I have one question pertaining to this,
“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!”
How did you set up the parameters here? This is where I am stuck. I would like to set up an incremental refresh and want the dates to be working dynamically where the report holds “historical” usage data and updates daily. Right now I can just add start and enddate manually – refresh and publish to service but I’d like to automate the load to be working dynamically with dates.
Thank you and looking forward to your reply.
Hey! What have you tried so far? you should be able to create the parameters and then just pass them inside of the function whilst using something like Date.From to transform them into simple dates that could be used by the function itself. Other than that, it should be pretty straightforward.