This template will allow you to monitor the state of your SQL Server Agent Jobs. You will need to specify the agent job name within the body of the SQL Query. You will need the proper credentials to execute against the db. Also, specify SQL Server Instance and DB name.
The query is checking the last_run_outcome for the job specified. Statistical values will return the following based on the outcome of the job the last time it ran.
0 = Failed
3 = Canceled
5 = Unknown
What version of SQL are you needing to monitor the SQL agent job status of? If it's SQL 2005 or earlier, I would recommend using the Application Template contained in this posting above -> SQL AGENT…
This template has been superseded by AppInsight for SQL included in the Server & Application Monitor (SAM) 6.0 RC available now!
Sign-up here and try it for yourself!