Sure, here is the python script I have been running to get the information needed. This was working correctly till a few weeks ago, but I have been seeing the 429 error since yesterday. I run this every 2-3 months as needed.
######## Loading the required libraries
# import adal
import requests
# import os
import pandas as pd
from azure.identity import ClientSecretCredential
from time import sleep
tenant = 'xxx'
client = 'xxx'
client_secret = 'xxx'
api = 'https://analysis.windows.net/powerbi/api/.default'
# Generates the access token for the Service Principal
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
tenant_id = tenant,
client_id = client,
client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token
headers = {'Authorization': f'Bearer {access_token}'}
workspacetable_id = []
workspacetable_name = []
workspace = requests.get('https://api.powerbi.com/v1.0/myorg/admin/groups?%24top=5000&%24filter=state%20eq%20%27Active%27%20and%20type%20eq%20%27Workspace%27&%24expand=users', headers=headers)
if workspace.status_code == 200:
for workspace_info in workspace.json()['value']:
workspacetable_id.append(workspace_info['id'])
workspacetable_name.append(workspace_info['name'])
Workspace_Info = pd.DataFrame({
"Workspace ID":workspacetable_id,
"Workspace Name":workspacetable_name
})
dataset_id = []
dataset_name = []
dataset_url = []
dataset_owner = []
dataset_workspaceid = []
dataset_connection_details = []
dataset_datasource_id = []
dataset_gateway_id = []
datasets = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets', headers=headers)
for datasets_info in datasets.json()['value']:
datasouces = requests.get('https://api.powerbi.com/v1.0/myorg/admin/datasets/'+datasets_info['id']+'/datasources', headers=headers)
#Deleted dataset ids are sometimes retained by PowerBI service, when you try to get datascource information with the above api, it errors out. The below if statement is to mitigate that situation
if datasouces.status_code != 200:
continue
for datasources_info in datasouces.json()['value']:
try:
dataset_id.append(datasets_info['id'])
except:
dataset_id.append("")
try:
dataset_name.append(datasets_info['name'])
except:
dataset_name.append("")
try:
dataset_url.append(datasets_info['webUrl'])
except:
dataset_url.append("")
try:
dataset_owner.append(datasets_info['configuredBy'])
except:
dataset_owner.append("")
try:
dataset_workspaceid.append(datasets_info['workspaceId'])
except:
dataset_workspaceid.append("")
try:
dataset_connection_details.append(datasources_info['connectionDetails'])
except:
dataset_connection_details.append("")
try:
dataset_datasource_id.append(datasources_info['datasourceId'])
except:
dataset_datasource_id.append("")
try:
dataset_gateway_id.append(datasources_info['gatewayId'])
except:
dataset_gateway_id.append("")
Dataset_Info = pd.DataFrame({
"Dataset ID":dataset_id,
"Dataset Name": dataset_name,
"Dataset URL": dataset_url,
"Dataset Owner": dataset_owner,
"Workspace ID": dataset_workspaceid,
"Connection Details":dataset_connection_details,
"Datasource ID":dataset_datasource_id,
"Gateway ID":dataset_gateway_id
})
Dataset_Info_Complete = pd.merge(Dataset_Info, Workspace_Info, on='Workspace ID', how='left')
Dataset = Dataset_Info_Complete[['Workspace Name','Dataset Name','Dataset URL','Dataset Owner','Connection Details','Dataset ID','Workspace ID','Datasource ID','Gateway ID']]
Dataset.to_excel('E:/Dataset Info.xlsx', sheet_name='PBI Reports',index=False)
