Getting SQL Agent Jobs and Job Steps Configuration

Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.

Enter dbatools…

In my first attempt at doing this I tried getting all the Jobs using Get-DbaAgentJob and exporting the Jobs to TSQL using Export-DbaScript. This did give me the code for all of the Jobs I was interested in. But that left me trying to decipher SQL Agent Job and Schedule syntax and encodings and I got all twisted up in the TSQL-ness of that. I needed this to be more readable.

So I thought…there has to be a better way…there is! So, I wrote the following. This code gets each SQL Agent Job, print the Job’s Name, NextRunDate, if it has a Schedule, Operator information, and then for each JobStep it prints the Step’s Name, Subsystem, and finally the Command. Using this I can quickly get a feel for the configurations across the environment.

Get a listing of all SQL Instances

    $Servers = Get-DbaRegisteredServer

Get all of the SQL Agent Jobs across all SQL Instances

    $jobs = Get-DbaAgentJob -SqlInstance $Servers.Name

Filter that list down to the SQL Agent Jobs that are in the Database Maintenance category

    $MaintenanceJobs = $jobs | Where-Object { $_.Category -eq 'Database Maintenance' } 

For each SQL Agent Job, print the Job’s Name, NextRunDate, if it has a Schedule, Operator information, and then for each JobStep print its Name, Agent Subsystem, and finally the Command.

    $JobsAndSteps = foreach ($MaintenanceJob in $MaintenanceJobs){
        foreach ($JobStep in $MaintenanceJob.JobSteps) {
            $obj = [PSCustomObject]@{
                SqlInstance = $MaintenanceJob.SqlInstance
                Name = $MaintenanceJob.Name
                NextRunDate = $MaintenanceJob.NextRunDate
                HasSchedule = $MaintenanceJob.HasSchedule
                OperatorToEmail = $MaintenanceJob.OperatorToEmail
                JobStepName = $JobStep.Name
                SubSystem = $JobStep.SubSystem
                Command = $JobStep.Command
                }
            $obj  
        }
    }

Here’s some sample output using Format-Table. From there I can quickly scan and analyze all the Jobs on all of the Instances in an environment.

$JobsAndSteps | Format-Table

SqlInstance     Name                                    NextRunDate           HasSchedule OperatorToEmail JobStepName                                           SubSystem Command
-----------     ----                                    -----------           ----------- --------------- -----------                                           --------- -------
PRODSQL1        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Backup         CmdExec sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'T:\Backup', @Ba...
PRODSQL1        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Sync           CmdExec ROBOCOPY SOME STUFF
PRODSQL1        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Cleanup     PowerShell RUN SOME POWERSHELL TO DO COOL STUFF
PRODSQL2        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Backup         CmdExec sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'T:\Backup', @Ba...
PRODSQL2        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Sync           CmdExec ROBOCOPY SOME STUFF
PRODSQL2        DatabaseBackup - USER_DATABASES - FULL  2/3/2021 1:00:00 AM          True DbaTeam         DatabaseBackup - USER_DATABASES - FULL - Cleanup     PowerShell RUN SOME POWERSHELL TO DO COOL STUFF

You can also take that output and convert it to CSV and then Excel for analysis

$JobsAndSteps | ConvertTo-Csv -NoTypeInformation | Out-File JobSteps.csv