Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I had obtained this "code" from the BI Elite team via a very cool Youtube Series on how to build a Power BI Usage Repot.
This was working an jsut stopped some months ago.
(** is there a way to upload a file to this forum? I can upload a PBIX file that has all the code that then would just need to be updated with specific app id/client secret and username/password credentials **)
I am having trouble executing the Power BI admin REST APIs -
https://api.powerbi.com/v1.0/myorg/admin/activityevents
https://api.powerbi.com/v1.0/myorg/admin/admin/groups
I am able to get an access token but when I then call the above REST APIs I get an error:
Expression.Error: Access to the resource is forbidden.
Below is the "code" i use. This used to work ... and to get around this error I go to the Power BI Rest API pages and "try" the get activity events which asks me to login and then provides a bearer token. I plug that token into my code rather than getting a token via the function i created ...
The code in the Activity Table basically calls teh Get Activity Function and passes the returned value from the Get Access Token Function. I know the Get access token function is returning a Token but seems that token does not work to call the Get Activity endpoint.
Solved! Go to Solution.
I was able to solve the problem.
1) I setup the Azure APP to be a member of the security group that is configured in the PBI Admin Console to have access to the REST APIs
2) I have a PBI file that can go and get the last 30 days of activity, but as is mentioned here that was always just a rolling 30 day window. IF that works for you then that is an option for sure..
3) I edited the "GET TOKEN" function to the below: ..
#Import necessary libraries
import msal
import requests
import json
import pandas as pd
from datetime import date, timedelta
import snowflake.connector
import sqlalchemy
# Function to insert a row into the Snowflake table
def insert_row(row):
sql_insert = f"INSERT INTO DATABASE.SCHEMA.TABLE(Id, RecordType, CreationTime, Operation, OrganizationId, UserType, UserKey, Workload, UserId, ClientIP, UserAgent, Activity, IsSuccess, RequestId, ActivityId, ItemName, WorkSpaceName, DatasetName, ReportName, WorkspaceId, ObjectId, DatasetId, ReportId, ReportType, DistributionMethod, ConsumptionMethod) VALUES ('{row['Id']}','{row['RecordType']}','{row['CreationTime']}','{row['Operation']}','{row['OrganizationId']}','{row['UserType']}','{row['UserKey']}','{row['Workload']}','{row['UserId']}','{row['ClientIP']}','{row['UserAgent']}','{row['Activity']}','{row['IsSuccess']}','{row['RequestId']}','{row['ActivityId']}','{row['ItemName']}','{row['WorkSpaceName']}','{row['DatasetName']}','{row['ReportName']}','{row['WorkspaceId']}','{row['ObjectId']}','{row['DatasetId']}','{row['ReportId']}','{row['ReportType']}','{row['DistributionMethod']}','{row['ConsumptionMethod']}')"
cursor.execute(sql_insert)
#Set SnowFlake parameters and create Connection
con = snowflake.connector.connect(
account="SNOWFLAKE ACCOUNT",
user= "USER",
password= "PASSWORD",
warehouse= "WAREHOUSE",
database= "DATABASE",
schema= "SCHEMA"
)
#Get yesterdays date and convert to string
activityDate = date.today() - timedelta(days=1)
activityDate = activityDate.strftime("%Y-%m-%d")
# Replace with your Azure AD app details
client_id = 'CLIENT_ID'
client_secret = 'CLIENT_SECRET'
tenant_id = 'TENNANT_ID'
authority_url = "https://login.microsoftonline.com/YOURDOMAIN"
scope = ["https://analysis.windows.net/powerbi/api/.default"]
#Set Power BI REST API to get Activities for today
url = "https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='" + activityDate + "T00:00:00'&endDateTime='" + activityDate + "T23:59:59'"
#Use MSAL to grab token
app = msal.ConfidentialClientApplication(client_id, authority=authority_url, client_credential=client_secret)
result = app.acquire_token_for_client(scopes=scope)
#Get latest Power BI Activities
if 'access_token' in result:
access_token = result['access_token']
header = {'Content-Type':'application/json', 'Authorization':f'Bearer {access_token}'}
api_call = requests.get(url=url, headers=header)
#Specify empty Dataframe with all columns
column_names = ['Id', 'RecordType', 'CreationTime', 'Operation', 'OrganizationId', 'UserType', 'UserKey', 'Workload', 'UserId', 'ClientIP', 'UserAgent', 'Activity', 'IsSuccess', 'RequestId', 'ActivityId', 'ItemName', 'WorkSpaceName', 'DatasetName', 'ReportName', 'WorkspaceId', 'ObjectId', 'DatasetId', 'ReportId', 'ReportType', 'DistributionMethod', 'ConsumptionMethod']
df = pd.DataFrame(columns=column_names)
#Set continuation URL
contUrl = api_call.json()['continuationUri']
#Get all Activities for first hour, save to dataframe (df1) and append to empty created df
result = api_call.json()['activityEventEntities']
df1 = pd.DataFrame(result)
pd.concat([df, df1])
#Call Continuation URL as long as results get one back to get all activities through the day
while contUrl is not None:
api_call_cont = requests.get(url=contUrl, headers=header)
contUrl = api_call_cont.json()['continuationUri']
result = api_call_cont.json()['activityEventEntities']
df2 = pd.DataFrame(result)
df = pd.concat([df, df2])
#Set Cursor for Snowflake
cursor = con.cursor()
# Apply the function to each row of the DataFrame
df.apply(insert_row, axis=1)
# Commit the transaction to save the changes
con.commit()
con.close()
I was able to solve the problem.
1) I setup the Azure APP to be a member of the security group that is configured in the PBI Admin Console to have access to the REST APIs
2) I have a PBI file that can go and get the last 30 days of activity, but as is mentioned here that was always just a rolling 30 day window. IF that works for you then that is an option for sure..
3) I edited the "GET TOKEN" function to the below: ..
#Import necessary libraries
import msal
import requests
import json
import pandas as pd
from datetime import date, timedelta
import snowflake.connector
import sqlalchemy
# Function to insert a row into the Snowflake table
def insert_row(row):
sql_insert = f"INSERT INTO DATABASE.SCHEMA.TABLE(Id, RecordType, CreationTime, Operation, OrganizationId, UserType, UserKey, Workload, UserId, ClientIP, UserAgent, Activity, IsSuccess, RequestId, ActivityId, ItemName, WorkSpaceName, DatasetName, ReportName, WorkspaceId, ObjectId, DatasetId, ReportId, ReportType, DistributionMethod, ConsumptionMethod) VALUES ('{row['Id']}','{row['RecordType']}','{row['CreationTime']}','{row['Operation']}','{row['OrganizationId']}','{row['UserType']}','{row['UserKey']}','{row['Workload']}','{row['UserId']}','{row['ClientIP']}','{row['UserAgent']}','{row['Activity']}','{row['IsSuccess']}','{row['RequestId']}','{row['ActivityId']}','{row['ItemName']}','{row['WorkSpaceName']}','{row['DatasetName']}','{row['ReportName']}','{row['WorkspaceId']}','{row['ObjectId']}','{row['DatasetId']}','{row['ReportId']}','{row['ReportType']}','{row['DistributionMethod']}','{row['ConsumptionMethod']}')"
cursor.execute(sql_insert)
#Set SnowFlake parameters and create Connection
con = snowflake.connector.connect(
account="SNOWFLAKE ACCOUNT",
user= "USER",
password= "PASSWORD",
warehouse= "WAREHOUSE",
database= "DATABASE",
schema= "SCHEMA"
)
#Get yesterdays date and convert to string
activityDate = date.today() - timedelta(days=1)
activityDate = activityDate.strftime("%Y-%m-%d")
# Replace with your Azure AD app details
client_id = 'CLIENT_ID'
client_secret = 'CLIENT_SECRET'
tenant_id = 'TENNANT_ID'
authority_url = "https://login.microsoftonline.com/YOURDOMAIN"
scope = ["https://analysis.windows.net/powerbi/api/.default"]
#Set Power BI REST API to get Activities for today
url = "https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='" + activityDate + "T00:00:00'&endDateTime='" + activityDate + "T23:59:59'"
#Use MSAL to grab token
app = msal.ConfidentialClientApplication(client_id, authority=authority_url, client_credential=client_secret)
result = app.acquire_token_for_client(scopes=scope)
#Get latest Power BI Activities
if 'access_token' in result:
access_token = result['access_token']
header = {'Content-Type':'application/json', 'Authorization':f'Bearer {access_token}'}
api_call = requests.get(url=url, headers=header)
#Specify empty Dataframe with all columns
column_names = ['Id', 'RecordType', 'CreationTime', 'Operation', 'OrganizationId', 'UserType', 'UserKey', 'Workload', 'UserId', 'ClientIP', 'UserAgent', 'Activity', 'IsSuccess', 'RequestId', 'ActivityId', 'ItemName', 'WorkSpaceName', 'DatasetName', 'ReportName', 'WorkspaceId', 'ObjectId', 'DatasetId', 'ReportId', 'ReportType', 'DistributionMethod', 'ConsumptionMethod']
df = pd.DataFrame(columns=column_names)
#Set continuation URL
contUrl = api_call.json()['continuationUri']
#Get all Activities for first hour, save to dataframe (df1) and append to empty created df
result = api_call.json()['activityEventEntities']
df1 = pd.DataFrame(result)
pd.concat([df, df1])
#Call Continuation URL as long as results get one back to get all activities through the day
while contUrl is not None:
api_call_cont = requests.get(url=contUrl, headers=header)
contUrl = api_call_cont.json()['continuationUri']
result = api_call_cont.json()['activityEventEntities']
df2 = pd.DataFrame(result)
df = pd.concat([df, df2])
#Set Cursor for Snowflake
cursor = con.cursor()
# Apply the function to each row of the DataFrame
df.apply(insert_row, axis=1)
# Commit the transaction to save the changes
con.commit()
con.close()
That's not how you are supposed to consume the activity reports. Keep in mind they are ephemeral and will disappear after 30 days. You are supposed to download the JSON extracts daily (via PowerShell), store them in a central repository and maybe feed a dataflow from the files - or read them in to datasets directly.
Thanks for your reply
Yes I know that... we are working in parallel to build scripts to run a daily extrace and utilize our Snowflake instance to save. I put this "version" here as our first go around on this was to just get the last 30 days worth of Activity..
Your response does not help solve the problem I posted.
The essence of the problem is that while I can get an Access Token, but that token does not work as the bearer token when calling the Admin-Get Activity endpoint.. If I go to the Power BI Rest API page https://learn.microsoft.com/en-us/rest/api/power-bi/admin/get-activity-events and "try"
I can take the Bearer token that is provided and plug it in to my Power BI code and it all works..
So why does that bearer token work and not the one from my code at the top here?
This could be a problme with our Azure APP but hoping someone on here can comment if something has changed in the Authentication process / library to get a proper Access Token..
According to Chris Webb you can only make this work with custom connectors, not with regular Power Query script.
This used to work all by itself and I can make it work as I described by manually getting an Auth Token from the Power BI Rest API documentation pages by running it from there..
And even then, the same "logic" and endpoints need to be used in Python code to get activity data from Powe BI..
when did it stop working all by itself?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
38 | |
4 | |
4 | |
3 | |
3 |