Get-SqlAgentJobHistory
Gets the job history present in the target instance of SQL Agent.
Syntax
ByPath (Default)
Get-SqlAgentJobHistory
[[-Path] <String[]>]
[-StartRunDate <DateTime>]
[-EndRunDate <DateTime>]
[-JobID <Guid>]
[-JobName <String>]
[-MinimumRetries <Int32>]
[-MinimumRunDurationInSeconds <Int32>]
[-OldestFirst]
[-OutcomesType <CompletionResult>]
[-SqlMessageID <Int32>]
[-SqlSeverity <Int32>]
[-Since <SinceType>]
[-AccessToken <PSObject>]
[-TrustServerCertificate]
[-HostNameInCertificate <String>]
[-Encrypt <String>]
[<CommonParameters>]
ByName
Get-SqlAgentJobHistory
[[-ServerInstance] <String[]>]
[-StartRunDate <DateTime>]
[-EndRunDate <DateTime>]
[-JobID <Guid>]
[-JobName <String>]
[-MinimumRetries <Int32>]
[-MinimumRunDurationInSeconds <Int32>]
[-OldestFirst]
[-OutcomesType <CompletionResult>]
[-SqlMessageID <Int32>]
[-SqlSeverity <Int32>]
[-Since <SinceType>]
[-Credential <PSCredential>]
[-ConnectionTimeout <Int32>]
[-AccessToken <PSObject>]
[-TrustServerCertificate]
[-HostNameInCertificate <String>]
[-Encrypt <String>]
[<CommonParameters>]
ByObject
Get-SqlAgentJobHistory
[-InputObject] <JobServer[]>
[-StartRunDate <DateTime>]
[-EndRunDate <DateTime>]
[-JobID <Guid>]
[-JobName <String>]
[-MinimumRetries <Int32>]
[-MinimumRunDurationInSeconds <Int32>]
[-OldestFirst]
[-OutcomesType <CompletionResult>]
[-SqlMessageID <Int32>]
[-SqlSeverity <Int32>]
[-Since <SinceType>]
[-AccessToken <PSObject>]
[-TrustServerCertificate]
[-HostNameInCertificate <String>]
[-Encrypt <String>]
[<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.
Parameter properties
| Type: | PSObject |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByName
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | PSCredential |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByName
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | String |
| Default value: | None |
| Accepted values: | Mandatory, Optional, Strict |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-EndRunDate
Specifies a job filter constraint that restricts the values returned to the date the job completed.
Parameter properties
| Type: | DateTime |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-InputObject
Specifies an array of SQL Server Management Object (SMO) objects that represent the SQL Server Agent being targeted.
Parameter properties
| Type: | JobServer[] |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByObject
| Position: | 1 |
| Mandatory: | True |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-JobID
Specifies a job filter constraint that restricts the values returned to the job specified by the job ID value.
Parameter properties
| Type: | Guid |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-JobName
Specifies a job filter constraint that restricts the values returned to the job specified by the name of the job.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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
Parameter properties
| Type: | CompletionResult |
| Default value: | None |
| Accepted values: | Failed, Succeeded, Retry, Cancelled, InProgress, Unknown |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | String[] |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByPath
| Position: | 1 |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | String[] |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
ByName
| Position: | 1 |
| Mandatory: | False |
| Value from pipeline: | True |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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)
Parameter properties
| Type: | SinceType |
| Default value: | None |
| Accepted values: | Midnight, Yesterday, LastWeek, LastMonth |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-SqlMessageID
Specifies a job filter constraint that restricts the values returned to jobs that have generated the specified message during runtime.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | Int32 |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
-StartRunDate
Specifies a job filter constraint that restricts the values returned to the date the job started.
Parameter properties
| Type: | DateTime |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | 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.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
(All)
| Position: | Named |
| Mandatory: | False |
| Value from pipeline: | False |
| Value from pipeline by property name: | False |
| Value from remaining arguments: | False |
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable, -ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.