Commands / Get-sqmAgentJobScheduleReport
Get-sqmAgentJobScheduleReport
SQL Agent sqmSQLTool v1.8.2+ · Get
Generates an HTML report for all SQL Agent jobs on an instance. Queries msdb.dbo.sysjobs, sysjobschedules, sysschedules and sysjobhistory to show each job's schedule (frequency, start time, subday interval), last execution time, last status, average duration, estimated next run, and last error message (truncated to 100 chars). Writes the report as an HTML file and optionally as CSV. Returns a flat array of PSCustomObject — one row per job/schedule combination.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMESingle instance. No pipeline support.
-SqlCredentialPSCredentialOptional—SQL auth. Omit for Windows auth.
-OutputPathstringOptionalC:\System\WinSrvLog\MSSQLCreated if it does not exist. Write access is verified before running.
-OutputCsvswitchSwitch$falseAlso write a CSV alongside the HTML report.
-EnableExceptionswitchSwitch$falseThrow on error instead of Write-Error + return.

Execution Flow

START dbatools installed? NO if EnableException: throw / else return YES OutputPath exists & writable? NO New-Item -Force verify write access YES Get-DbaAgentJob (all jobs) No job found → WARNING logged; continues to history query Invoke-DbaQuery → msdb history JOIN sysjobs JOIN sysjobschedules JOIN sysschedules JOIN sysjobhistory (step_id=0) Groups: MAX(run_date), MAX(run_status), AVG(run_duration in sec) Converts freq_type (1=Once,4=Daily,8=Weekly,16=Monthly,64=Agent start,128=CPU idle) Per job: parse schedule + execution data _ConvertJobSchedule: freq_type + subday_interval → readable text (e.g. "Daily every 1 day(s) @ 02:00") _ConvertJobRunTime: run_date (YYYYMMDD int) + run_time (HHMMSS int) → datetime string _EstimateNextExecution: heuristic from schedule text — "N/A" if no match -OutputCsv set? YES Export-Csv AgentJobSchedule_*.csv NO _GenerateAgentJobHtml → AgentJobSchedule_*.html Summary stats (total/success/failed/disabled) + filterable/sortable table DONE — returns $jobData array

Examples

Basic report — local instance
Get-sqmAgentJobScheduleReport
Remote instance with CSV export
Get-sqmAgentJobScheduleReport -SqlInstance "SQL01" -OutputPath "D:\Reports" -OutputCsv
SQL auth, throw on error
$cred = Get-Credential
Get-sqmAgentJobScheduleReport -SqlInstance "SQL01\PROD" -SqlCredential $cred -EnableException
Filter returned data for failed jobs only
Get-sqmAgentJobScheduleReport -SqlInstance "SQL01" |
    Where-Object { $_.LastStatus -eq 'Failed' } |
    Select-Object JobName, Schedule, LastExecution, LastError
⬆ Index
All Commands