This post explains how to list failed SQL Server Jobs , on multiple SQL Server Instances and output the results to a HTML file.

 It is only one extra step to send this file as an attachment.

The example script , returns failed SQL Server Agent Jobs within the last 48 hrs, for a SQL Server Instance.

$isodate=Get-Date -format s 
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\instances.txt"
$outputfile="\logs\sql_server_health_sqlserver_jobs_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile
#invoke stylesheet
. .\modules\stylesheet.ps1
 
$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
$cn.Open()
$sql = $cn.CreateCommand()
$sql.CommandText = "SELECT sjh.server,sj.name, sjh.message, sjh.run_date, sjh.run_time , ‘http://www.sqlserver-dba.com/contact-me/’ as Explain
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4) 
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid 
WHERE    DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 48
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
 
}

$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle | Set-Content $outputfilefull

See Also

SQL Server – Send email using Powershell

SQL Agent Jobs – Schedule in seconds

Author: Jack Vamvas (http://www.sqlserver-dba.com)