Extracting event id from SQL Agent Job message

Welcome Forums General PowerShell Q&A Extracting event id from SQL Agent Job message

This topic contains 8 replies, has 3 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #111880

    Participant
    Points: 3
    Rank: Member

    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.
    
    
  • #111883

    Participant
    Points: 20
    Rank: Member

    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]}}
    
    
    • #111901

      Participant
      Points: 3
      Rank: Member

      Karunan, your first suggestion did work. Thank you

       

    • #111904

      Participant
      Points: 3
      Rank: Member

      Karunan, the 2nd suggestion matches "Version 11.0.7001.0"

    • #111913

      Participant
      Points: 20
      Rank: Member

      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.

  • #111890

    Participant
    Points: 0
    Rank: Member

    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?

    • #111899

      Participant
      Points: 3
      Rank: Member

      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.

  • #111896

    Participant
    Points: 0
    Rank: Member

    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?

     

    • #111905

      Participant
      Points: 3
      Rank: Member

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

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

You must be logged in to reply to this topic.