SQL Agent Job Logs - Can't get the Error Log
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 &gt;= 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 &lt;&gt; 'Succeeded';