Stored Prodedure for SQL Server Agent Job Statistics and Status

There is no inbuilt view or SP for SQL Server Agent Job Statistics, for example in which state the job is, how much time it is taking. I created this Stored Procedure which will provide information related to the Jobs i.e. Job run date & time, job status, elapsed time, server name, error message (if job throws any error). Please execute the given SP below and follow the instruction to run it on your server.

1. Do not pass any filter if you want to see All Jobs (Succeeded, errored, running etc) 

    SQL Command to run -- Exec exec usp_Sql_Job_Status 

2. Pass 0 if you want to see only Error Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 0

3. Pass 1 if you want to see only Succeeded Job
 

    SQL Command to run -- Exec exec usp_Sql_Job_Status 1

4. Pass 2 if you want to see only Retry Job
 


    SQL Command to run -- Exec exec usp_Sql_Job_Status 2


5. Pass 3 if you want to see only Canceled Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 3


6. Pass 4 if you want to see only Running Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 4

CREATE PROCEDURE [dbo].[usp_Sql_Job_Status] 
    -- Add the parameters for the stored procedure here 
    @paramstatus int=Null 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 
 
    -- Insert statements for procedure here 
    SELECT * FROM ( 
            SELECT  
                RunDate=DATEADD(SECOND, CASE WHEN LEN(run_time)=1 then RIGHT(CAST(run_time AS VARCHAR(6)),1) 
                                             WHEN LEN(run_time)=2 then RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=3 then LEFT(CAST(run_time AS VARCHAR(6)),1)*60+RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=4 then LEFT(CAST(run_time AS VARCHAR(6)),2)*60+RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=5 then LEFT(CAST(run_time AS VARCHAR(6)),1)*60*60+SUBSTRING(CAST(run_time AS VARCHAR(6)),2,2)*60 
                                                                                                                +RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                            WHEN LEN(run_time)=6 then LEFT(CAST(run_time AS VARCHAR(6)),2)*60*60+SUBSTRING(CAST(run_time AS VARCHAR(6)),3,2)*60 
                                                                                                                +RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                        END,  
                                CONVERT(DATETIME, CONVERT(CHAR(8), run_date))), 
                JobName=b.name, 
                StatusCode=run_status, 
                RunStatus=CASE WHEN run_status=0 THEN 'Error'  
                                WHEN run_status=1 THEN 'Succeeded' 
                                WHEN run_status=2 THEN 'Retry' 
                                WHEN run_status=3 THEN 'Canceled'  
                                WHEN run_status=4 THEN 'Running'  
                                ELSE 'Unknown' END, 
                ElapsedTime=run_duration, 
                StepName=step_name, 
                SqlMessageId=sql_message_id, 
                SqlSeverity=sql_severity, 
                [Message]=[message], 
                [Server]=[server] 
            FROM  
                msdb.[dbo].[sysjobhistory] a  
                JOIN  
                    msdb.[dbo].[sysjobs] b  
                ON a.job_id=b.job_id 
        UNION 
            SELECT  
                RunDate=jact.run_requested_date, 
                JobName=jv.name, 
                StatusCode=4, 
                RunStatus='Running', 
                ElapsedTime=DATEDIFF( SECOND, jact.run_requested_date, GETDATE() ) , 
                Null, 
                Null, 
                Null, 
                Null, 
                [Server]=jv.originating_server 
            FROM  
                msdb.dbo.sysjobs_view jv 
                JOIN 
                    msdb.dbo.sysjobactivity jact 
                ON  
                    jv.job_id = jact.job_id 
                JOIN 
                    msdb.dbo.syssessions ss 
                    ON 
                    ss.session_id = jact.session_id 
                JOIN 
                    ( 
                        SELECT 
                            MAX( agent_start_date ) AS max_agent_start_date 
                        FROM 
                            msdb.dbo.syssessions 
                    ) sess 
                    ON 
                        ss.agent_start_date = sess.max_agent_start_date 
                WHERE  
                    run_requested_date IS NOT NULL AND stop_execution_date IS NULL 
         
 )QUERY 
 WHERE (StatusCode=@paramstatus) OR (-1=isnull(@paramstatus,-1)) 
 ORDER BY RunDate DESC  
END 

cheers,