PowerShell Function to Check the Status of a SQL Agent Job using the .NET Framework

My previous blog article demonstrated how to start a SQL agent job using the .NET Framework from PowerShell to eliminate the dependency of needing the SQL Server PowerShell module or snap-in on the machine where the command is being run from.

There's not much use of blindly starting a SQL agent job without being able to check the status of it so I decided to write another function to accomplish that task. If the machine where the code is being run from has the SQL Server PowerShell module that installs as part of SQL Server Management Studio 2016, then you could simply use existing cmdlets without having to use this custom function.

  1#Requires -Version 3.0
  2function Get-MrSqlAgentJobStatus {
  3
  4<#
  5.SYNOPSIS
  6    Retrieves the status of one or more SQL Agent Jobs from the specified target instance of SQL Server.
  7
  8.DESCRIPTION
  9    Get-MrSqlAgentJobStatus is a PowerShell function that is designed to retrieve the status of one or more SQL
 10    Server Agent jobs from the specified target instance of SQL Server without requiring the SQL Server PowerShell
 11    module or snap-in to be installed.
 12
 13.PARAMETER ServerInstance
 14    The name of an instance of SQL Server where the SQL Agent is running. For default instances, only
 15    specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.
 16
 17.PARAMETER Name
 18    Specifies the name of one or more Job objects that this cmdlet gets. The names may or may not be
 19    case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running.
 20
 21.PARAMETER Credential
 22    SQL Authentication userid and password in the form of a credential object.
 23
 24.EXAMPLE
 25     Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history, test
 26
 27.EXAMPLE
 28     Get-MrSqlAgentJobStatus -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential)
 29
 30.EXAMPLE
 31     'syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQLServer01
 32
 33.INPUTS
 34    String
 35
 36.OUTPUTS
 37    PSCustomObject
 38
 39.NOTES
 40    Author:  Mike F Robbins
 41    Website: http://mikefrobbins.com
 42    Twitter: @mikefrobbins
 43#>
 44
 45    [CmdletBinding()]
 46    param (
 47        [Parameter(Mandatory)]
 48        [string]$ServerInstance,
 49
 50        [Parameter(Mandatory,
 51                   ValueFromPipeLine)]
 52        [string[]]$Name,
 53
 54        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
 55    )
 56
 57    BEGIN {
 58
 59        $Params = @{
 60            ServerInstance = $ServerInstance
 61            Database = 'msdb'
 62        }
 63
 64        if ($PSBoundParameters.Credential) {
 65            $Params.Credential = $Credential
 66        }
 67
 68    }
 69
 70    PROCESS {
 71        foreach ($n in $Name) {
 72
 73            $Params.Query = "EXEC dbo.sp_help_job @JOB_NAME = '$n', @job_aspect= 'JOB'"
 74            $Results = Invoke-MrSqlDataReader @Params |
 75                       Select-Object -Property originating_server, name, last_run_outcome, current_execution_status
 76
 77            [pscustomobject]@{
 78                ComputerName = $Results.originating_server
 79                Name = $Results.name
 80                Outcome = switch ($Results.current_execution_status) {
 81                              1 {'Executing';break}
 82                              2 {'Waiting for thread';break}
 83                              3 {'Between retries';break}
 84                              4 {'Idle';break}
 85                              5 {'Suspended';break}
 86                              7 {'Performing completion actions';break}
 87                              default {'An unknown error has occurred'}
 88                          }
 89                Status = switch ($Results.last_run_outcome) {
 90                             0 {'Failed';break}
 91                             1 {'Succeeded';break}
 92                             3 {'Canceled';break}
 93                             5 {'Unknown';break}
 94                             default {'An unknown error has occurred'}
 95                         }
 96            }
 97
 98        }
 99
100    }
101
102}

Specify the SQL instance name and job name to return the status. There's also a credential parameter to specify SQL authentication if needed.

1Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history

agentjobstatus1a.png

Multiple jobs can also be retrieved via parameter or pipeline input:

1Get-MrSqlAgentJobStatus -ServerInstance SQL011 -Name syspolicy_purge_history, test
2syspolicy_purge_history', 'test' | Get-MrSqlAgentJobStatus -ServerInstance SQL011

agentjobstatus2a-768x215.png

The Get-MrSqlAgentJobStatus function shown in this blog article can be downloaded from my SQL repository on GitHub. This function depends on my Invoke-MrSqlDataReader function which is also part of my MrSQL PowerShell module which can be found in the same repo.

µ