Using Powershell to List Failed SQL Server Jobs

Running the code below within the Powershell interface will list all SQL Server jobs failed
on SQL Server instances (SQL SERVER 2000 & SQL SERVER 2005) listed in the "C:\Instances.txt" document.
An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Currently the sql code will list jobs failed within the last 2 days. Configure to your requirements.

To run , wither copy and paste straight into a Powershell  cmdlet which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension. 

-----CODE STARTS-----------------------------------
foreach ($svr in get-content "C:\Instances.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT sjh.server,sj.name, CONVERT(VARCHAR(30),sjh.message) as message , sjh.run_date, sjh.run_time
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(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 2"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}
-----------------CODE ENDS--------------------------

 See Also

SQL Agent Jobs – Schedule in seconds

Powershell Scripts for DBA