How to use pscutomobject to list Name and path for each report to export to csv?

[cataster]

I wrote the following script that fetches all reports from the report server, and identified the ID’s.

# 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}" -f $_.Id, $_.Name)
            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("https://sql-dev02.domain.com/Reports"))

foreach($reportID in $reportIDs) {
    (getDataSources "https://sql-dev02.domain.com/Reports" $reportID | % {
        return $_.DataModelDataSource.Username
    })
}

However, in the foreach, im only returning the username field. I would like to return some more information, possibly in a csv format, that lists the report name, path, as well as the username thats currently outputted for each report.

How do i achieve the following result?

currently im just getting output like this:

Srvc_Account2

Srvc_Account3

etc…

I thought of returning the Name in the listReports function like so return $.Id, $.Name, but i am stuck because right now im using this return specifically for $reportIDs array…so not sure how im going to be able to utilize/loop through that for the name as well…

I have and love using pscutomobject, but im just not sure in the context of functions like what ive done here with listReports how to implement it…because idk how to return it and then call it properly in $reportIDs = @(listReports(“https://sql-dev02.domain.com/Reports”))

foreach($reportID in $reportIDs)

 

[dicey]

I would like to return some more information, possibly in a csv format, that lists the report name, path, as well as the username…

I’ll focus on this and create a collection object in a format similar to the screenshot, and output the collection to a CSV file. The source data is dummy data stored in a CSV file.

Background

• I prefer the encapsulation provided by PowerShell classes and using class methods in place of functions; and I generally wrap them in a single cmdlet (not shown).
  • This “blueprinting” can reduce coding time spent across similar items and actions.
• I rarely use PSCustomObject, New-Object, and avoid the use of the += assignment operator.
  • I’m even weening off of [DataTable] where it’s not required.
• I prefer [ArrayList] over other collection types, but will use [Dictionary] in specific cases (e.g., generating hexadecimal key values that incorporate multiple datetimes).
• I don’t use Name attributes as unique identifiers because of ISO/IEE metadata standards.

Remarks

• Workspace is used in these examples to show string splitting.
  • It could be substituted for another node or pattern (e.g., PowerBIReports(<id>)).
• Some prior knowledge of the source data structure is assumed.
  • The string piece where Workspace exists was known, as was the URL path structure.
  • I did specify my own headers on the raw “scraped” data for fun.
• _id is something that I stole shamelessly from BSON. You could name the property Id or ReportID.
• I did not use the last piece in the split URL for the report name because of metadata vocab stuff.
  • You could have a class property FileName defined by $report.ParseURL($i.ReportFullPath)[-1] --handy if the file extension is included in the URL.

Source data

Script

using namespace System.Collections;
using namespace System.Text.RegularExpressions;

Class Report
{
    [guid] $_id;
    [string] $FullName;
    [string] $Name;
    [string] $UserName;
    [string] $Workspace;

    [string[]]ParseURL([string] $uri)
    {
        return [Regex]::Split( [Regex]::Replace($uri,"^(https?:|)\//",''),'/');
    }
}

Class Reports
{
    [ArrayList] $Report;

    Reports()
    {
        $this.Report = [ArrayList]::new();
    }

    [void]AddItem([Report]$item)
    {
        $this.Report.Add($item);
    }
}

$reportCollection = [Reports]::new();
$reportList = Import-Csv -LiteralPath "A:\reports.csv" -Header "Id","ReportName","ReportFullPath","DatasourceUser";

foreach($i in $reportList)
{
    $report = [Report]::new();
    $report._id = $i.Id;
    $report.FullName = $i.ReportFullPath;
    $report.Name = $i.ReportName;
    $report.UserName = $i.DatasourceUser;
    $report.Workspace = $report.ParseURL($i.ReportFullPath)[3];
    $reportCollection.AddItem($report);
}

$reportCollection.Report | Export-Csv -Path A:\reports_new.csv -NoTypeInformation;
# demos
ii A:\reports_new.csv;
$reportCollection.Report[-1];
# $reportCollection.Report[-1].Workspace;  # calls an individual property in the last instance;

reports_new.csv

Return Last instance of Report in Reports

$reportCollection.Report[-1];