Forum Discussion

alexyin1053's avatar
alexyin1053
Helper I
1 year ago
Solved

Get SQL Server Data from Power BI Report Server's Rest API

Dear all,   Currenly we're running on premise power bi report server, while we would like to get one of SQL Server's  table([ReportServer].[dbo].[Users]) by using Power BI Report Server's Rest API....
@NeedHelp
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, alexyin1053 

    Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:

    key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn

    key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn

    import requests
from requests_ntlm import HttpNtlmAuth

url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# replace your username and pwd
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
    newdict = {"id": item["Id"],"ReportName":item["Name"]}
    newlist.append(newdict)
for i in range(len(newlist)):
    url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
    response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
    policies = response1.json()["Policies"]
    newlist[i]["Policies"] = policies
    

print(newlist)

    You should be able to get a dictionary like this (which contains a list of user information accessible for each report):

    You can then use this dictionary for analysis or import into Power BI.

     

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you rohit1991 

Hi, alexyin1053 

The API can obtain the user list information of the report server. However, some fields are owned in SQL Server, and they are not within the scope of API acquisition, so I will take you through how to obtain it and explain which fields cannot be obtained.

The following is an example to obtain the list of users at the server level:

Press F12:

Sample:

http://YourServerIP/Reports/api/v2.0/System/Policies

Using python write a script:

import requests
from requests_ntlm import HttpNtlmAuth

url = "http://YourServerIp/Reports/api/v2.0/System/Policies"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# Replace with your username and password
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
print(response.json())

Here are the results:

Others are similar. Of course, it is still recommended that you use ssms to connect to your database and use the following SQL statement to query the entire table:

SELECT TOP (1000) [UserID]
      ,[Sid]
      ,[UserType]
      ,[AuthType]
      ,[UserName]
      ,[ServiceToken]
      ,[Setting]
      ,[ModifiedDate]
  FROM [ReportServer].[dbo].[Users]

 

 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

  • alexyin1053's avatar
    alexyin1053
    Helper I
    1 year ago

    Hi Anonymous  ,

     

    Thanks for the reply, and sorry I didn't described our goal very clearly.

    Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
    SSMS Query SQL code:

     

    SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

     


    Ideally, we would like to retrieve the upper information directly through the API of the AP Server so that the data is always up to date.


    Besides, we also have access to the SQL database, so querying directly to the SQL Server is also a viable option.

    Let me know if more information is needed!

    Thanks!

     

    Best Regards,

    Alex

  • alexyin1053's avatar
    alexyin1053
    Helper I
    1 year ago

    Hi Anonymous  ,

     

    Thanks for the reply, and sorry I didn't described our goal very clearly.

    Our goal is to display the information on another service website. The information is about all the dashboards currently on the ReportServer, along with the people who have browsing permissions.
    SSMS Query SQL code:

     

    SELECT DISTINCT U.UserName, C.Name as ObjectName, C.Path, 
       C.Description, MU.UserName As LatestModifiedByUserName
  FROM [ReportServer].[dbo].[Users] U
       join [ReportServer].[dbo].[PolicyUserRole] PUR on U.UserID = PUR.UserID
       join [ReportServer].[dbo].[Policies] P on P.PolicyID = PUR.PolicyID
       join [ReportServer].[dbo].[Catalog] C on C.PolicyID = P.PolicyID
       join [ReportServer].[dbo].[Users] MU on C.ModifiedByID = MU.UserID
 Where C.Type = 13 --Filter Type="PBIReport“;

     


    Ideally, we would like to retrieve the upper information directly through the API of the AP Server so that the data is always up to date.


    Besides, we also have access to the SQL database, so querying directly to the SQL Server is also a viable option.

    Let me know if more information is needed!

    Thanks!

     

    Best Regards,

    Alex

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, alexyin1053 

      Your clarification has helped me better understand your needs. You expect to get a list of users with access to each Power BI report. This is available through the API. I wrote a python code:

      key api : Power BI Reports - Get Power BI Report Policies - REST API (Power bi report) | Microsoft Learn

      key api: Power BI Reports - Get Power BI Reports - REST API (Power bi report) | Microsoft Learn

      import requests
from requests_ntlm import HttpNtlmAuth

url = "http://Yourserver/Reports/api/v2.0/PowerBIReports"
headers = {
    "accept": "application/json, text/plain, */*",
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "connection": "keep-alive",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/132.0.0.0 Safari/537.36 Edg/132.0.0.0"
}

# replace your username and pwd
username = ""
password = ""

response = requests.get(url, headers=headers, auth=HttpNtlmAuth(username, password))

print(response.status_code)
reportlist = response.json()["value"]
newlist = []
newdict = {}
for item in reportlist:
    newdict = {"id": item["Id"],"ReportName":item["Name"]}
    newlist.append(newdict)
for i in range(len(newlist)):
    url2 = f"http://YourServer/Reports/api/v2.0/PowerBIReports({newlist[i]["id"]})/Policies"
    response1 = requests.get(url2, headers=headers, auth=HttpNtlmAuth(username, password))
    policies = response1.json()["Policies"]
    newlist[i]["Policies"] = policies
    

print(newlist)

      You should be able to get a dictionary like this (which contains a list of user information accessible for each report):

      You can then use this dictionary for analysis or import into Power BI.

       

       

      Best Regards

      Jianpeng Li

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

       

       

      • alexyin1053's avatar
        alexyin1053
        Helper I
        1 year ago

        Hi Anonymous 

        This do solved our problem and also letting us know the limitations of PBIRS' API!

        Thanks a lot!

         

        Best Regards,

        Alex