Short post today. Simple query that will tell you every job that ran between two datetime stamps. Note: this of course assumes that the data exists. Job history is only kept for so long so depending on your settings the data may already have been deleted. Also if a job is currently running it’s first step it’s not going to show up in the history table so obviously it won’t show up in the results for this query.
USE msdb
GO
DECLARE @StartCheckDateTime DATETIME = '2/1/2021 1:00 AM'
DECLARE @EndCheckDateTime DATETIME = '2/1/2021 2:59 AM'
SELECT sysjobs.name AS JobName, sysjobhistory.step_name AS JobStepName, sysjobhistory.step_id AS JobStepId,
Vars.StartDateTime, Vars2.EndDateTime,
*
FROM msdb.dbo.sysjobhistory
JOIN msdb.dbo.sysjobs
ON sysjobhistory.job_id = sysjobs.job_id
CROSS APPLY (SELECT msdb.dbo.agent_datetime(sysjobhistory.run_date, sysjobhistory.run_time) AS StartDateTime,
(sysjobhistory.run_duration / 10000 * 3600)/*Hours*/ +
((sysjobhistory.run_duration % 10000) / 100 * 60)/*Minutes*/ +
(sysjobhistory.run_duration % 100) /*Seconds*/ AS RunDurationSec) AS Vars
CROSS APPLY (SELECT DATEADD(SECOND, Vars.RunDurationSec,Vars.StartDateTime) AS EndDateTime) AS Vars2
WHERE Vars.StartDateTime <= @EndCheckDateTime
AND Vars2.EndDateTime >= @StartCheckDateTime
AND sysjobhistory.step_id <> 0