SQL Server Jobs Monitor

Monitor Sql Server Jobs

ALTER PROCEDURE [dbo].[spETLMonitor] As

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test].[dbo].[Raw_tblETLMonitor]') AND type in (N'U'))
DROP TABLE [Test].[dbo].[Raw_tblETLMonitor]

declare @session_id INT
declare @session_id_as_char NVARCHAR(16)

IF @session_id IS NULL
  SELECT TOP(1) @session_id = session_id FROM msdb.dbo.syssessions ORDER by agent_start_date DESC
  ELSE IF NOT EXISTS( SELECT * FROM msdb.dbo.syssessions WHERE session_id = @session_id)
  BEGIN
    SELECT @session_id_as_char = CONVERT(NVARCHAR(16), @session_id)
    RAISERROR(14262, -1, -1, '@session_id', @session_id_as_char)
  END

Declare @enum_job Table (
JobID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)      



insert into @enum_job exec master.dbo.xp_sqlagent_enum_jobs 1,garbage
SELECT  a.job_id As JobId,
        case when charindex('_',b.name) > 0 then
            left(b.name,charindex('_',b.name)-1)
            else b.name
       end [AccountCode],
       right(b.name,len(b.Name)-charindex('_',b.name)) [ProcessArea],
       b.name [JobName],
       a.start_execution_date   [LastStartDate],    
       a.stop_execution_date    [LastStopDate],
       Case When a.stop_execution_date Is Null Then
       Cast(DateDiff(ss,a.start_execution_date ,Getdate())/60.0 As Decimal(28,2))
       Else
       Cast(DateDiff(ss,a.start_execution_date ,a.stop_execution_date)/60.0 As Decimal(28,2))
       End As ElapsedTime,
       c.message [Message],
       case d.State
   when 1 then 'Executing'
   when 2 then 'Waiting for thread'
   when 3 then 'Between retries'
   when 5 then 'Suspended'
   when 7 then 'Performing completion actions'
    Else
       case c.run_status
            when 0 then 'Failed'
            when 1 then 'Succeeded'
            when 3 then 'Canceled'
            when 5 then 'Unknown'
       end end as [RunStatus],
       a.next_scheduled_run_date [NextScheduledRunDate],
       c.step_id [StepID],
       c.retries_attempted [RetriesAttempted],'AMER' As Region,
       c.server  as HostName   Into test.dbo.Raw_tblETLMonitor
FROM   msdb.dbo.sysjobactivity a
       LEFT JOIN msdb.dbo.sysjobhistory c ON a.job_history_id = c.instance_id
       join msdb.dbo.sysjobs_view b on a.job_id = b.job_id
       Join @enum_job d On a.job_id = d.JobId
where  a.session_id = @session_id
order by b.name