Why not replace lines 5 and 6 in the "listReports" function?
[PSCustomObject]@{
Id = $_.Id
Name = $_.Name
Path = $_.Path
}
Line 22 would then become:
(getDataSources "http://pbi.domain.com/reports" $reportID.Id | % {
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have the following script that fetches reports from a URI and displays the datasource(s) username. However, we'd like to get the name of the reports so we know what datasource username is associated with what report for tracking purposes.
listReports()
function parses each report and returns its ID return $_.Id
The report ID is a hash like
12b1657c-dc07-5b35-afc0-b0d6c9047860
The $_
object also has other meaningful properties, such as $_.Name
, and $_.Path
Current output of full script (from the last foreach loop that is) is a list of Datasource usernames (not the report IDs):
TKCSIMPORT
payroll
si_51932_p_00h
payrollpayroll
tkcsimport
which is what we want, but i also want to display the report name/paths associated with each of those usernames, e.g.
expected output:
DS Username Report Name
TKCSIMPORT Report1
payroll Report2
si_51932_p_00h Report3
payroll Report3payroll Report4
tkcsimport Report5
How do I do this?
I would try return $_.Id, $_.Name, $_.Path
in listReports()
but then the challenge is how to map the extra properties here
@(listReports("http://pbi.domain.gm.com/reports"))
and parse them in the last for loop to include in the output?
Full script:
# lists all the reports on the server that are available to your user account
Function listReports($baseURL) {
$reports = Invoke-RestMethod -UseDefaultCredentials -uri "$baseURL/api/v2.0/CatalogItems"
$reports.value | Where-Object {$_.Type -eq "PowerBIReport"} | foreach {
#Write-Host ("{0} {1} {2}" -f $_.Id, $_.Name, $_.Path)
return $_.Id
}
}
Function getDataSources($baseURL, $reportID) {
$sources = Invoke-RestMethod -UseDefaultCredentials -uri "$baseURL/api/v2.0/PowerBIReports($reportID)/DataSources"
if ($sources.value -is [array]) {
return $sources.value
} else {
return @($sources.value)
}
}
$reportIDs = @(listReports("http://pbi.domain.com/reports"))
foreach($reportID in $reportIDs) {
(getDataSources "http://pbi.domain.com/reports" $reportID | % {
return $_.DataModelDataSource.Username
})
}
Why not replace lines 5 and 6 in the "listReports" function?
[PSCustomObject]@{
Id = $_.Id
Name = $_.Name
Path = $_.Path
}
Line 22 would then become:
(getDataSources "http://pbi.domain.com/reports" $reportID.Id | % {
@Rich Matheisen
I implemented your code update and in order to print out the Name and path of the reports alongside the username as in the expected output, i created an array of a ps custom object. here is the working version for future visitors :)
Function listReports($baseURL) {
$reports = Invoke-RestMethod -UseDefaultCredentials -uri "$baseURL/api/v2.0/CatalogItems"
$reports.value | Where-Object {$_.Type -eq "PowerBIReport"} | foreach {
[PSCustomObject]@{
Id = $_.Id
Name = $_.Name
Path = $_.Path
}
}
}
Function getDataSources($baseURL, $reportID) {
$sources = Invoke-RestMethod -UseDefaultCredentials -uri "$baseURL/api/v2.0/PowerBIReports($reportID)/DataSources"
if ($sources.value -is [array]) {
return $sources.value
} else {
return @($sources.value)
}
}
$reportList = @(listReports($webPortalURL))
$ReportsDatasourceUsernames_pscoArray = @()
$loopCount = 1
foreach($report in $reportList) {
(getDataSources $webPortalURL $report.Id | % {
#return $_.DataModelDataSource.Username
$ReportsDatasourceUsernames_pscoArray += [PSCustomObject]@{
'#' = $loopCount
'Datasource Username' = $_.DataModelDataSource.Username
'Report Name' = $report.Name
'Report Path' = <#"=HYPERLINK(""$($webPortalURL + `"/powerbi`" + $($report.Path))"",""$($report.Name)"")"#> "<a href='$($webPortalURL + `"/powerbi`" + $($report.Path))'>$($report.Name)</a>"
#'Status' = "$($error[0])`r`n$($error[0].InvocationInfo.PositionMessage)`r`n$($error[0].ScriptStackTrace)"
}
})
$loopCount++
}