Run a query against multiple databases by querying groups of instances listed in the CMS in parallel, then output a spreadsheet

Invoke-QueryInParallel.ps1

# Run a query against multiple databases 
# 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
 
$sqlLikeDbName = "specialDbNameToMatchGoesHere"
 
$dbNameMatches = $sqlInstances | Invoke-DbaQuery -database master -Query " select @@SERVERNAME as SQLInstance, name, state_desc, user_access_desc, is_read_only from sys.databases where database_id > 4 and state_desc = 'ONLINE' AND name LIKE `'%$($sqlLikeDbName)%`' ;" -Verbose -MessagesToOutput

# all SMO - no modules needed to import into the runspace
$results = Start-RSJob -Throttle 30 <# -ModulesToImport dbatools #> -InputObject $dbNameMatches <# -Verbose #> -ScriptBlock {
 
$ServerInstance = $_.SqlInstance
$database = $_.name
$smoSrv = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
# query from https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15
$sqlQuery = @"
SELECT name AS object_name   
  ,SCHEMA_NAME(schema_id) AS schema_name  
  ,type_desc  
  ,create_date  
  ,modify_date  
FROM sys.objects  
WHERE modify_date > GETDATE() - 90  
ORDER BY modify_date DESC
"@
$smoSrv.Databases[$database].ExecuteWithResults($sqlQuery).Tables[0]
} | Wait-RSJob -ShowProgress <#-Verbose#> | Receive-RSJob <#-Verbose#>
 
Write-Output "$(($results).count) rows returned"

$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"