SQL Agent Job Logs - Can't get the Error Log

I've some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to get that information with this query:

```
DECLARE @job_id UNIQUEIDENTIFIER

SELECT
@job_id = job_id FROM msdb.dbo.sysjobs
WHERE
[name] like '%JOB_1%'
OR [name] like '%JOB_2%'
OR [name] like '%JOB_3%'
OR [name] like '%JOB_4%'
OR [name] like '%JOB_5%'

;WITH Error_Output (job_id, error_log) AS
(
SELECT
JS.job_id,
CASE
WHEN JSL.[log] IS NULL THEN JH.[Message]
ELSE JSL.[log]
END AS LogOutput
FROM
msdb.dbo.sysjobsteps JS
INNER JOIN msdb.dbo.sysjobhistory JH
ON JS.job_id = JH.job_id AND JS.step_id = JH.step_id
LEFT OUTER JOIN msdb.dbo.sysjobstepslogs JSL
ON JS.step_uid = JSL.step_uid
WHERE
INSTANCE_ID >
(SELECT
MIN(INSTANCE_ID)
FROM
(SELECT top (2)
INSTANCE_ID, job_id
FROM
msdb.dbo.sysjobhistory
WHERE
job_id = @job_id
AND STEP_ID = 0
ORDER BY
INSTANCE_ID desc
) A
)
AND JS.step_id <> 0
AND JH.job_id = @job_id
AND JH.run_status = 0
)
SELECT
sj.name AS [Job Name]
,MAX(sja.run_requested_date) AS [Start Date]
,MAX(sja.start_execution_date) AS [Effective Start Date]
,MAX(sja.stop_execution_date) AS [Effective End Date]
,DATEDIFF(MINUTE, MAX(sja.start_execution_date), MAX(sja.stop_execution_date)) as [Job Execution Time - M]
,CASE WHEN error_log IS NULL THEN 'Job runs successfully' else error_log end as [Job Log]
FROM
msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
LEFT OUTER JOIN Error_Output AS error ON error.job_id = sja.job_id AND error.job_id = sj.job

I have written a post about retrieving job status from msdb, which you can see here. The code in that post does not pull in data from dbo.sysjobstepslogs, but it's pretty straightforward to extend that code to pull in the last error message from dbo.sysjobstepslogs if you are logging to that table.

By default, job steps do not log to msdb. To configure your job steps to log to dbo.sysjobstepslogs, you must go into the job step properties for every step in the job, visit the "Advanced" tab, and select the option for "Log to table":

You can then create this Table-Valued Function to get the most recent job status. I've modified the code from the post above to also pull the most recent error from the job, if it is available:

CREATE FUNCTION dbo.SqlAgentJob_GetStatus (@JobName sysname)
     RETURNS TABLE
 AS
 RETURN
 SELECT TOP 1
     JobName        = j.name,
     IsRunning      = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL
                          THEN 1 ELSE 0 
                        END,
     RequestSource  = ja.run_requested_source,
     LastRunTime    = ja.start_execution_date,
     NextRunTime    = ja.next_scheduled_run_date,
     LastJobStep    = js.step_name,
     RetryAttempt   = jh.retries_attempted,
     JobLastOutcome = CASE
                        WHEN ja.job_id IS NOT NULL
                            AND ja.stop_execution_date IS NULL THEN 'Running'
                        WHEN jh.run_status = 0 THEN 'Failed'
                        WHEN jh.run_status = 1 THEN 'Succeeded'
                        WHEN jh.run_status = 2 THEN 'Retry'
                        WHEN jh.run_status = 3 THEN 'Cancelled'
                      END,
     LastError = (SELECT TOP 1 jsl.log 
                 FROM msdb.dbo.sysjobstepslogs jsl 
                 WHERE jsl.step_uid = js.step_uid
                 AND jsl.date_modified >= ja.start_execution_date)
 FROM msdb.dbo.sysjobs j
 LEFT JOIN msdb.dbo.sysjobactivity ja 
     ON ja.job_id = j.job_id
        AND ja.run_requested_date IS NOT NULL
        AND ja.start_execution_date IS NOT NULL
 LEFT JOIN msdb.dbo.sysjobsteps js
     ON js.job_id = ja.job_id
        AND js.step_id = ja.last_executed_step_id
 LEFT JOIN msdb.dbo.sysjobhistory jh
     ON jh.job_id = j.job_id
        AND jh.instance_id = ja.job_history_id
 WHERE j.name = @JobName
 ORDER BY ja.start_execution_date DESC;
 GO

Finally, you can leverage the TVF to query for your job status, last step to run, last logged error, etc:

--Specific jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE 
        j.[name] like '%JOB_1%'
     OR j.[name] like '%JOB_2%'
     OR j.[name] like '%JOB_3%'
     OR j.[name] like '%JOB_4%'
     OR j.[name] like '%JOB_5%';
 --Failed Jobs
 SELECT sts.*
 FROM msdb.dbo.sysjobs j
 CROSS APPLY dbo.SqlAgentJob_GetStatus (j.name) sts
 WHERE sts.JobLastOutcome <> 'Succeeded';

StackExchange Database Administrators Q#177204, answer score: 4