Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
FailedJobs-HTML
$today = get-date -Format "MM/dd/yyyy"
$header = @"
<style>
h1 {
font-family: Arial, Helvetica, sans-serif;
color: #e68a00;
font-size: 28px;
}
h2 {
font-family: Arial, Helvetica, sans-serif;
color: #000099;
font-size: 16px;
}
table {
font-size: 12px;
border: 0px;
font-family: Arial, Helvetica, sans-serif;
}
td {
padding: 4px;
margin: 0px;
border: 0;
}
th {
background: #395870;
background: linear-gradient(#49708f, #293f50);
color: #fff;
font-size: 11px;
text-transform: uppercase;
padding: 10px 15px;
vertical-align: middle;
}
tbody tr:nth-child(even) {
background: #f0f0f2;
}
#CreationDate {
font-family: Arial, Helvetica, sans-serif;
color: #ff3300;
font-size: 12px;
}
.RunningStatus {
color: #008000;
}
.StopStatus {
color: #ff0000;
}
</style>
"@
#Heading for report
$reportInfo = "<h1>SQL Agent Job Failure Report: $today</h1>"
# Load variable with list of servers to look for failed jobs
$servers = Get-DbaRegisteredServer -SqlInstance "localhost\sql2019" -Group "Servers"
# Get Agent Job information for all jobs on all servers in the $servers variable
$failedJobs = Get-DbaAgentJob -SqlInstance $servers
# Apply different filters
# Now just Failed run outcomes
$failedJobsOnly = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>Jobs with Failed Status</h2>"
# Show all jobs that did not have a "Successful" last run outcome
$failedJobsAll = $failedJobs | Where-Object LastRunOutcome -ne "Succeeded" | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Non-Successful Jobs</h2>"
# Now filter out those Replication jobs that were cancelled
$failedJobsNoRepl = $failedJobs | Where-Object { ( $_.LastRunOutcome -ne "Succeeded") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Failed Jobs with No Replication Jobs</h2>"
# How about any outcome that is not Failed or Succeeded
$failedJobsNotFailedSuccess = $failedJobs | Where-Object { ( $_.LastRunOutcome -ne "Succeeded" -AND $_.LastRunOutcome -ne "Failed") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Job Status Not Succeed or Fail</h2>"
# What if we want to see jobs that have never been executed
$failedJobsNeverExecuted = $failedJobs | Where-Object { ( $_.LastRunDate -eq "01/01/0001") -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed</h2>"
# Know we can look for never executed and not enabled
$failedJobsNeverExecutedDisabled = $failedJobs | Where-Object { ( $_.LastRunDate -eq "01/01/0001" -AND $_.IsEnabled -eq $false) -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed and Disabled</h2>"
# Do failed jobs have a schedule
$failedJobsNeverExecutedNoSchedule = $failedJobs | Where-Object { ( $_.LastRunOutcome -eq "Failed" -AND $_.HasSchedule -eq $false) -AND ($_.Category -notlike "REPL*") } | ConvertTo-Html -Property SqlInstance, Name, LastRunDate, LastRunOutcome, Category, IsEnabled, HasSchedule -Fragment -PreContent "<h2>All Jobs Never Executed and No Schedule</h2>"
#The command below will combine all the information gathered into a single HTML report
$Report = ConvertTo-HTML -Body "$reportInfo $failedJobsOnly $failedJobsAll $failedJobsNoRepl $failedJobsNotFailedSuccess $failedJobsNeverExecuted $failedJobsNeverExecutedDisabled $failedJobsNeverExecutedNoSchedule" `
-Title "Failed SQL Agent Job Report" -Head $header -PostContent "<p id='CreationDate'>Creation Date: $today</p>"
#The command below will generate the report to an HTML file
$Report | Out-File "C:\Temp\FailedJobsReport.html"
$body = Get-Content C:\Temp\FailedJobsReport.html -Raw
#Send email to DBA Team
Send-MailMessage -To gbargsley@gmail.com -From ProdDBAs@gmail.com -Subject " Failed SQL Agent Job Report " -Body $body -BodyAsHtml -Priority High -SmtpServer mail.gmail.com
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.