How can I determine which datasets are using a gateway connection?

I realize this thread is old, but we just finished a powershell script that builds an Excel workbook where we can link the gateway connections to dataset datasources. We still have to do a vlookup between the worksheets to find what is being used. It's not picking up connections for Paginated Reports, either.

You'll need to install the PowerBI and Excel modules first.

Install-Module -Name MicrosoftPowerBIMgmt

Install-Module -Name ImportExcel

Then run this. It might kick out some errors if you don't have access to a given workspace.

$scope = "Organization" #Individual
$filter = "(type eq 'Workspace') and (state eq 'Active')"

Connect-PowerBIServiceAccount

Get-PowerBIWorkspace -All -scope $scope -Filter $filter | 
	Select-Object -Property "Id","Name","IsReadOnly","IsOnDedicatedCapacity","CapacityId","Description","Type","State","IsOrphaned" | 
		Export-Excel -Path C:\Temp\PowerBI\PowerBI_WorkspaceDetails.xlsx -WorksheetName "Workspaces" -AutoSize

Get-PowerBIWorkspace -Scope $scope -All -Filter $filter | 
Foreach {
	$wsId = $_.Id;
	$wsName=$_.Name; 
	Get-PowerBIReport -WorkspaceId $wsId -Scope $scope | Select-Object -Property ID, Name, WebUrl, DatasetId | 
	Foreach { 
		[PSCustoMObject]@{
			'WSID' = $wsId; 
			'WSName' = $wsName; 
			'reportId' = $_.ID; 
			'reportName' = $_.Name; 
			'reportDataset' = $_.DatasetId; 
			'reportURL' = $_.WebUrl 
		} 
	} 
} | Export-Excel -Path C:\Temp\PowerBI\PowerBI_WorkspaceDetails.xlsx -WorksheetName "Reports" -AutoSize

Get-PowerBIWorkspace -Scope $scope -All -Filter $filter | 
Foreach {
	$wsId = $_.Id;
	$wsName=$_.Name; 
	Get-PowerBIDataset -Scope $scope -WorkspaceId $wsId | 
	Foreach {
		$dsID = $_.Id; 
		$dsName = $_.Name; 
		Get-PowerBIDatasource -DatasetId $dsID -Scope $scope | 
		Foreach { 
			[PSCustoMObject]@{
				'WSID' = $wsId; 
				'WSName' = $wsName; 
				'DSID' = $dsID; 
				'DSName' = $dsName; 
				'SrcId' = $_.DatasourceId; 
				'SrcName' = $_.Name; 
				'SrcType' = $_.DatasourceType; 
				'SrcConnectString' = $_.ConnectionString; 
				'SrcDtlServer' = $_.ConnectionDetails.server; 
				'SrcDtlDB' = $_.ConnectionDetails.database 
			} 
		}
	}
} | Export-Excel -Path C:\Temp\PowerBI\PowerBI_WorkspaceDetails.xlsx -WorksheetName "Datasources" -AutoSize

# Get gateways
$gateways = Invoke-PowerBIRestMethod -Url 'gateways' -Method Get | ConvertFrom-Json
$gateways.value | ForEach {
	$gwID = $_.id
	$datasources = Invoke-PowerBIRestMethod -Url "gateways/$($gwID)/datasources" -Method Get | ConvertFrom-Json
    $datasources.value | ForEach {
        $connDetails = $_.connectionDetails | ConvertFrom-Json

        [PSCustoMObject]@{
		    'datasourceId' = $_.id; 
		    'datasourceName' = $_.datasourceName; 
		    'datasourceType' = $_.datasourceType; 
		    'datasourceDtlServer' = $connDetails.server; 
		    'datasourceDtlDB' = $connDetails.database; 
		    'datasourceDtlPath' = $connDetails.path; 
		    'datasourceDtlExtType' = $connDetails.extensionDataSourceKind; 
		    'datasourceDtlExtPath' = $connDetails.extensionDataSourcePath; 
            'datasourceDtlODBCConnString' = $connDetails.connectionString;
		    'datasourceDtlURL' = $connDetails.url   
	    } 
    }
} | Export-Excel -Path C:\Temp\PowerBI\PowerBI_WorkspaceDetails.xlsx -WorksheetName "Gateway Datasources" -AutoSize