Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks PK that is exactly what I was looking for! –  SQL3D Sep 22 '11 at 19:12

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.

share|improve this answer
    
That won't return jobs that failed in the last 24 hours, but then ran successfully after that. –  pk. Sep 22 '11 at 13:19
    
@pk not the first time this has happened :) –  Shawn Melton Sep 22 '11 at 14:26
    
Thanks Shawn. This works as well. +1 Sorry I couldn't give you both credit as an answer. –  SQL3D Sep 22 '11 at 19:13
    
No worries. SMO is the fav for most to work with SQL Server. I am not to proficient with it yet. –  Shawn Melton Sep 23 '11 at 2:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.