# Extracting event id from SQL Agent Job message

**URL:** <https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320>\
**Category:** PowerShell Help\
**Created:** [September 17, 2018, 7:34am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320 "2018-09-17T07:34:58Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [September 17, 2018, 7:34am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/1 "2018-09-17T07:34:58Z")

</div>

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.

&nbsp;

```
Execution ID: 22522
```

&nbsp;

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}}
```

&nbsp;

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.
```

---

<div class="post-metadata">

**Author:** ![kvprasoon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kvprasoon/32/4449_2.png) [@kvprasoon](https://forums.powershell.org/u/kvprasoon)\
**Post date:** [September 17, 2018, 7:55am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/2 "2018-09-17T07:55:32Z")

</div>

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]}}
```

---

<div class="post-metadata">

**Author:** ![krisvg](https://avatars.discourse-cdn.com/v4/letter/k/da6949/32.png) [@krisvg](https://forums.powershell.org/u/krisvg)\
**Post date:** [September 17, 2018, 8:13am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/3 "2018-09-17T08:13:23Z")

</div>

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
```

&nbsp;

Gives this result

True  
Execution ID:  
22522

Is that what you were looking for?

---

<div class="post-metadata">

**Author:** ![krisvg](https://avatars.discourse-cdn.com/v4/letter/k/da6949/32.png) [@krisvg](https://forums.powershell.org/u/krisvg)\
**Post date:** [September 17, 2018, 8:19am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/4 "2018-09-17T08:19:54Z")

</div>

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)  
&nbsp;

Is that what you were looking for?

&nbsp;

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [September 17, 2018, 8:21am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/5 "2018-09-17T08:21:42Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [September 17, 2018, 8:23am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/6 "2018-09-17T08:23:50Z")

</div>

Karunan, your first suggestion did work. Thank you

&nbsp;

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [September 17, 2018, 8:23am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/7 "2018-09-17T08:23:50Z")

</div>

Karunan, the 2nd suggestion matches “Version 11.0.7001.0”

---

<div class="post-metadata">

**Author:** ![firmbyte](https://avatars.discourse-cdn.com/v4/letter/f/e68b1a/32.png) [@firmbyte](https://forums.powershell.org/u/firmbyte)\
**Post date:** [September 17, 2018, 8:28am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/8 "2018-09-17T08:28:57Z")

</div>

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

```
Expression={$null=$_.Message -match "(?Execution ID: )(?\d*)"}}
```

---

<div class="post-metadata">

**Author:** ![kvprasoon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kvprasoon/32/4449_2.png) [@kvprasoon](https://forums.powershell.org/u/kvprasoon)\
**Post date:** [September 17, 2018, 8:36am UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/9 "2018-09-17T08:36:20Z")

</div>

> _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.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:34pm UTC](https://forums.powershell.org/t/extracting-event-id-from-sql-agent-job-message/11320/10 "2024-05-16T20:34:21Z")

</div>