Skip to content

Query Power BI activity events with Synapse Serverless SQL

This is a natural extension to my post about how to query the Power BI tenant metadata.

The JSON data output from the “Get Activity Events” REST API (Power BI Audit Log) is much more straight forward, but also comes with a caveat. The documentation of the “Activity Event Entities” is not available anywhere and it’s continuously extended with new events as Power BI is evolving. I have done my best to include as many as possible, but would love to get feedback, so I can include even more. My list contains 94 and is based on 3 years of data from 3 tenants with a lot of traffic. I also did my best to set the datatypes, but this can probably also be done better.

And talking about the datatypes, then some of the returned entities is in objects and arrays. The objects is straight forward as you can “dot” your way to the values. As with this example with the Aggregated Workspace Information:

But the arrays is much more difficult, as they will “explode” the output and generate multiple lines of a given event. In this cases I have chosen to just output it as a JSON array like with the Subscription Schedule:

If your interested in these kind of data, then I will recommend to make a specific query to only output this. I might make a follow-up post targeting this, as there are a couple of interesting use cases. For instance the SharingAction or the FolderAccessRequests. With the later I have made an example of outputting the first entry in the array. Most cases only include one, but I have also seen cases with multiple – so be careful with this approach.

Also the naming can be confusing. Folder? I believe it’s either a workspace or an App, but I can be wrong. It’s futureproof, as we will be able to create folders in workspaces?

Another thing to be aware of is that only a few of the entities is common for all events and you will then see a lot of (NULL) values. The common ones are Activity, CreationTime, ClientIP, Operation, UserId, Workload etc.

The scripts is available in my GitHub repository: https://github.com/justBlindbaek/PowerBIMonitor/blob/main/ServerlessSQL/raw/ActivityEvents.sql

I would love to receive your feedback. Can you use this query? Can I do something better? Include more entities? Anything – please ping me!

Leave a Reply

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