Get-SqlAgentJobHistory

Gets the job history present in the target instance of SQL Agent.

Syntax

Get-SqlAgentJobHistory
   [-StartRunDate <DateTime>]
   [-EndRunDate <DateTime>]
   [-JobID <Guid>]
   [-JobName <String>]
   [-MinimumRetries <Int32>]
   [-MinimumRunDurationInSeconds <Int32>]
   [-OldestFirst]
   [-OutcomesType <CompletionResult>]
   [-SqlMessageID <Int32>]
   [-SqlSeverity <Int32>]
   [-Since <SinceType>]
   [[-Path] <String[]>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Get-SqlAgentJobHistory
   [-StartRunDate <DateTime>]
   [-EndRunDate <DateTime>]
   [-JobID <Guid>]
   [-JobName <String>]
   [-MinimumRetries <Int32>]
   [-MinimumRunDurationInSeconds <Int32>]
   [-OldestFirst]
   [-OutcomesType <CompletionResult>]
   [-SqlMessageID <Int32>]
   [-SqlSeverity <Int32>]
   [-Since <SinceType>]
   [[-ServerInstance] <String[]>]
   [-Credential <PSCredential>]
   [-ConnectionTimeout <Int32>]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Get-SqlAgentJobHistory
   [-StartRunDate <DateTime>]
   [-EndRunDate <DateTime>]
   [-JobID <Guid>]
   [-JobName <String>]
   [-MinimumRetries <Int32>]
   [-MinimumRunDurationInSeconds <Int32>]
   [-OldestFirst]
   [-OutcomesType <CompletionResult>]
   [-SqlMessageID <Int32>]
   [-SqlSeverity <Int32>]
   [-Since <SinceType>]
   [-InputObject] <JobServer[]>
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]

Description

The Get-SqlAgentJobHistory cmdlet gets the JobHistory object present in the target instance of SQL Agent.

This cmdlet supports the following modes of operation to get the JobHistory object:

  • Specify the path of the SQL Agent instance.
  • Pass the instance of the SQL Agent in the input.
  • Invoke the cmdlet in a valid context.

Examples

Example 1: Get the entire job history from the specified server instance

PS C:\> Get-SqlAgentJobHistory -ServerInstance "MyServerInstance" | Format-Table

InstanceID SqlMessageID Message
---------- ------------ -------
        34            0 The job succeeded.  The Job was invoked by Schedule 8 (syspolicy_purge_history_schedule).  T... 
        33            0 Executed as user: DOMAIN\Machine1$. The step did not generate any output.  Process Exit

This command gets the entire job history in the server instance named MyServerInstance and then formats the output.

Example 2: Get the job history from the specified server instance

PS C:\> Get-SqlAgentJobHistory -ServerInstance "MyServerInstance" -JobID 187112d7-84e1-4b66-b093-e97201c441ed

JobID            : 187112d7-84e1-4b66-b093-e97201c441ed
JobName          : Job_73cc6990-6386-49f9-9826-96c318ad8afa
RunStatus        : 3

This command gets the job history of the job object with ID '187112d7-84e1-4b66-b093-e97201c441ed' in the server instance named 'MyServerInstance'.

Example 3: Get the job history from a time duration from the specified server instance

PS C:\> Get-SqlAgentJobHistory -ServerInstance "MyServerInstance" -Since Yesterday
InstanceID       : 4
SqlMessageID     : 0
Message          : The job was stopped prior to completion by User admin.  The Job was invoked by User
                   admin.  The last step to run was step 1 (JobStep_3e4cd4ba-3433-4311-a6a2-816884101504).

This command returns the job history since the day before in the server instance named 'MyServerInstance'.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionTimeout

Specifies the number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer value between 0 and 65534. If 0 is specified, connection attempts do not time out.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Credential

Specifies a PSCredential object that is used to specify the credentials for a SQL Server login that has permission to perform this operation.

Type:PSCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-EndRunDate

Specifies a job filter constraint that restricts the values returned to the date the job completed.

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies an array of SQL Server Management Object (SMO) objects that represent the SQL Server Agent being targeted.

Type:JobServer[]
Position:1
Default value:None
Required:True
Accept pipeline input:True
Accept wildcard characters:False

-JobID

Specifies a job filter constraint that restricts the values returned to the job specified by the job ID value.

Type:Guid
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-JobName

Specifies a job filter constraint that restricts the values returned to the job specified by the name of the job.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MinimumRetries

Specifies the job filter constraint that restricts the values returned to jobs that have failed and been retried for minimum number of times.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-MinimumRunDurationInSeconds

Specifies a job filter constraint that restricts the values returned to jobs that have completed in the minimum length of time specified, in seconds.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-OldestFirst

Indicates that this cmdlet lists jobs in oldest-first order. If you do not specify this parameter, the cmdlet uses newest-first order.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-OutcomesType

Specifies a job filter constraint that restricts the values returned to jobs that have the specified outcome at completion.

The acceptable values for this parameter are:

-- Failed -- Succeeded -- Retry -- Cancelled -- InProgress -- Unknown

Type:CompletionResult
Accepted values:Failed, Succeeded, Retry, Cancelled, InProgress, Unknown
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Path

Specifies the path to the Agent of SQL Server, as an array, on which this cmdlet runs the operation. If you do not specify a value for this parameter, the cmdlet uses the current working location.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ServerInstance

Specifies the name of an instance of SQL Server, as an array, where the SQL Agent runs. For default instances, only specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-Since

Specifies an abbreviation that you can instead of the StartRunDate parameter.

It can be specified with the EndRunDate parameter.

You cannot use the StartRunDate parameter, if you use this parameter.

The acceptable values for this parameter are:

  • Midnight (gets all the job history information generated after midnight)
  • Yesterday (gets all the job history information generated in the last 24 hours)
  • LastWeek (gets all the job history information generated in the last week)
  • LastMonth (gets all the job history information generated in the last month)
Type:SinceType
Accepted values:Midnight, Yesterday, LastWeek, LastMonth
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SqlMessageID

Specifies a job filter constraint that restricts the values returned to jobs that have generated the specified message during runtime.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-SqlSeverity

Specifies a job filter constraint that restricts the values returned to jobs that have generated an error of the specified severity during runtime.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-StartRunDate

Specifies a job filter constraint that restricts the values returned to the date the job started.

Type:DateTime
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

System.String[]

Microsoft.SqlServer.Management.Smo.Agent.JobServer[]

Outputs

System.Object