October 17, 2017 at 4:06 am
Morning All,
I am trying to get a query to show me the results of jobs in the last 24 hours.
I want to see PER FAILED JOB:
The count of failures
The last outcome (be it fail or success or retry or cancelled)
I am trying to (ideally) do this in one statement. Without using CTE's or Temp Tables.
I am not sure if its even possible, but gut feeling is that I need to use Partition/Over etc. But can't quite make it work.
So far I've got the following tsql (that shows failed/retry jobs per Job):
SELECT
count(*) as total_failures,
j.name as JobName
FROM
msdb..sysjobs j (nolock)
INNER JOIN
msdb..sysjobhistory jh (nolock) ON j.job_id = jh.job_id
WHERE
CONVERT(datetime, dbo.agent_datetime(run_date, run_time), 121) > DATEADD(HOUR, -24, GETDATE())
AND run_status in (0,3)
group by j.name
But I am not sure how to get the most RECENT outcome be it success or fail.
I know that to use the partition function for this I'd need to remove the where clause on run_status.
Really what I am looking for is a where clause per partition statement.
The end result set I am hoping to look like
job_name
failure_count
last_outcome
last_rundate (optional)
As always any help is most appreciated.
Cheers
Alex
October 17, 2017 at 8:30 am
alex.sqldba - Tuesday, October 17, 2017 4:06 AM...
The end result set I am hoping to look likejob_name
failure_count
last_outcome
last_rundate (optional)As always any help is most appreciated.
Cheers
Alex
This is not a where clause per partition statement, but I think it will do what you're looking for. I included a total_cancellations column because you were filtering on a run_status of 0 or 3
SELECT
job_name = j.name,
total_failures = SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END),
total_cancellations = SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END),
last_outcome = max(run_status),
last_rundate = max(CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time), 121))
FROM
msdb..sysjobs j (nolock)
INNER JOIN
msdb..sysjobhistory jh (nolock) ON j.job_id = jh.job_id
WHERE
CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time), 121) > DATEADD(HOUR, -24, GETDATE())
AND run_status IN (0,3)
AND step_id = 0
group by j.name
October 17, 2017 at 8:38 am
Well, I never knew you could put a case statement within a sum() ... AMAZING!!!
Thanks so much!
October 17, 2017 at 8:58 am
For completeness and for anyone following or on a similar mission to me, below is how I ended up using SqlPirate's (above) T-SQL.
select
job_name,
total_failures,
total_cancellations,
case last_outcome
when 0 then 'Fail'
when 1 then 'Success'
when 2 then 'Retry'
when 3 then 'Cancel'
end as last_run_result
from
(
SELECT
job_name = j.name,
total_failures = SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END),
total_cancellations = SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END),
last_outcome = max(run_status),
last_rundate = max(CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time), 121))
FROM
msdb..sysjobs j (nolock)
INNER JOIN
msdb..sysjobhistory jh (nolock) ON j.job_id = jh.job_id
WHERE
CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time), 121) > DATEADD(HOUR, -24, GETDATE())
AND step_id = 0
group by j.name
) as job_states
where total_failures > 0
October 17, 2017 at 10:46 am
That's handy. I'm saving that.
October 17, 2017 at 3:51 pm
I don't think the logic for last_outcome is correct. Maybe something like this:SELECT job_name = j.name, run_count, total_failures, total_cancellations, run_status AS last_run_status, run_datetime AS last_run_datetime
FROM
(SELECT job_id,
msdb.dbo.agent_datetime(run_date,run_time) AS run_datetime,
COUNT(*) OVER (PARTITION BY job_id) AS run_count,
total_failures = SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY job_id),
total_cancellations = SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY job_id),
CASE run_status WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Failed' END AS run_status,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS recentness
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
AND dbo.agent_datetime(run_date, run_time) > DATEADD(HOUR, -24, GETDATE())) jh
INNER JOIN msdb.dbo.sysjobs AS j ON jh.job_id = j.job_id
WHERE total_failures > 0
AND recentness = 1;
October 17, 2017 at 4:14 pm
alex.sqldba - Tuesday, October 17, 2017 8:38 AMWell, I never knew you could put a case statement within a sum() ... AMAZING!!!Thanks so much!
It's called a CROSS TAB. You can read more about that ancient but still incredibly useful SQL Black Art at the following link. And, yes, another name for a CROSS TAB is a "Pivot" without using the PIVOT operator.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2017 at 5:05 pm
Chris Harshman - Tuesday, October 17, 2017 3:51 PMI don't think the logic for last_outcome is correct. Maybe something like this:SELECT job_name = j.name, run_count, total_failures, total_cancellations, run_status AS last_run_status, run_datetime AS last_run_datetime
FROM
(SELECT job_id,
msdb.dbo.agent_datetime(run_date,run_time) AS run_datetime,
COUNT(*) OVER (PARTITION BY job_id) AS run_count,
total_failures = SUM(CASE WHEN run_status = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY job_id),
total_cancellations = SUM(CASE WHEN run_status = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY job_id),
CASE run_status WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' ELSE 'Failed' END AS run_status,
ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS recentness
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
AND dbo.agent_datetime(run_date, run_time) > DATEADD(HOUR, -24, GETDATE())) jh
INNER JOIN msdb.dbo.sysjobs AS j ON jh.job_id = j.job_id
WHERE total_failures > 0
AND recentness = 1;
Thank you Chris, you put the schema name for the tables. Yes, you can use .. instead of .dbo. but that is called differed binding and depending on your environment, it may fail to give you the results you expect. You should specify the schema of the tables in your queries.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply