Creating a chart with SSDT to show how many jobs are running at all times

Question

Am trying to find away to plot a chart/graph to show how many jobs are running at any given time through the day, when jobs are starting is easy, but the idea is that it should show how long that it is taking along with this.

I've got a Query which runs through all jobs gets their start time, their duration, I've calculated the end time if that's needed, but I cant seem to be able to put that on the screen for people to be able to go, oh yea there was 5 long running jobs happening here and none here.

Most of the jobs take next to no time at all but we do have a few which take a few minutes and few overnight which take potentially over an hour.

The basic code I'm using to grab the information:

    WITH CTE as(
        select run_duration
        ,cast(concat(cast(run_date as varchar(8)),' ',substring(right('00000'+cast(run_time as varchar(8)),6),1,2),':',
            substring(right('000'+cast(run_time as varchar(6)),4),1,2),':',
            substring(right(run_time,2),1,2))as datetime) as [starttime]
        FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
        WHERE run_duration >0
    )
    SELECT 1 as [running],run_duration,starttime,DATEADD(ss,run_duration,[starttime]) AS [endTime]
    FROM CTE 
    WHERE  [starttime] > (GETUTCDATE() - 1)
    ORDER BY [starttime] DESC

Any help / clues as how I should go about this would be appreciated (Creating a separate admin table is a possibility if required.

Attached link to image of a very rough idea of what I'm after http://stephen-bov.co.uk/images/JobsRunning.png

Thanks Ste

Answer 1

New code refinment, now takes into consideration jobs that are still running, and also jobs that were still running but started before the date-1 (also runs a lot faster)

IF OBJECT_ID('tempdb..#jobtimes') IS NOT NULL
   DROP TABLE #jobtimes
IF OBJECT_ID('tempdb..#jobhis') IS NOT NULL
   DROP TABLE #jobhis

SELECT instance_ID
    ,(substring(right('00000'+cast(run_duration as varchar(8)),6),1,2)*60*60) +
        (substring(right('000'+cast(run_duration as varchar(6)),4),1,2)*60) +
        substring(right(run_duration,2),1,2)
        as run_duration
    ,cast(concat(cast(run_date as varchar(8)),' ',substring(right('00000'+cast(run_time as varchar(8)),6),1,2),':',
        substring(right('000'+cast(run_time as varchar(6)),4),1,2),':',
        substring(right(run_time,2),1,2))as datetime) as [startingtime]
    INTO #jobhis
   FROM msdb.dbo.sysjobhistory WITH (NOLOCK) 
   WHERE 
run_duration >0 and
step_id = 0 and
cast(run_date as varchar(8)) > (GETDATE() - 2 )
    UNION
SELECT 1,datediff(second,start_execution_date,GETdate()),start_execution_date 
    FROM msdb.dbo.sysjobactivity with(nolock) 
    WHERE run_requested_date is not null 
        and stop_execution_date is null 
        and start_execution_date > GETUTCDATE()-2


SELECT 1 as running,run_duration, instance_ID, startingtime, dateadd(ms,-10,DATEADD(ss,run_duration,startingtime)) as endtime
        into #jobtimes
    FROM #jobhis
    WHERE  dateadd(ms,-10,DATEADD(ss,run_duration,startingtime)) > (GETDATE() -1)
UNION 
SELECT NULL as running ,0 as run_duration, 0 as instance_ID, DATEADD(ss,run_duration,startingtime) as startingtime, NULL as endtime
    FROM #jobhis
    WHERE  [startingtime] > (GETDATE() - 1 )

SELECT p1.startingtime,count(p2.running) as instances 
    FROM #jobtimes p1
        LEFT JOIN #jobtimes p2 ON 
            p2.running =1 
            AND p1.startingtime between  p2.startingtime and p2.endtime
    WHERE p1.startingtime > (getdate()-1)
    GROUP BY p1.startingtime,p1.run_duration

DROP TABLE #jobtimes

DROP TABLE #jobhis

---

Old answer for reference

Update for being able to detect the end of each job aswell so the graph will go down to 0 once all of the jobs have finished or just go down to however many is remaining when one finished,

we've got 27k jobs a day running that take atleast a second and running the query straight in management studio takes around a minute and a half, running in SSDT its taking less than 10 seconds (dont know if its caching a lot of the information)

 WITH CTE AS(
    SELECT instance_ID
    ,(substring(right('00000'+cast(run_duration as varchar(8)),6),1,2)*60*60) +
        (substring(right('000'+cast(run_duration as varchar(6)),4),1,2)*60) +
        substring(right(run_duration,2),1,2)
        as run_duration
    --,msdb.dbo.agent_datetime(run_date,run_time) as startingtime
    ,cast(concat(cast(run_date as varchar(8)),' ',substring(right('00000'+cast(run_time as varchar(8)),6),1,2),':',
        substring(right('000'+cast(run_time as varchar(6)),4),1,2),':',
        substring(right(run_time,2),1,2))as datetime) as [startingtime]
    FROM msdb.dbo.sysjobhistory WITH (NOLOCK) 
    WHERE run_duration >0 and cast(run_date as varchar(8)) > (GETUTCDATE() - 2 )
), Pri AS
(
SELECT 1 as running,run_duration, instance_ID, startingtime, dateadd(ms,-10,DATEADD(ss,run_duration,startingtime)) as endtime
FROM CTE 
WHERE  [startingtime] > (GETUTCDATE() - 1 )
UNION 
SELECT NULL as running ,0 as run_duration, 0 as instance_ID, DATEADD(ss,run_duration,startingtime) as startingtime, NULL as endtime
FROM CTE 
WHERE  [startingtime] > (GETUTCDATE() - 1 )
)
SELECT p1.startingtime,count(p2.running) as instances FROM Pri p1
LEFT JOIN Pri p2 ON 
    p2.running =1 AND p1.startingtime between  p2.startingtime and p2.endtime
    GROUP BY p1.startingtime,p1.run_duration
order by startingtime

Hope anyone else who would want to see this will find this useful

Ste