How do I use PowerShell to find out what jobs failed within the past 24 hours?

Question

Long time DBA, but new to PowerShell. I'm looking to use PowerShell to loop through several SQL Server instances and find out what jobs failed within the past 24 hours. I need to know failures even if the job ran successfully afterwards. Right now I just want to get it to work on one server, and then I'll move on to multiple servers.

What I have so far allows me to loop through all the jobs, but I'm not sure what to do to get the execution statuses for the last 24 hours:

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost"

$jobs = $srv.JobServer.Jobs

foreach ($job in $jobs)
{
    $jobHistory = $job.EnumHistory()
}

Any help would be appreciated. I'm looking forward to getting in to PowerShell more, but right now, some of the SMO is a bit confusing.

Thanks, Dan

Answer 1

The following code is untested, but I think it should work.

$jobs = $srv.JobServer.Jobs

$jhf = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter
$jhf.OutcomeTypes = [Microsoft.SqlServer.Management.Smo.Agent.CompletionResult]::Failed

foreach ($job in $jobs)
{
    foreach ($jobRun in $job.EnumHistory($jhf) | where {$_.RunDate -gt ((Get-Date).AddDays(-1))})
    {
        $jobRun
    }
}

EDIT: I changed mine up a bit after playing around with Shawn's code. I like his method of accessing the RunDate better.

Answer 2

If you are using SQL Server 2008 or higher with SQLPS you can just use this line of code (broken up for readability):\

EDIT: Corrected code to use the EnumHistory as @pk does but within SQLPS. So just really an alternate way of doing it.

dir SQLSERVER:\SQL\ServerName\DEFAULT\JobServer\Jobs | foreach {$_.EnumHistory()} | 
   where {$_.RunStatus -eq 0} | where {$_.RunDate -gt ((Get-Date).AddDays(-1))}

If you are running SQL 2005 you can install SQL Server 2008 R2 SQLPS on the server. Microsoft made it a redistributable package. Chad Miller wrote a good blog post on it and provides a download for the module.