Power BI Inventory using Power BI REST API

ByHariharan Rajendran

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

  1. List of Workspaces
  2. List of Datasets
  3. List of Reports
  4. List of Dashboards
  5. List of Tiles
  6. List of Data Gateways
  7. List of Data sources on the gateway
  8. List of users and their access.

 

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

  1. Register an app and get the Client ID and Use it on the below script

 


# Parameters - fill these in before running the script!

# ======================================================

 

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

 

$clientId = "xxxxxx-xxxx-xxx-xxx-xxx"

 # End Parameters =======================================

 function GetAuthToken

{

    if(-not (Get-Module AzureRm.Profile)) {

      Import-Module AzureRm.Profile

    }

     $redirectUri = "urn:ietf:wg:oauth:2.0:oob"

     $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

     $authority = "https://login.microsoftonline.com/common/oauth2/authorize";

     $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList$authority

     $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

     return $authResult

}

 function get_groups_path($group_id) {

    if ($group_id -eq "me") {

        return "myorg"

    } else {

        return "myorg/groups/$group_ID"

    }

}

 # PART 1: Authentication

# ==================================================================

$token = GetAuthToken

 # Building Rest API header with authorization token

$auth_header = @{

   'Content-Type'='application/json'

   'Authorization'=$token.CreateAuthorizationHeader()

}

 # ==================================================================

# Get the list of groups

$uri = "https://api.powerbi.com/v1.0/myorg/groups/"

$all_groups = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\Allgroups.csv")

$all_groups | Export-csv $filePath

 # ==================================================================

# Get the list of gateways

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\AllGateways.csv")

$all_gateways | Export-csv $filePath

 # ==================================================================

# Get the list of gateways data sources

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

$all_gateways_id=$all_gateways.id

 foreach ($gateway in $all_gateways_id)

{

     $uri = "https://api.powerbi.com/v1.0/myorg/gateways/$gateway/datasources"

   $all_gateway_DS = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

  }

$filePath = ("C:\Users\Hari\Documents\AllGatewaysDS.csv")

$all_gateway_DS | Export-csv $filePath

 # ==================================================================

# Get the list of datasets from Groups

$all_datasets=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $datasets = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_datasets+=$datasets

  }

$filePath = ("C:\Users\Hari\Documents\alldatasets.csv")

$all_datasets| Export-csv $filePath

 # ==================================================================

# Get the list of reports from Groups

$all_reports=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/reports"

   $reports = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_reports+=$reports

  }

$filePath = ("C:\Users\Hari\Documents\allreports.csv")

$all_reports| Export-csv $filePath

 # ==================================================================

# Get the list of dashboards from Groups

 $output_dashboard = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   Group_Id = $group.id

   GroupName = $group.name

   displayName = ($all_dashboards.displayName -join ',')

     } | Select-Object Group_Id,GroupName, DisplayName

     }

$filePath = ("C:\Users\Hari\Documents\alldashboards.csv")

$output_dashboard| Export-csv $filePath

 # ==================================================================

# Get the list of tiles

$all_tiles=@()

foreach ($group in $all_groups)

   $group_id = $group.id

    $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   foreach ($dashboard in $all_dashboards)

   {

     $dashboard_id = $dashboard.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards/$dashboard_id/tiles"

   $tiles = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   }

   $all_tiles+=$tiles

  }

$filePath = ("C:\Users\Hari\Documents\alltiles.csv")

$all_tiles| Export-csv $filePath

 # ==================================================================

# Get the list of users from Groups

 $output = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/users"

   $all_group_user = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   GroupName = $group.name

   Access = ($all_group_user.groupUserAccessRight -join ',')

   DisplayName = ($all_group_user.displayName -join ',')

   PricipalType = ($all_group_user.principalType -join ',')

   } | Select-Object GroupName,Access, DisplayName, PricipalType

     }

    

$filePath = ("C:\Users\Hari\Documents\AllUsers.csv")

$output | Export-csv $filePath

 ## ==================================================================

## Get the list of datasets from Groups with Group Names

 

$alldatasets_GP = foreach ($group in $all_groups)

   $group_id = $group.id

  $group_name = $group.name

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $alldatasetsGP = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

       New-Object -Typename psobject -Property @{

   GroupId = $group.id

   GroupName = $group.name

   Dataset_Id = ($alldatasetsGP.id -join ',')

   DisplayName = ($alldatasetsGP.name -join ',')

     } | Select-Object GroupName,Dataset_Id, DisplayName

 }

$filePath = ("C:\Users\Hari\Documents\alldatasets_Groups.csv")

$alldatasets_GP | Export-csv $filePath

[/]

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!