How to Access Specific Custom Reports Using PowerBI (via Spirion Reporting API)

Requirements

• PowerBI
• Spirion Reporting API clientID and secret
• Spirion Console hostname or URL
• ReportID of desired report (see the Custom KB article - "How To Get Report IDs for Spirion Custom Reports")

How to Set Up PowerBI

Procedure:

1. Open PowerBI
2. New>Blank Report
3. Click Transform data (Queries group on ribbon)
4. Right-click the queries list on the left side of the screen.
5. • New Query>Blank Query
     
     
      
5. Click Advanced Editor button (in Query section of the ribbon)
6. Replace the body with the text in the code block below: it automates the process of authenticating with the API to retrieve report data in bulk.
7. 1. Key components of the script:
   2. Authentication (OAuth 2.0): The GetAccessToken function uses the "Client Credentials" flow. It sends a ClientID and ClientSecret to Spirion's token endpoint (/oauth2/connect/token) to receive a temporary access token.
   3. Data Retrieval: It targets the LoadReportData endpoint to pull specific report information based on a ReportID.
   4. Pagination: The parameters PagesToLoad and RowsPerPage indicate the script is set up to handle large datasets by requesting data in multiple "pages".
      

   # Define API URL
   
   $apiUrl = "https://api.{yourconsole}-spirion.com/reporting/Report/Reports"
   
   # Define access token (Replace with actual token)
   
   $accessToken = "your token here"
   
   # Set headers
   
   $headers = @{
   
       "Authorization" = "Bearer $accessToken"
   
       "Content-Type"  = "application/json"
   }
   
   # Define output directory and file
   
   $outputDir = "C:\SpirionAPI"
   
   $outputFile = "$outputDir\Reports and IDs.csv"
   
   # Create directory if it doesn't exist
   if (!(Test-Path -Path $outputDir)) {
       New-Item -ItemType Directory -Path $outputDir -Force
   }
   
   # Make API request
   
   $response = Invoke-RestMethod -Uri $apiUrl -Method Get -Headers $headers
   
   # Check if response contains data
   
   if ($response) {
       # Prepare CSV content
       $csvData = @()
       foreach ($report in $response) {
           $csvData += [PSCustomObject]@{
               Title       = $report.title
               ID          = $report.id
               DateCreated = $report.dateCreated
           }
       }
   
   		# Export to CSV
   		$csvData | Export-Csv -Path $outputFile -NoTypeInformation -Force
       Write-Host "CSV file saved to: $outputFile"
       # Display data
       $csvData | Format-Table -AutoSize
   } else {
       Write-Host "No reports found."
   }

7. Modify the query parameters at the top of the query.
8. • TokenURL
   • ClientID
   • ReportAPIURL
   • ClientSecret
   • ReportID
   • PagesToLoad
   • RowsPerPage
8. Click Done (Button bottom right)
9. The data should be loaded, otherwise, use the "refresh all" button, and check the UI for errors by clicking each element in the Applied Steps section on the right side of the screen under the heading "Query Settings".
10. • The Applied Steps>Combined Data will provide the data preview.
      
      
      
10. Click Close & Apply (button top left)
11. Select your desired data filtering: