FailedJobs-HTML

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