0

I'm going to use Dbatools to check my job is running or not. If it isn't running I need to send out an email alert.

I only have a few backgrounds with PowerShell programming.

# Import-Module D:\Tools\dbatools\dbatools.psd1 if it isn't loaded
If ( ! (Get-module dbatools )) {
Import-Module D:\Tools\dbatools\dbatools.psd1
}

# Get the job status
Get-DbaAgentJob -SqlInstance My_SQL_Instance -Job My_Job_Name | Out-File C:\DBA\Result.txt

# Send the email alert if the job is not running
Send-MailMessage -From My_Email_Address -Subject "My_Job_Name job is not running..." -To User_Email_Address -Attachments C:\DBA\Result.txt -Body "The MiantoEDW replication job is not running..." -BodyAsHtml -SmtpServer My_SmtpServer

I need to verify the property of CurrentRunStatus to determine to send an email alert or not.

DBALUKE HUANG
  • 247
  • 1
  • 10

2 Answers2

1

I would do something like the following:

$jobStatus = Get-DbaAgentJob -SqlInstance My_SQL_Instance -Job My_Job_Name
$jobStatus | Select-Object Name,CurrentRunStatus | Export-Csv C:\DBA\Result.csv -NoTypeInformation
if ($jobStatus.CurrentRunStatus -ne "Executing") {
    # Run some code if job is not running
    Send-MailMessage -From My_Email_Address -Subject "My_Job_Name job is not running..." -To User_Email_Address -Attachments C:\DBA\Result.csv -Body "The MiantoEDW replication job is not running..." -BodyAsHtml -SmtpServer My_SmtpServer
}
else {
    # Run some code if job is running
}

Get-DbaAgentJob doesn't display the CurrentRunStatus property by default. You will need to retrieve it, which is done by Select-Object CurrentRunStatus. Since the command outputs an object, I chose to use Export-Csv to export a cleaner output that aligns the object properties and values. $jobStatus stores the output of the Get-DbaAgentJob command. Accessing the $jobStatus.CurrentRunStatus property for value Executing will verify if a job is currently running.

AdminOfThings
  • 23,946
  • 4
  • 17
  • 27
0

I've not used dbatools but I assume the CurrentRunStatus is available in the Result.txt file you're outputting to?

If so, assign the result of Get-DbaAgentJob to a variable and then Out-File from that variable. Then access the CurrentRunStatus property from the variable to determine whether or not to send the alert.

MysticHeroes
  • 164
  • 5