Monitoring MS SQL Jobs with PowerShell

PROBLEM: You manage many MS SQL servers and need an easy way to review jobs that have failed.

SOLUTION: I will show you one way to accomplish this task with results displaying on the screen or to a file. To make your life easier, I recommend you download & install PowerGUI from Quest Software. This application comes with a script editor that will enhance your PowerShell development experience.

Go ahead and open a new session of PowerShell. You can type or copy and paste the code from our initial three lines of code. We will start by loading the SMO assembly & creating an instance of the server object. On the third and final line of code, we get a collection of all jobs by accessing the SQL Server agent on the server. We then pipe the resulting object and look for all jobs which are enabled that have failed.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "TUIRA"
$srv.jobserver.jobs|where-object {$_.IsEnabled -eq $TRUE -and `
    $_.LastRunOutcome -eq "Failed"
}

You will notice the result not being in a desirable format, especially when you have many jobs. We will use the information from one of the jobs to pick the specific properties we want in our report and display them in a table format. Lets pick the Parent, Category, Description, LastRunDate, LastRunOutcome, & Name properties for our next result.
On your PS session screen, hit the up key and you should get the last line of commands we executed. Hit the left arrow key to modify the command as follow

$srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}|Format-Table `
    Parent, Category, Name, LastRunDate, LastRunOutcome, Description `
    -Autosize

Now we have a result we can save to a file or print. But before doing that, we’ll expand this script to retrieve the jobs from multiples servers. Lets go ahead and open a Notepad session and enter the name of a few MS SQL servers you manage, one name per line, and save it with the file name serverlist.txt. I saved mine in C: for simplicity. Now, we just need to enclose our previously executed commands in a loop.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
foreach($svr in get-content C:serverlist_prd.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}| `
        Format-Table Parent, Category, Name, LastRunDate, `
        LastRunOutcome, Description -Autosize
}

I find this looping syntax very helpful whenever I need to execute code against multiple servers. The result from this modified version of the original code will leave a blank space between each server or iteration of the code. We’ll solve that with a variable in the next and final version of the script which includes the code to save the report to a file.

CLEAR
Set-Content -Path "C:TempMonitorJobs_LowerEnvironments.txt" -Value " "
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null

foreach($svr in get-content C:tempserverlist.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $a += $srv.jobserver.jobs
}

$a|where-object {$_.isenabled -eq $TRUE -and $_.LastRunOutcome `
    -eq "Failed"}|Sort-Object -Descending LastRunDate| `
    Format-Table Parent, Category, Name, LastRunDate, `
    LastRunOutcome, Description -Autosize|Out-File `
    -FilePath C:TempMonitorJobs.txt -Append -Encoding ASCII