This is the second part in my blog post series showing you how I created the Power BI Reports list.

In this blog post I am going to show you how I used PowerShell to get all the information of the App Names, reports and users that have permissions in the different Apps.

Users can get access to Power BI reports directly via the Share method, as well as via an App. I did this to ensure that I did not miss any reports that a user did have access to, and I could not show it!

The first part in the series can be found here: Which Power BI Reports do I have access to? – Part 1 – Using the power BI Scanner to get App Workspace data. – Data Analytics | FourMoo and Power BI

Pre-requisite

One thing that I did to make the refreshing of my reports easier is I had OneDrive sync application running locally on my PC/Server where I would download the JSON files.

The reason I did this is two-fold.

  • The first reason is it is a lot easier download files to a local disk drive.
  • Because I am using the OneDrive sync application this will automatically upload the files to OneDrive.
    • Later when I need to refresh my Power BI Report, I do not have to worry about a gateway connection to get the refresh working.

PowerShell file and explanation

I used PowerShell to connect to the Power BI Rest APIs so that I could download the data I needed.

The good thing is that this same PowerShell script could be used with Azure Runbooks or Azure Function Apps if required (You would need to change where the files are downloaded to)

Please note that you will need to have a Power BI Admin account in order to get all the App and App Users information.

Here is the script and I have commented in the script what each section is doing.

#Log into Power BI 
# You will be prompted to Log in
Login-PowerBI  
 
#Folder Location (My OneDrive Location)
$FolderLocation = 'C:\Users\Gilbert\fourmoo.com\Que PBI - Documents\APIs\App_AppUsers'

#Get all the details for all the Apps
$parsed  = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/apps" -Method Get | ConvertFrom-Json

$ActivityLogSchema = $parsed | Select-Object -ExpandProperty Value

$ActivityLogSchema | Export-Csv $FolderLocation\App_ID.CSV

# Get a list of APP ID into CSV

$CSV = Import-CSV $FolderLocation\App_ID.CSV

# Loop Through and get a list of users in each app
foreach($LINE in $CSV)

{
    # Get the AppID into a Variable
    $AppID = $LINE.id

    $AppID

    # Using the REST API get a list of App Users
    $AppUsers = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/admin/apps/$AppID/users" -Method Get 

    $AppUsers

    #Output the data into a JSON File
    $AppUsers | Out-File $FolderLocation\$AppID.JSON

    #Using the REST API to get a list of App Links for Reports
    $AppReports = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/admin/apps/$AppID/reports" -Method Get
    $AppReports

    #Outout App Reports into a JSON File
    $AppReports | Out-File $FolderLocation\$AppID.Reports.JSON

}

There are some details I would like to highlight

  • Line 3:
    • In some customers tenants I have a Power BI Account which does NOT have MFA and I securely store the details which I then use to log into Power BI.
    • This allows me to not have to manually log in each time to run the file.
  • Line 6
    • This is my OneDrive folder location.
    • You will need to change this to your folder location on your PC/Server
  • The rest of the PowerShell script will run as is without being modified.

Once the script has successfully run, I can then see the files on my local folder as shown below.

Likewise, I could see the files on OneDrive online

If I had to open one of the JSON files, I would see the contents. As shown below I can see a member, user, and Group

Summary

In this blog post I have shown you how to use a combination of PowerShell and the Power BI Rest API to download the App Name, reports, and users into the required files.

Thanks for reading and in the final part of the blog post series I will show you how I put it all together.

Any comments or suggestions are most welcome!