Fetching data sources from dataset using PowerShell script

[Anonymous]

Hi All,

Can anyone please provide the Powershell script to fetch the data sources from each and every dataset in Power BI service.

Thanks in advance!

[aj1973]

Hi @Anonymous 

Get-PowerBIDataset -Scope Organization |
Foreach {
$dsID = $_.Id;
$dsName = $_.Name;
Get-PowerBIDatasource -DatasetId $dsID -Scope Organization |
Foreach {
[PSCustoMObject]@{

'WSID' = $wsId;
'WSName' = $wsName;
'DSID' = $dsID;
'DSName' = $dsName;
'SrcId' = $_.DatasourceId;
'SrcName' = $_.Name;
'DatasourceType' = $_.DatasourceType;
'ConnectionString' = $_.ConnectionString;
'ConnectionDetails.server' = $_.ConnectionDetails.server;
'ConnectionDetails.database' = $_.ConnectionDetails.database;
'ConnectionDetails.Url' = $_.ConnectionDetails.Url;
'GatewayId' = $_.GatewayId

}
}
}| ConvertTo-Json | Out-File -FilePath "D:\Logs\Inventaire\Datasources\PowerBI_Datasources_$(Get-Date -Format yyyyMMdd).json" -Encoding utf8 -Force

[Anonymous]

Hi @aj1973 ,

Thanks for responding to me, As you suggested i ran the powershell but getting the below error. May i know why i am getting the below error.

Thanks in advance!

[aj1973]

Can't see the error

Are you Power BI Admin?

[Anonymous]

Below is the error message and yes i am the Power BI admin.

Get-PowerBIDatasource : Operation returned an invalid status code 'NotFound'
At line:6 char:1
+ Get-PowerBIDatasource -DatasetId $dsID -Scope Organization |
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIDatasource:GetPowerBIDatasource) [Get-PowerBIDatasource], HttpOper
ationException
+ FullyQualifiedErrorId : Operation returned an invalid status code 'NotFound',Microsoft.PowerBI.Commands.Data.GetPowerBIDatasou
rce

Thanks,

Nagendra Y

[aj1973 · Accepted solution]

It is saying that the command

you either re install the module

Or it could be also

Therefore you need a Service account or Power BI App Registration

[Anonymous]

Hello @aj1973 

As you suggested i reinstall the module again but same results

[aj1973]

You have an authantication problem. How do you connect to the service? are you using an App registration? eventhough you are Power Admin, are you allowed to use and read the REST APIs power bi? check the Admin portal to be allowed

[Anonymous]

I am using both App registration and my credentials but getting same results. App registration have Power BI admin access and added as an admin of all workspaces in premium capacity. Yes i allowed to use and read the REST APIs power bi below ias screenshot.

[aj1973]

When using the App Registration, is the private key installed in the machine/server that executes the script?

Can you please show me how do you connect and execute the script *.ps1?

The way you are connecting now, can you run just this API "Get-PowerBIDataset -Scope Organization" and tell me if it returns a result?

[Anonymous]

Using clientid and clientsecret we are connecting the power bi service and running script which is as follows:

$AppId = "Application ID"
$TenantId = "Tenant ID"
$ClientSecret = "Clientsecret"

#Create secure string & credential for application id and client secret
$PbiSecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText
$PbiCredential = New-Object Management.Automation.PSCredential($AppId, $PbiSecurePassword)

#Connect to the Power BI service
Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $PbiCredential

Get-PowerBIDataset -Scope Organization |
Foreach {
$dsID = $_.Id;
$dsName = $_.Name;
Get-PowerBIDatasource -DatasetId $dsID -Scope Organization |
Foreach {
[PSCustoMObject]@{

'WSID' = $wsId;
'WSName' = $wsName;
'DSID' = $dsID;
'DSName' = $dsName;
'SrcId' = $_.DatasourceId;
'SrcName' = $_.Name;
'DatasourceType' = $_.DatasourceType;
'ConnectionString' = $_.ConnectionString;
'ConnectionDetails.server' = $_.ConnectionDetails.server;
'ConnectionDetails.database' = $_.ConnectionDetails.database;
'ConnectionDetails.Url' = $_.ConnectionDetails.Url;
'GatewayId' = $_.GatewayId

}
}
}| ConvertTo-Json | Out-File -FilePath "D:\Logs\Inventaire\Datasources\PowerBI_Datasources_$(Get-Date -Format yyyyMMdd).json" -Encoding utf8 -Force

As you suggested i have run just API "Get-PowerBIDataset -Scope Organization" and getting results with out any issues.

[aj1973]

@Anonymous 

This is not the right way to access with App registration

It is like you are converting the App registration into a User with credentials and PSW, False.

This is the right way:

Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint 38DA4BED389A014E69A6E6D8AE56761E85F0DFA4 -ApplicationId b5fde143-722c-4e8d-8113-5b33a9291468

You need :

If this is not a good option for you to install CertificateThumbprint,(Recommended) then there is another way but little longer since you can call APIs(Your way) one by one.

Let me know.