Find all the SQL Agent Jobs by querying groups of instances listed in the CMS in parallel using runspaces, then output a spreadsheet

Get-AllSqlAgentJobsInParallel.ps1

# Find all the SQL Agent Jobs by querying groups 
# of instances listed in the CMS
# in parallel, then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob

# Use CMS server and optional group name to identify SQL instance names, remove duplicates
$sqlInstances = Get-DbaRegServer -SqlInstance MYCMS.my.domain -Group 'Prod1' | Select-Object -ExpandProperty ServerName | Sort-Object -Unique;
$sqlInstances += Get-DbaRegServer -SqlInstance MYCMS.my.domain -Group 'Stage1' | Select-Object -ExpandProperty ServerName | Sort-Object -Unique;

# No SQL Engine here even though entries are present in the CMS
$noSqlEnginePresent =($hereString = @"
SSAS77A.my.domain
SSAS77B.my.domain
SSRS45.my.domain
"@
).split("`n").TrimEnd("`r")

# I want to exclude CNAME records that point to Always On Availability Group listeners
$cNames = ($hereString = @"
PROD87.my.domain
PROD88.my.domain
PROD89.my.domain
"@
).split("`n").TrimEnd("`r")

# optional - Let's not include the SSAS and SSRS entries explicitly named above
$sqlInstances = $sqlInstances | where {$_ -NotIn $noSqlEnginePresent}
$sqlInstances = $sqlInstances | where {$_ -NotIn $cNames}
#<# optional - remove DNS suffix #>$sqlInstances = $sqlInstances -replace '.my.domain', ''

# How many entries are left to run against?
Write-Output "`$sqlInstances: $($sqlInstances.count)"

                               # 15 concurrent runspaces below will run in parallel
$results = Start-RSJob -Throttle 15 -ModulesToImport dbatools -InputObject $sqlInstances <# -Verbose #> -ScriptBlock { 
        # Try to connect for 7 seconds and then move on
   if ( ((Connect-DbaInstance -SqlInstance $_ -ConnectTimeout 7).Status -EQ "Online") ) {       
         Get-DbaAgentJob -SqlInstance $_ 
}} | Wait-RSJob -ShowProgress <#-Verbose#> | Receive-RSJob <#-Verbose#>

Write-Output "$(($results).count) records exported"

$fileDescription = 'SQL Agent Jobs in Prod1, Stage1'
$filenameExcel = ("\\fileserver47.my.domain\dbasql\xlsx\" + [string](Get-Date -format "yyyy-MM-dd__HHmmss") + "_" + $fileDescription + ".xlsx" ); #assign $filename variable
 
$paramHash = @{
    Path              = $filenameExcel
    WorksheetName     = $fileDescription
    TableName         = $fileDescription
    TableStyle        = 'Medium27' # because I like blue
    AutoSize          = $True
}
 
$results | Export-Excel @paramHash -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors # exclude certain properties from creating columns
Write-Output "$filenameExcel exported"