November 2, 2017 at 3:12 am
Morning All,
So this place is very job-centric, alot of the processes happen overnight and as jobs they run just fine.
What isn't so straightforward is alerting each stakeholder about the status of their job.
I find adding and editing operators clunky and requires too much editing as people start and leave.
Has anyone found a way to trigger an email on any job completing and being able to identify the job, look it up in a table and pick out a list of 'subscribers'
The latter half of that is fairily trivial. What I am struggling to achieve is either the jobs to be aware of themselves (in that I can pass the current executing job name into a proc) or a generic message from Agent saying 'this job ### has completed ###' where ### is the job name and outcome respectively.
I am happy to use SMO if that will help
Cheers!
Alex
November 2, 2017 at 4:48 am
When setting up the SQL Server scheduled jobs, what you could do is add a final step to each one along the lines of:
declare @JobName varchar(100)
select @JobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))
exec job_status_report @JobName
and have that job_status_report stored procedure take the @JobName parameter and look up the details in msdb.dbo.sysjobhistory for the current run, and build that into the email you want...
(This is all thinking out loud, as it were, rather than tried and tested - apart from the whole bit about getting the job name from the scheduled task... that works - we use it a lot here.)
Some documentation about Using Tokens in Job Steps.
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 2, 2017 at 6:12 am
Wow, that's awesome -- the $(ESCAPE_NONE(JOBID)).
The only problem I can foresee with adding this as a last step is that during the time it looks up the status of that job, the job will still be running looking up the status of itself? So there wont be a job completed row in the history until after this step completes. Does that make sense?
November 2, 2017 at 6:54 am
Yes, that makes sense; however, you can look at the msdb.dbo.sysjobhistory table for the results of the individual steps; combine that with the run-time information from msdb.dbo.sysjobactivity to pull the date/time information to ensure you're only pulling information for the currently-running incarnation of that job. And that should sort you out (or at least get you most of the way there).
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 2, 2017 at 7:14 am
ThomasRushton - Thursday, November 2, 2017 6:54 AMYes, that makes sense; however, you can look at the msdb.dbo.sysjobhistory table for the results of the individual steps; combine that with the run-time information from msdb.dbo.sysjobactivity to pull the date/time information to ensure you're only pulling information for the currently-running incarnation of that job. And that should sort you out (or at least get you most of the way there).
Here's a query that'll help with some of that...
SELECT j.name,
a.session_id,
a.run_requested_date,
a.last_executed_step_id,
a.last_executed_step_date,
GETDATE() AS CurrentDateTime,
DATEDIFF(SECOND, a.start_execution_date, GETDATE()) AS RunTimeInSeconds,
jh.step_name,
jh.step_id,
jh.run_status,
jh.run_duration,
jh.message,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS jobstepdatetime
FROM msdb.dbo.sysjobactivity a
INNER JOIN msdb.dbo.sysjobs j
ON j.job_id = a.job_id
LEFT JOIN msdb.dbo.sysjobhistory jh
ON jh.job_id = j.job_id
WHERE a.start_execution_date IS NOT NULL
AND a.stop_execution_date IS NULL
AND a.session_id =
(
-- latest agent execution session id
SELECT TOP 1
session_id
FROM msdb.dbo.syssessions s
ORDER BY s.agent_start_date DESC
)
AND a.run_requested_source = 1
AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= a.start_execution_date
AND j.name = @JobName -- or whatever your parameter is...
ORDER BY j.name,
msdb.dbo.agent_datetime(jh.run_date, jh.run_time); -- jobstepdatetime;
Thomas Rushton
blog: https://thelonedba.wordpress.com
November 2, 2017 at 8:13 am
alex.sqldba - Thursday, November 2, 2017 3:12 AMMorning All,So this place is very job-centric, alot of the processes happen overnight and as jobs they run just fine.
What isn't so straightforward is alerting each stakeholder about the status of their job.I find adding and editing operators clunky and requires too much editing as people start and leave.
Has anyone found a way to trigger an email on any job completing and being able to identify the job, look it up in a table and pick out a list of 'subscribers'
The latter half of that is fairily trivial. What I am struggling to achieve is either the jobs to be aware of themselves (in that I can pass the current executing job name into a proc) or a generic message from Agent saying 'this job ### has completed ###' where ### is the job name and outcome respectively.
I am happy to use SMO if that will help
Cheers!
Alex
Users coming and going can usually be handled by creating distribution groups in your mail system. I'm making a guess that there is an AD group for, let's say, "Accounting". As users are moved into and out of the AD groups, your operators do not need to change.
If someone wants, or doesn't want, to get an email. that gets handed off to the network admins to make the appropriate changes.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 2, 2017 at 9:15 am
Hi Michael,
Unfortunately our Administrators are spread pretty thin, and as we have over two thousand jobs (over multiple instances) and we also have users who want to opt-in to some reports and out of others as their priorities change and as their job titles changes (they hot desk alot here) I am trying to get a system in place where by they can subscribe and unsubscribe by themselves. A front end is a trivial task that populates a table with yes/no/email address sort of thing.
November 2, 2017 at 9:30 am
Cheers Thomas!
November 2, 2017 at 10:14 am
alex.sqldba - Thursday, November 2, 2017 9:15 AMHi Michael,Unfortunately our Administrators are spread pretty thin, and as we have over two thousand jobs (over multiple instances) and we also have users who want to opt-in to some reports and out of others as their priorities change and as their job titles changes (they hot desk alot here) I am trying to get a system in place where by they can subscribe and unsubscribe by themselves. A front end is a trivial task that populates a table with yes/no/email address sort of thing.
Ok, here is a thought. It's pretty kludgy, and can certainly create some security holes if not done properly. I had a similar situation in a previous position.
What I did was create a set of tables that contained all of the reports that a person could subscribe to. I then created an SSRS report that when you went to it, it had a multi-select drop down of all the reports. I user picked the report(s) that they wanted to subscribe to or un-subscribe from. There was another text parameter where they typed in their email address.
When they ran the report, it validated the email address against AD. If that was good, it inserted or deleted the reports they decided to subscribe to from a "subscription" table.
At the end of the process, it sent a confirmation email to the user and generated the SSRS report. I originally sent a copy to the DBA group, but that got crazy. I changed that to a nightly report that ran for the previous day.
The "right" way to do this would be to create a web site for subscriptions. But the SSRS report got it done as a quick fix.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 3, 2017 at 6:15 pm
ThomasRushton - Thursday, November 2, 2017 4:48 AMWhen setting up the SQL Server scheduled jobs, what you could do is add a final step to each one along the lines of:
declare @JobName varchar(100)
select @JobName = name from msdb..sysjobs where job_id = $(ESCAPE_NONE(JOBID))
exec job_status_report @JobNameand have that job_status_report stored procedure take the @JobName parameter and look up the details in msdb.dbo.sysjobhistory for the current run, and build that into the email you want...
(This is all thinking out loud, as it were, rather than tried and tested - apart from the whole bit about getting the job name from the scheduled task... that works - we use it a lot here.)
Very cool. I learned something new! Thanks, Thomas.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply