Reputation: 175
I need to make a query that gets the the status of sql server agent jobs.
The tricky part, is that I both want the time the job last have failed (if any) AND when it last ran successful - is that possible?
So far I have only been able to get the last successful run or the last failed run of a job.
SELECT
job.name
,jobh.run_date
,DATEADD(HOUR, (jobh.run_time / 1000000) % 100,
DATEADD(MINUTE, (jobh.run_time / 10000) % 100,
DATEADD(SECOND, (jobh.run_time / 100) % 100,
DATEADD(MILLISECOND, (jobh.run_time % 100) * 10,
cast('00:00:00' as TIME(0)))))) AS 'tidspunkt'
,jobh.run_duration
FROM msdb.dbo.sysjobhistory jobh
JOIN [msdb].[dbo].[sysjobs] job ON jobh.job_id = job.job_id
WHERE jobh.step_id = 0
AND jobh.message LIKE '%failed%'
AND (jobh.run_date = CONVERT(VARCHAR, GETDATE(), 112) OR jobh.run_date = CONVERT(VARCHAR, DATEADD(DAY, -1, GETDATE()), 112) )
AND job.name = 'UpdateCPR'
ORDER BY jobh.run_date DESC, jobh.run_time DESC
Upvotes: 1
Views: 66
Reputation: 5646
Just change the line...
AND jobh.message LIKE '%failed%'
...to following...
AND (jobh.message LIKE '%failed%' OR jobh.message LIKE '%succeeded%')
You will get multiple rows. If you wish, you can group that by LIKE result and select TOP 1 from both to retrieve it in one row.
EDIT
Use this to get both fail and success info in one row.
;WITH jobRuns AS (
SELECT
job.job_id,
job.name,
jobh.run_time,
jobh.run_duration,
execStatus
FROM msdb.dbo.sysjobhistory jobh
JOIN msdb.dbo.sysjobs job
ON jobh.job_id = job.job_id
CROSS APPLY(
SELECT execStatus = CASE
WHEN jobh.message LIKE '%failed%' THEN 0
WHEN jobh.message LIKE '%succeeded%' THEN 1 END) ext
WHERE
jobh.step_id = 0
and job.name LIKE '%testjob%'
/*GROUP BY
job.job_id,
job.name,
jobh.run_time,
execStatus*/)
,lastRuns AS (
SELECT TOP 1
jobRuns.job_id,
jobRuns.name,
run_time_failed = failed.run_time,
run_duration_failed = failed.run_duration,
run_time_succeeded = success.run_time,
run_duration_succeeded = success.run_duration
FROM jobRuns
CROSS APPLY(
SELECT TOP 1 run_time, run_duration
FROM jobRuns
WHERE execStatus = 0
ORDER BY run_time DESC) failed
CROSS APPLY(
SELECT TOP 1 run_time, run_duration
FROM jobRuns
WHERE execStatus = 1
ORDER BY run_time DESC) success
)
SELECT *
FROM lastRuns
Upvotes: 2