How to retrieve latest SQL Agent job status regardless of execution date with PowerShell

Data and Databases
1

I’m working with a PowerShell cmdlet that retrieves failed SQL Server job history from a specific server instance. Currently I’m using this approach:

Get-SqlAgentJobHistory -ServerInstance "PRODDB2" -Since Yesterday -Outcome Failed

This command works fine for showing failed jobs from yesterday, but I’m running into an issue. Some jobs might have been resolved after failing, and others could have executed several days ago. What I really need is to get the most recent execution status for every job on the server, regardless of when it actually ran. Is there a parameter or different approach that would give me just the latest run result for each job without being limited by time constraints?

2

interesting problem! have you tried querying msdb.dbo.sysjobhistory directly with Invoke-Sqlcmd? the native cmdlets sometimes miss edge cases. what sql server version is running on PRODDB2? that’ll help determine the best approach.

3

The Problem:

You’re using the Get-SqlAgentJobHistory PowerShell cmdlet to retrieve failed SQL Server job history from the “PRODDB2” instance. Your current command only shows jobs that failed yesterday, but you need the most recent execution status for every job, regardless of when it last ran. You want a solution that retrieves only the latest run result for each job without time limitations.

:gear: Step-by-Step Guide:

  1. Retrieve and Process Job History: The core solution involves retrieving all job history entries, grouping them by job name, sorting by run date, and selecting the most recent entry for each job. This is accomplished using a PowerShell pipeline:
Get-SqlAgentJobHistory -ServerInstance "PRODDB2" | 
Group-Object JobName | 
ForEach-Object { $_.Group | Sort-Object RunDate -Descending | Select-Object -First 1 }
  • Get-SqlAgentJobHistory -ServerInstance "PRODDB2": This retrieves all job history entries from the specified server instance. The crucial part is omitting the -Since parameter, which removes the time constraint.
  • Group-Object JobName: This groups the results by the JobName property, ensuring that we have all the history entries for each individual job together.
  • ForEach-Object { ... }: This iterates through each job group.
  • $_.Group | Sort-Object RunDate -Descending: Inside the loop, this sorts the history entries for each job in descending order based on the RunDate property (newest first).
  • Select-Object -First 1: Finally, this selects only the first (most recent) entry from the sorted list for each job.

  1. Verify the Output: After running this command, examine the output carefully. Ensure that you are getting the latest execution status for each job, regardless of the RunDate. Pay close attention to the Outcome property to confirm that you are seeing the most recent result (Success, Failure, etc.).

  2. Handle Large Datasets (Optional): If you have an extremely large number of jobs or a very extensive job history, this approach might take considerable time. Consider optimizing this step by first filtering jobs based on their last execution date. For example, you could restrict the fetch to jobs with activity within the last month.

:mag: Common Pitfalls & What to Check Next:

  • Permissions: Verify that the account used to run the PowerShell script has the necessary permissions to access the SQL Server’s job history.
  • Server Instance Name: Double-check that "PRODDB2" is the correct name of your SQL Server instance. Incorrect capitalization or typos can cause errors.
  • Module Availability: Ensure the SqlServer module is imported into your PowerShell session before running the command. You might need to run Import-Module SqlServer first.
  • Alternative Approach (Less Efficient): If the above solution is too slow or complex, consider fetching all jobs using Get-SqlAgentJob and then iterating through them to fetch the latest history entry with Get-SqlAgentJobHistory for each job. This is less efficient but more straightforward for smaller environments.

:speech_balloon: Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

4

there’s actually a simpler approach - use -MaxCount 1 with Get-SqlAgentJob to grab all jobs, then loop through each one with Get-SqlAgentJobHistory (skip the time filters). it’s slower but way more reliable than trying to group massive datasets, and you won’t miss anything.