Extracting event id from SQL Agent Job message

PowerShell Help
1

I have a job that runs and reports on Agent job failures, it works fine but I want to parse the message to get the “Execution ID” if there is one and add that in the select as a separate field.

The regex to get the numbers after “Execution ID:” is what I’m looking for help with. I’ve tried adding various regex expressions in here “Expression={$_.Message}” but not found a solution.

Any help or guidance gratefully received.

 

Execution ID: 22522

 

Command is:

WorkFlow Run-PAgentJobHistory

{

[cmdletbinding()]

param(

[Parameter(Mandatory=$true)]

[string[]]$ServerInstance  # string array to hold multiple sql instances

)

foreach -parallel ($S in $ServerInstance)

{

#Get-SqlAgentJob -ServerInstance $S

Get-SqlAgentJobHistory -ServerInstance $S -StartRunDate (Get-Date).AddHours(-1) -OutcomesType Failed | ?{ $_.StepName -ne "(Job outcome)" }

}

}

$FailedJobs = @()

$FailedJobs += Run-PAgentJobHistory $SqlServers | Select Server, JobName, StepName , RunDate, Message, @{Label="Execution ID"; Expression={$_.Message}}

 

Example of error message:

The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.

Executed as user: domain\svcsql. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.7001.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  11:28:23 PM  Package execution on IS Server failed.

Execution ID: 22522, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  11:28:23 PM  Finished: 11:41:41 PM  Elapsed:  797.562 seconds.

The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.
2

You can use -Match operator to find it via regex, here are two ways of doing it here.

#Match to Execution ID: 
@{Label="Execution ID"; Expression={$null=$_.Message -match 'Execution ID: \d{4}';($Matches[0] -split ':')[-1].Trim()}}

#Direct match to 4 digit number.

@{Label="Execution ID"; Expression={$null=$_.Message -match '\d{4}';$Matches[0]}}
3

This code

$Test= "The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.

Executed as user: domain\svcsql. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.7001.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  11:28:23 PM  Package execution on IS Server failed.

Execution ID: 22522, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  11:28:23 PM  Finished: 11:41:41 PM  Elapsed:  797.562 seconds.

The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.
"

$Test -match "(?Execution ID: )(?\d*)"

$Matches.ExecIDText
$Matches.ExecIDValue

 

Gives this result

True
Execution ID:
22522

Is that what you were looking for?

4

This code:

$Test= "The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.

Executed as user: domain\svcsql. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.7001.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  11:28:23 PM  Package execution on IS Server failed.

Execution ID: 22522, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  11:28:23 PM  Finished: 11:41:41 PM  Elapsed:  797.562 seconds.

The package execution failed.  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.
"

$Test -match "(?*ExecIDText*Execution ID: )(?*ExecIDValue*\d*)"

$Matches.ExecIDText
$Matches.ExecIDValue

$Test -match '(?Execution)'

Gives this result:

True
Execution ID:
22522

You’ll have to replace the * characters around the group names by “smaller than”, “greater that” signs, I’dont know how to get those characters in the code example (they’re interpreted as HTML ans erased)
 

Is that what you were looking for?

 

5

Kris, although taking it separately as you did, yes it does, but I’m trying to get the result in the select statement and in that it fails.

6

Karunan, your first suggestion did work. Thank you

 

7

Karunan, the 2nd suggestion matches “Version 11.0.7001.0”

8

I tried using the format you suggested in the Select but it returns nothing.

Expression={$null=$_.Message -match "(?Execution ID: )(?\d*)"}}
9
the 2nd suggestion matches "Version 11.0.7001.0"

Yes, you have multiple numerics in the message. I prefer the first one which is specific.