November 8, 2022 at 10:18 am
I am currently trying to write a query that tells me when a SQL agent job ran last and also how long it took. This is my code currently
with cte2
As (select j.name as 'JobName',
j.job_id as 'JobID',
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
run_duration as 'RunDurationSeconds'
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
--and j.name = 'TestJob' --Uncomment to search for a single job
)
Select cte.JobName,
cte.JobID,
--RunDateTime as [most recent run],
max (cte.RunDateTime) as [most recent run],
cte.RunDurationSeconds
from cte2 as cte
where
1=1
--and RunDateTime =
--(
-- Select max(rundatetime) from cte2
--)
Group by
cte.JobName,
cte.JobID,
cte.RunDurationSeconds
The MAX does not seem to be running and is bringing back the time it took over different days. When I uncomment the sub query in the where clause it just returns the latest job that ran.
What do I need to do to the query to bring back
Job name
Job id
Latest date/time it ran
how long that took
November 9, 2022 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 14, 2022 at 1:13 am
Here's what you asked for, along with a last run status...
WITH
cteJob AS
(--==== Get only the jobs that are enabled
SELECT job_id ,name
FROM msdb.dbo.sysjobs job
WHERE job.enabled = 1 --Only jobs that are enabled
)
,cteLatest AS
(--==== Setup to pick only the latest history row for each job. SortOrder = 1 for latest run for each job_id.
SELECT SortOrder = ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC)
,job_id, run_status, run_date, run_time, run_duration
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0 --Only the top level reporting for each job
)--==== Join the two CTEs to produce the desired report with a RunStatus added.
SELECT JobName = j.name
,JobID = j.job_id
,RunStatus = CASE l.run_status --I don't use CHOOSE because it's slower than CASE
WHEN 1 THEN 'Succeeded' --Short Ciruit the CASE because most will have a run_status = 1
WHEN 0 THEN 'Failed'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
ELSE 'UNKNOWN'
END
,RunDateTime = msdb.dbo.agent_datetime(l.run_date,l.run_time)
,RunDurationSeconds = l.run_duration/10000*3600+l.run_duration/100%100*60+l.run_duration%100
FROM cteJob j
JOIN cteLatest l ON l.job_id = j.job_id
WHERE l.SortOrder = 1 --Only the latest run
ORDER BY JobName
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2023 at 8:40 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply