Using Powershell to find last step run from an SQL agent job

Question

I have a powershell script that provides SQL Agent Job information. The script is meant to monitor the jobs and tell me if they failed the last time they ran. However i also want to know which was the last run step. and i can't seem to figure out how.

I am querying the Sql server mangement object, because it needs to be usable on multiple remote servers (remote connections) and i wish to avoid running SQL scripts.

Keep in mind i'm rather new to powershell.

This is the code i have so far: And i have loaded the SMO libraries, i just didn't show it the copied script.

## Get Jobstep Class SMO

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location;
$JobStep = New-Object microsoft.sqlserver.management.smo.agent.jobstep

## Run the select from the local SQL server management objects

$SQLSvr = "."
$MySQLObject = new-object Microsoft.SqlServer.Management.Smo.Server `
$SQLSvr;;

$Select = ($MySQLObject.JobServer.jobs) `
| Select  Name, isEnabled, `
lastRunDate, lastRunOutCome, NextRunDate  `
| Where-Object {$_.LastRunDate -ge ((Get-Date).adddays(-2)) } `
| ft -AutoSize;  

$Select

The push-location section is meant to get the correct smo class for selecting the job step (unsure if it is right), however i haven't added anything from it.

Now the script does work, i get no errors and i get returned the overall information i want, but i cannot figure out how to add the jobstep - and i have consulted google. I'm well aware that i need to add more to the select, but what is the issue for me. So, how do i extract the last run job step from SQL Agent job, using the SMO, and add it to the above script?

Answer 1

You can use the SqlServer module from the PowerShell Gallery (Install-Module SqlServer), and then something like:

$h = Get-SqlAgentJobHistory -ServerInstance servername -JobName jobname

$h[0] will give you the last step ran.

This will give you the result in the format you wanted:

Get-SqlAgentJob -ServerInstance servername | 
Where-Object {$_.LastRunDate -ge ((Get-Date).AddDays(-2))} | ForEach-Object {
    $h = Get-SqlAgentJobHistory -ServerInstance servername -JobName $_.Name
    [PSCustomObject]@{
        Name           = $_.Name
        IsEnabled      = $_.IsEnabled
        LastRunDate    = $_.LastRunDate
        LastRunOutcome = $_.LastRunOutcome
        NextRunDate    = $_.NextRunDate
        LastRunStep    = $h[0].StepName
    }
}