March 7, 2017 at 12:43 pm
Hi guys, I need to get a list of the top 10 jobs that failed the more during the last month (last 4 weeks).
I need:
- JobName
- Week of the month
- Total amount of executions
- Total amount of executions that failed
I can get all of the data from the sysjobs and sysjobshistory tables, but not sure how to handle and make the conversion to get the week of the month.
Could you please help me with this?
Thanks!!
March 7, 2017 at 1:14 pm
I'd compute the integer version of the date beginning of 4 weeks ago first, then compare that to the run_date, something like:
DECLARE @first_date int = CONVERT(int, CONVERT(varchar(8), DATEADD(wk, DATEDIFF(wk, 0, GetDate()) - 4, 0),112));
SELECT j.name, jh.step_id, jh.step_name, jh.sql_message_id, jh.sql_severity, jh.message,
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS run_datetime, run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 AS run_duration
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= @first_date
AND (jh.run_status = 0 OR jh.message like '%error:%')
AND jh.step_id > 0
ORDER BY jh.run_date DESC, jh.run_time DESC;
March 7, 2017 at 1:15 pm
Do you have a Calendar Table available? That would be ideal, but lacking same, how would you define your weeks? Either way, however, if you insist on dividing a month into weeks, not every month is going to have the same number of weeks, which can be darned inconvenient for measuring purposes, especially when you start looking at historical information. There are some good alternatives, such as using ISO week numbers, or at least week of the year, but as long as both monthly and weekly reporting have to be together, the fact that the calendar isn't very convenient will be a constant source of grief. Consider separately reporting weekly stats from monthly stats, and things get a lot easier.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 1:27 pm
You need to define a "week", that is, what day do you consider to be the start of a week?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2017 at 1:27 pm
OK, I must need more caffeine, I totally missed the expected results, try this?
DECLARE @first_date int = CONVERT(int, CONVERT(varchar(8), DATEADD(wk, DATEDIFF(wk, 0, GetDate()) - 4, 0),112));
SELECT j.name, DATEADD(wk, DATEDIFF(wk, 0, msdb.dbo.agent_datetime(jh.run_date,jh.run_time)),0) AS beg_of_week,
COUNT(*) AS exec_count,
SUM(CASE WHEN (jh.run_status = 0 OR jh.message like '%error:%') THEN 1 ELSE 0 END) AS fail_count
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= @first_date
-- AND (jh.run_status = 0 OR jh.message like '%error:%')
AND jh.step_id = 0
GROUP BY j.name, DATEADD(wk, DATEDIFF(wk, 0, msdb.dbo.agent_datetime(jh.run_date,jh.run_time)),0)
ORDER BY beg_of_week, j.name;
March 9, 2017 at 8:44 am
I got it working! Thanks guys for all the help! 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply