Check for failed SQL Agent Jobs with Powershell

Checking for failed SQL Agent jobs should be part of any DBA workplan. Here’s another Powershell script that makes checking the last run outcome easy on multiple SQL Servers. To run this script you need to create a list of your SQL Servers in a text file called sqlservers.txt. Place this text file in your user profile directory, C:\Users\Rhys on my laptop.

?View Code POWERSHELL
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Get List of sql servers to check
$sqlservers = Get-Content "$Env:USERPROFILE\sqlservers.txt";
 
# Loop through each sql server from sqlservers.txt
foreach($sqlserver in $sqlservers)
{
      # Create an SMO Server object
      $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
 
      # Jobs counts
      $totalJobCount = $srv.JobServer.Jobs.Count;
      $failedCount = 0;
      $successCount = 0;
 
      # For each jobs on the server
      foreach($job in $srv.JobServer.Jobs)
      {
            # Default write colour
            $colour = "Green";
            $jobName = $job.Name;
            $jobEnabled = $job.IsEnabled;
            $jobLastRunOutcome = $job.LastRunOutcome;
 
            # Set write text to red for Failed jobs
            if($jobLastRunOutcome -eq "Failed")
            {
                  $colour = "Red";
                  $failedCount += 1;
            }
            elseif ($jobLastRunOutcome -eq "Succeeded")
            {
                  $successCount += 1;
            }
            Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";
      }
 
      # Writes a summary for each SQL server
      Write-Host -ForegroundColor White "=========================================================================================";
      Write-Host -ForegroundColor White "$sqlserver total jobs = $totalJobCOunt, success count $successCount, failed jobs = $failedCount.";
      Write-Host -ForegroundColor White "=========================================================================================";
}

The output provided shows jobs with a last run status of “Succeeded” in green and “Failed” in red. A summary is provided for each individual SQL Server.

Checking SQL Agent Jobs with Powershell


22 Comments

  1. Royce says:

    I tried using this and it only returns like as if the answers were empty. It all stays green and prints nicely but it’s as if it doesn’t find any jobs? I have several servers and several jobs so I don’t know why it isn’t working?

  2. Rhys says:

    I’ll need to know what is being printed out before I can have a proper guess.

    If there’s green text then it’s finding jobs (the green text printed is for successful jobs).

  3. Royce says:

    got it to work! I had previously had a list of servers for osql but that required quotes around the server name. i removed that and this script works great. thanks

  4. Rhys says:

    Hi Royce,

    Good stuff. If you want to use the same text file for osql and powershell you could add something to remove the quotes from server names, Something like


    $sqlserver = $sqlserver.Replace('"', '');

    should do it.

    Rhys

  5. Royce says:

    thanks for the reply.
    I have a new challenge. I want to use an xml file to contain all the info for a database application. I want to make using this app very easy and so I want to build it in such a way that the only adjustments anyone needs to make is easily contained in the xml file. We have entries like . Currently I have another file that reads the xml and sets a $ variable like $path_fullbackup=[xml]..getattribute(“path_fullbackup”)
    I would like to write some kind of function that would bring into scope a new variable with the same name as whatever the node is in the xml. so in other words to avoid hard coding looking for certain attribute names and defining what the $ variable should be.

    ….
    I don’t really know how to do it so I have some pseudo code that is close to the idea:
    #Iterate through the Path nodes
    Foreach ($Path in $paths)
    {
    ForEach $PathAttrib in $Path.Attributes
    {
    $AttributeName = $PathAttrib.Name
    $AttributeValue = $PathAttrib.Value
    }
    $AttribHash.Add($AttributeName, $AttributeValue)
    }

    Is this even possible?

  6. Rhys says:

    Not something I’ve ever done but it looks simple enough. Checkout Powershell and XML Configuration Files

    Cheers,

    Rhys

  7. Vinay says:

    hi,

    how to print the overview output first and all job details after that. what i want is
    first shud be
    servername total jobs=10 success count=10 failed jobs=0
    then
    all job details one by one.. how to acheive this??

    and one more thing how to mail this entire output???

    Thanks in Advance

  8. Rhys says:

    Hi Vinay,

    Very easily. Just append the job output to a variable and then output it after your title.

    i.e.

    Instead of

    Write-Host -ForegroundColor $colour "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";

    Do..


    $output += "SERVER = $sqlserver JOB = $jobName ENABLED = $jobEnabled LASTRUN = $jobLastRunOutcome";

    Don’t forget to reset $output for each server.

    See this for emailing with Powershell

    Cheers,

    Rhys

  9. dave downey says:

    Thanks for a brilliant script. I have some sql jobs that are disabled but your script still returns their staus as ‘enabled’

  10. Rhys says:

    Hi Dave,

    Mmmmm, it shouldn’t because I’m reading the property for each job at $job.IsEnabled;

    Perhaps you can try resetting these at the bottom of the foreach loop. Perhaps posting the full output might help.

    Cheers,

    Rhys

  11. dave downey says:

    Rhys,

    my apologies – misread the output.
    Enabled = False is marked against all disabled jobs.
    Thanks again for a brilliant script.

    Dave

  12. lee watkins says:

    Thanks for this script! Good stuff! I would like to additionally know how long it took for the job to run (last_run_duration). Can you offer any input? Thanks again – HUGE timesaver!!
    Lee

  13. Rhys says:

    Hi Lee,

    It looks like the last_run_duration field isn’t exposed through SMO http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job_members%28v=sql.100%29.aspx so I guess the only option would be to query msdb for this info.

    Cheers,

    Rhys

  14. Srinath says:

    HI RHYS,

    Thanks for the Script, i am not very good in scripting, entering the scripting.
    ON using the script i have the below error or running the script. Could you please assist.
    PS C:\scripts> .\SQL.ps1
    New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type
    is loaded.
    At C:\scripts\SQL.ps1:9 char:21
    + $srv = New-Object <<<

  15. Srinath says:

    HI RHYS,

    i have sorted and got the result, failing to get the result in mail. Could you assist please

  16. Rhys says:

    Hi Srinath,

    Would need a little more detail, you’ve not really given me anything to go on.

    Rhys

  17. Murad says:

    Hi Rhys,

    Ive come across your useful script as I start to get to grips with PS.
    Forgive the noob question – how could I output the results to a file on c drive?
    Say either as a txt file that is tab formatted or even a html [which I realise is a lot more work]

    Im hoping to ultimately run this from Agent job output to a shared folder for anyone to be able to read easily.

    Thanks

  18. Rhys says:

    Hi Murad,

    Something like this will get you started for export to csv…

    PS C:\Users\campbellr> $srv.JobServer.Jobs | Where-Object {$_.LastRUnOutcome -eq "Failed";} | Select-Object * | Export-C
    sv C:\temp\tmp.txt -NoType

    Check out the ConvertTo-Html cmdlet for export to html.

    Cheers,

    Rhys

  19. Murad says:

    Thanks Rhys

    Can you tell me why I have snowblindness right now – and cant understand why calling “Sqlserver” is not populating in the resultant csv file.

    $srv.JobServer.Jobs | Where-Object {$_.LastRUnOutcome -eq “Failed”;} | Select-Object Sqlserver,Name,Lastrunoutcome | Export-Csv C:\Powershell\csv_test.csv -NoType

  20. Rhys says:

    Are you just running that line? You need to load the smo extension and create the sql server object in $srv before this.

  21. Murad says:

    Hi Rhys

    No I was running that line as part of the end of your overall script.
    So thought it had been defined already in this line:

    # Create an SMO Server object
    $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;

    So

  22. Rhys says:

    I wouldn’t run it as part of that script. You’ll over-write the csv file for each new server unless you handle that somehow. This code works for a single server…

    $sqlserver=”SQLServer”;
    [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null;
    $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;
    $srv.JobServer.Jobs | Where-Object {$_.LastRunOutcome -eq “Failed”;} | Select-Object Sqlserver,Name,Lastrunoutcome | Export-Csv C:\Powershell\csv_test.csv -NoType

Leave a Reply