Include Sql Agent job only if invoked by service account

Welcome Forums General PowerShell Q&A Include Sql Agent job only if invoked by service account

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

 
Participant
1 month, 1 week ago.

  • Author
    Posts
  • #121546

    Participant
    Points: 123
    Rank: Participant

    I've got a script that reports on failed Agent jobs but I want it to only include the failed jobs that were invoked by the service account. So that when I re-run the job, if it fails, the failure from me running the job is not included.

    I'm looking for a way of knowing which account invoked the job or of parsing the Message and using teh entry in there.

    Any help greatly appreciated.

    Command is:

    
    Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddHours(-24))
    
    

    Results:

    
    InstanceID : 146792
    SqlMessageID : 0
    Message : The job failed. The Job was invoked by User mydomain\thatuser. The last step to run was step 3 (Master Pkg). The job was requested to start at
    step 1 (Master Pkg).
    StepID : 0
    StepName : (Job outcome)
    SqlSeverity : 0
    JobID : 4475d290-4b1e-4be3-9493-18d0558a878b
    JobName : importantJob
    RunStatus : 0
    RunDate : 11/3/2018 8:01:28 AM
    RunDuration : 29
    OperatorEmailed : DW Operators
    OperatorNetsent :
    OperatorPaged :
    RetriesAttempted : 0
    Server : Server\Instance
    
     
    
    InstanceID : 146788
    SqlMessageID : 0
    Message : The job failed. The Job was invoked by User mydomain\serviceaccount. The last step to run was step 3 (Master Pkg).
    StepID : 0
    StepName : (Job outcome)
    SqlSeverity : 0
    JobID : 4475d290-4b1e-4be3-9493-18d0558a878b
    JobName : importantJob
    RunStatus : 0
    RunDate : 11/3/2018 8:00:40 AM
    RunDuration : 25
    OperatorEmailed : DW Operators
    OperatorNetsent :
    OperatorPaged :
    RetriesAttempted : 0
    Server : Server\Instance
    
    
  • #121549

    Participant
    Points: 815
    Helping Hand
    Rank: Major Contributor

    You can do a filter on Message property using Where-Object with a regex match on serviceaccount.

    Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddHours(-24)) | Where-Object -FilterScript {
    $_.Message -notmacth 'serviceaccount'
    }
    
  • #121555

    Participant
    Points: 123
    Rank: Participant

    Thank you very much!

    I'd been at this for ages....I didn't know about the filterscript.

    
    -FilterScript
    
    

     

You must be logged in to reply to this topic.