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

 
Moderator
7 months, 1 week ago.

  • Author
    Posts
  • #111880

    Participant
    Topics: 32
    Replies: 126
    Points: 279
    Helping Hand
    Rank: Contributor

    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

    Moderator
    Topics: 8
    Replies: 736
    Points: 2,059
    Helping Hand
    Rank: Community Hero

    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
      Topics: 32
      Replies: 126
      Points: 279
      Helping Hand
      Rank: Contributor

      Karunan, your first suggestion did work. Thank you

       

    • #111904

      Participant
      Topics: 32
      Replies: 126
      Points: 279
      Helping Hand
      Rank: Contributor

      Karunan, the 2nd suggestion matches "Version 11.0.7001.0"

    • #111913

      Moderator
      Topics: 8
      Replies: 736
      Points: 2,059
      Helping Hand
      Rank: Community Hero

      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
    Topics: 1
    Replies: 9
    Points: 5
    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
      Topics: 32
      Replies: 126
      Points: 279
      Helping Hand
      Rank: Contributor

      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
    Topics: 1
    Replies: 9
    Points: 5
    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
      Topics: 32
      Replies: 126
      Points: 279
      Helping Hand
      Rank: Contributor

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

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

The topic ‘Extracting event id from SQL Agent Job message’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort