Report on where Reports are being sent

I have something that I threw together here.  Unfortunately, it doesn't specifically say who is the email recipient.

However, this should let you at least have the listing to work from.  I've got an idea about a way to get email addresses, but it will be very ugly.  No ETA on that.

Here is a very ugly way to get the recipient info into a spreadsheet using powershell and the API. Read through the comments carefully, especially the ones at the very bottom of the script. If this is a one time item you need to do this will probably get the job done. 

cls
cd c:\powershellscripts #Adjust the path C:\PowershellScripts\ExportReports\ to fit where you run your scripts from
Write-host "Loading Solarwinds SWIS Plugin for Powershell"
Import-Module -Name SwisPowerShell
Add-PSSnapin SwisSnapin 

$swis = Connect-Swis -host YOURORIONSERVER -Trusted # Important!!!   Put the proper Orion servername in this line

#Adjust the path C:\PowershellScripts\ExportReports\ to fit where you run your scripts from

#removes old files - if you get an error when you run this it is because you still have the spreadsheet open. Close it and run it again

$filename = "C:\PowershellScripts\ExportReports\ReportActions.csv" #Adjust the path C:\PowershellScripts\ExportReports\ to fit where you run your scripts from
if (test-path $filename) {
Remove-Item $filename
write-host "file $filename removed"
}
else{
write-host "file $filename does not exist"
}



$query = "
   
SELECT TOP 1000 ReportJobID, Name, ActionsData
FROM Orion.ReportJobs
"

$rows = Get-SwisData -SwisConnection $swis -Query $query

$rows | Export-CSV -Path "C:\PowershellScripts\ExportReports\ReportActions.csv" -NoTypeInformation #C:\PowershellScripts\ExportReports\ to fit where you run your scripts from

#Follow these instructions exactly:
#Now go and open C:\PowershellScripts\ExportReports\ReportActions.csv using Excel
#Highlight all of column C only
#At the top click Data then Text to Columns
#Click Delimited then click Next
#Uncheck dilimeters that are checked
#Check Other and fill in with a colon :
#Click Finish

#Email to address should be shown in Column V

Here's a PowerShell version that should give you enough details to go off of.

$SwisConnection = Connect-Swis -Hostname orionserver.domain.local -Credential ( Get-Credential -Message "Enter your Orion Credentials" )
$Query = @"
SELECT [Reports].Title AS [Report Title]
     , CONCAT('/Orion/Report.aspx?ReportID=', [Reports].ReportID) AS [DetailsUrl]
     , CONCAT('<a href="', '/Orion/Report.aspx?ReportID=', [Reports].ReportID, '"', 'target="_blank">', [Reports].Title, '</a>') AS [Title_Html]
--     , [Reports].ReportID
--     , [JobDefinitions].ReportJobID
--     , [JobDefinitions].ReportID
--     , [Jobs].ReportJobID
     , [Jobs].Name AS [Job Name]
-- FrequencyTitle contains the same data as the Frequencies Display Name, so omitting
--     , [Jobs].FrequencyTitle AS [Frequency Name (Job)]
-- SchedulesData, ActionsData, and ReportsData are all JSON formatted and cannot be parsed via SWQL.
--     , [Jobs].SchedulesData
     , [Jobs].ActionsData
--     , [Jobs].ReportsData
-- UrlData does not apply to report schedules - only alerts (AFAIK)
--     , [Jobs].UrlData
     , [Jobs].ActionTitles AS [Action]
-- ReportTitles would be usefule if I wanted to "source" from Jobs, but this is a query grouped by the Report title
--     , [Jobs].ReportTitles
     , [Jobs].Enabled
     , CASE
         WHEN [Jobs].Enabled = 'TRUE' THEN '<img src="/Orion/images/StatusIcons/Small-Up.gif" alt="Enabled" />Enabled'
         ELSE '<img src="/Orion/images/StatusIcons/Small-Down.gif" alt="Disabled" />Disabled'
       END AS [Enabled Status]
--     , [Frequencies].FrequencyID
     , [Frequencies].DisplayName AS [Frequency Name]
-- If you can read CRON, this makes a bunch of sense, keeping it here for those who can
     , [Frequencies].CronExpression
-- Duration does not apply to report schedules - only alerts (AFAIK)
--     , [Frequencies].Duration
     , [Frequencies].StartTime AS [Job Start]
     , [Frequencies].EndTime AS [Job End]
-- EnabledDuringTimePeriod does not apply to report schedules - only alerts (AFAIK)
--     , [Frequencies].EnabledDuringTimePeriod
     , [Frequencies].CronExpressionTimeZoneInfo AS [Time Zone]
FROM Orion.Report AS [Reports]
LEFT JOIN Orion.ReportJobDefinitions AS [JobDefinitions]
  ON [Reports].ReportID = [JobDefinitions].ReportID
LEFT JOIN Orion.ReportJobs AS [Jobs]
  ON [JobDefinitions].ReportJobID = [Jobs].ReportJobID
 --AND [Jobs].Enabled = 'TRUE'
LEFT JOIN Orion.ReportSchedules AS [Schedules]
  ON [Jobs].ReportJobID = [Schedules].ReportJobID
LEFT JOIN Orion.Frequencies AS [Frequencies]
  ON [Schedules].FrequencyID = [Frequencies].FrequencyID
-- To show only reports with schedules enabled, uncomment the below line
WHERE [Jobs].Enabled = 'TRUE'
ORDER BY [Reports].Title
"@

$Reports = Get-SwisData -SwisConnection $SwisConnection -Query $Query
$Reports | Add-Member -MemberType ScriptProperty -Name "Action Description" -Value { ( $this.ActionsData | ConvertFrom-Json | Select-Object -ExpandProperty Description ).Replace("<br/>", ";`n") } -Force

$Reports | Select-Object -Property 'Report Title', 'Job Name', 'Enabled', 'Action', 'Action Description' | Export-Csv -Path ( Join-Path -Path ( [Environment]::GetFolderPath("Desktop") ) -ChildPath "ScheduledReports.csv" ) -NoTypeInformation -Confirm:$false -Force
Invoke-Item -Path ( Join-Path -Path ( [Environment]::GetFolderPath("Desktop") ) -ChildPath "ScheduledReports.csv" )

The output looks like this:

Thanks, ill give this a go and see where it lands me