SQL Statement

select t2.name + ': ' + convert(varchar,t1.step_id) + ': ' + t1.step_name + ': ' + t1.message
       AS "JOB NAME: STEP ID: STEP NAME: MESSAGE", 1
FROM msdb..sysjobhistory  T1    
JOIN msdb..sysjobs  T2 ON T1.job_id = T2.job_id    
WHERE T1.run_status = 0    
AND T1.step_id != 0
AND CONVERT(DATETIME,RTRIM(run_date)) +
    (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration) / 216e4
    >= dateadd (n,-#FREQUENCY#,current_timestamp)

Description

This alert will look for jobs that have failed since the last time this alert executed.  The #FREQUENCY#
variable provides the number of minutes between each run of the alert (specified in the Execution Interval
of the alert definition).  If the alert executes once every 10 minutes and is now running at 10:00 am,
the alert will look for jobs that have failed since 9:50 (10 minutes ago).  The email message will
contain the Job Name, Step ID, Step Name and Error Message separated by colons.  The 1 at the end of
the alert is used to trigger the threshold in Ignite, note the 1 as the min value for the high threshold.