Every SQL Agent job on every server in one spreadsheet: a PowerShell and dbatools inventory

#Prerequisites

Install-Module dbatools -Scope CurrentUser
Set-DbatoolsInsecureConnection -SessionOnly    # only if your instances use self-signed certs; prefer trusted certs

The account running it needs read access to msdb on each instance (SQLAgentReaderRole is enough for job data).

#Server list

From a file, a CMS or your CMDB:

$servers = Get-Content .\servers.txt
# or: $servers = Get-DbaRegServer -SqlInstance CMS01 | Select-Object -ExpandProperty ServerName

#Collect

$results = foreach ($s in $servers) {
  try {
    Get-DbaAgentJob -SqlInstance $s -EnableException | ForEach-Object {
      [pscustomobject]@{
        Server        = $s
        Job           = $_.Name
        Category      = $_.Category
        Owner         = $_.OwnerLoginName
        Enabled       = $_.IsEnabled
        HasSchedule   = $_.HasSchedule
        LastRun       = $_.LastRunDate
        LastOutcome   = $_.LastRunOutcome
        NextRun       = $_.NextRunDate
        EmailOnFail   = ($_.EmailLevel -in 'OnFailure','Always')
        Operator      = $_.OperatorToEmail
        Steps         = $_.JobSteps.Count
        Subsystems    = ($_.JobSteps.SubSystem | Sort-Object -Unique) -join ','
        Description   = $_.Description
      }
    }
  }
  catch {
    [pscustomobject]@{ Server = $s; Job = "ERROR: $($_.Exception.Message)" }
  }
}
$results | Export-Csv ".\AgentJobs_$(Get-Date -f yyyyMMdd).csv" -NoTypeInformation

-EnableException makes dbatools throw, so the catch records unreachable servers instead of skipping them quietly.

#Flag problems

$results | Where-Object { $_.Job -notlike 'ERROR*' } | ForEach-Object {
  $issues = @()
  if ($_.Owner -notin 'sa','CONTOSO\svc-sqljobs')        { $issues += 'personal owner' }
  if (-not $_.EmailOnFail)                                { $issues += 'no failure email' }
  if ($_.Enabled -and -not $_.HasSchedule)                { $issues += 'enabled, no schedule' }
  if ($_.LastOutcome -eq 'Failed')                        { $issues += 'last run failed' }
  if (-not $_.Description)                                { $issues += 'no description' }
  if ($issues) { $_ | Add-Member -NotePropertyName Issues -NotePropertyValue ($issues -join '; ') -PassThru }
} | Export-Csv .\AgentJobIssues.csv -NoTypeInformation

Adjust the owner allow-list to your standard.

#Store centrally instead of CSV

$results | Add-Member -NotePropertyName CollectedAt -NotePropertyValue (Get-Date) -PassThru |
  Write-DbaDbTableData -SqlInstance SQLDBA01 -Database OpsMart -Schema inv -Table AgentJob -AutoCreateTable

Keeping each run lets you answer "when did this job first appear?" and "which jobs disappeared after the migration?".

#Run it on a schedule

A SQL Agent job on your DBA server with a CmdExec step:

powershell.exe -NoProfile -NonInteractive -File "D:\Jobs\inventory\Get-AgentJobInventory.ps1"

Run it under a proxy account that has reader rights on all instances. Weekly is enough for inventory; daily if you also use it for failure reporting.

#Other useful dbatools commands

Check each command's help (Get-Help Find-DbaAgentJob -Detailed). Parameters change between dbatools versions.

Tip

Save the CSV in the same place every week. A diff between two weeks is a cheap change report.