November 22, 2017 at 8:19 am
I have a trigger that will let me know when a job is disable or enabled how do i add to the to show when a job is added or deleted and if the time or steps were changed
November 22, 2017 at 8:20 am
here is code
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[jobChecker]
ON sysjobs
FOR UPDATE AS
SET NOCOUNT ON
-- # DECLARE VARIABLES # --
DECLARE @username VARCHAR(50),
@hostName VARCHAR(50),
@jobName VARCHAR(100),
@newEnabled INT,
@oldEnabled INT,
@jobCreated INT,
@bodyText VARCHAR(200),
@subjectText VARCHAR(200),
@servername VARCHAR(50),
@profileName VARCHAR(50) = 'DBAAlerts',
@recipients VARCHAR(500) = 'michael.yukas@thermofisher.com'
-- # SET VARIABLES # --
SELECT @username = SYSTEM_USER
SELECT @hostName = HOST_NAME()
SELECT @servername = @@servername
SELECT @newEnabled = ENABLED FROM Inserted
SELECT @oldEnabled = ENABLED FROM Deleted
SELECT @jobName = Name FROM Inserted
-- # CHECK FOR ENABLED/DISABLED # --
IF @newEnabled <> @oldEnabled
BEGIN
IF @newEnabled = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ENABLED'
END
IF @newEnabled = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been DISABLED'
END
SET @subjectText = 'SQL Job on ' + @subjectText
-- # SEND EMAIL # --
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profileName,
@recipients = @recipients,
@body = @bodyText,
@subject = @subjectText
END
November 22, 2017 at 8:33 am
Unless it's essential that you be notified immediately upon any change, I think I'd change the trigger so that it writes to a table instead of firing off an e-mail every time a change is made. That way, you won't get a barrage of e-mails when lots of changes are being made.
I don't know enough about the internals of the job engine to know whether changes to sysjobs are made one by one or all together. Yes, usually you'll only add one job at a time, but what happens if you select two or more jobs and disable or delete them? If all modifications are written to sysjobs in one operation, your trigger will fail.
If you want to know about changes to schedules and steps, you'll need a similar trigger on sysjobsteps and on sysjobschedules (I think that's what the tables are called).
John
November 22, 2017 at 8:41 am
i am ok with how it works i just cant figure out the logic to add for job added job deleted or schedule changed
November 22, 2017 at 8:55 am
For schedule changed, see the last line of my first reply. For job deleted, you need to look for rows with the same job_id that appear in the Deleted virtual table but not in Inserted.
By the way, I recommend that you remove your personal (or work if that's what it is) e-mail address from any code you post.
John
November 27, 2017 at 6:32 am
this trigger thing is just a big pain for this. Is there a way with standard alerts to be notified for a disable, enable, and schedule change
November 27, 2017 at 10:40 am
I'd be curious what the trigger issue is for you.
There isn't a general alerting system, as any type of change isn't necessarily tracked. Extended Events does some of this, and you could have a session specifically looking for agent changes. This would just record the issue. There are ways to write work to look for this change, but a trigger is really the easiest way.
Jobs aren't first class citizens in SQL Server. They are part of the Agent subsystem, which is separate from the database engine. Your trigger is just checking for changes to the meta data stored in SQL Server.
November 27, 2017 at 8:35 pm
myukas - Monday, November 27, 2017 6:32 AMthis trigger thing is just a big pain for this. Is there a way with standard alerts to be notified for a disable, enable, and schedule change
It may be a pain but it's a good way to trap the history so you can bust someone's chops if they weren't supposed to make such a change. Looking at the larger picture, your auditors would like you a little better if you demonstrated such audits on changes to your jobs.
There are also some temporal audit tools that are available in T-SQL now that will auto-magically make the history tables and triggers for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2017 at 8:10 am
i am getting emails just fine for enable and disable jobs but not for add or delete not sure how to code this
use msdb
go
CREATE TRIGGER [dbo].[jobChecker2]
ON sysjobs
FOR UPDATE AS
SET NOCOUNT ON
-- # DECLARE VARIABLES # --
DECLARE @username VARCHAR(50),
@hostName VARCHAR(50),
@jobName VARCHAR(100),
@newEnabled INT,
@oldEnabled INT,
@newjobadd INT,
@oldjobadd INT,
@jobCreated INT,
@bodyText VARCHAR(200),
@subjectText VARCHAR(200),
@servername VARCHAR(50),
@profileName VARCHAR(50) = 'DBAAlerts',
@recipients VARCHAR(500) = 'michael.yukas@thermofisher.com'
-- # SET VARIABLES # --
SELECT @username = SYSTEM_USER
SELECT @hostName = HOST_NAME()
SELECT @servername = @@servername
SELECT @newEnabled = ENABLED FROM Inserted
SELECT @oldEnabled = ENABLED FROM Deleted
SELECT @newjobadd = ADD FROM Inserted
SELECT @oldjobadd = ADD FROM Inserted
SELECT @jobName = Name FROM Inserted
-- # CHECK FOR ENABLED/DISABLED # --
IF @newEnabled <> @oldEnabled
BEGIN
IF @newEnabled = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ENABLED'
END
IF @newEnabled = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been DISABLED'
END
IF @newjobadd <> @oldjobadd
BEGIN
IF @newjobadd = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ADDED'
END
IF @newjobadd = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been Deleted'
END
SET @subjectText = 'SQL Job on ' + @subjectText
-- # SEND EMAIL # --
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profileName,
@recipients = @recipients,
@body = @bodyText,
@subject = @subjectText
END
November 28, 2017 at 8:18 am
OK, you've posted the code that does work. If you show us what you've tried that doesn't work for added and deleted jobs, we should be able to help out.
John
November 28, 2017 at 8:21 am
thats my problem not sure what to put in
November 28, 2017 at 8:26 am
Well yes your trigger is set to only run on updates 😀
Change FOR UPDATE AS to FOR INSERT, UPDATE, DELETE AS
It looks like you'll also need to modify the logic in there a little bit since that trigger is specifically coded for updates. You'll probably just need a couple new blocks like
IF @newEnabled IS NULL
BEGIN
<do code for deleted job>
END
IF @oldEnabled IS NULL
BEGIN
<do code for new job>
END
November 28, 2017 at 8:32 am
i was thinking this
use msdb
go
CREATE TRIGGER [dbo].[jobChecker2]
ON sysjobs
FOR UPDATE AS
SET NOCOUNT ON
-- # DECLARE VARIABLES # --
DECLARE @username VARCHAR(50),
@hostName VARCHAR(50),
@jobName VARCHAR(100),
@newEnabled INT,
@oldEnabled INT,
@newjobadd INT,
@oldjobadd INT,
@jobCreated INT,
@bodyText VARCHAR(200),
@subjectText VARCHAR(200),
@servername VARCHAR(50),
@profileName VARCHAR(50) = 'DBAAlerts',
@recipients VARCHAR(500) = ''
-- # SET VARIABLES # --
SELECT @username = SYSTEM_USER
SELECT @hostName = HOST_NAME()
SELECT @servername = @@servername
SELECT @newEnabled = ENABLED FROM Inserted
SELECT @oldEnabled = ENABLED FROM Deleted
SELECT @newjobadd = ADD FROM Inserted
SELECT @oldjobadd = ADD FROM Inserted
SELECT @jobName = Name FROM Inserted
-- # CHECK FOR ENABLED/DISABLED # --
IF @newEnabled <> @oldEnabled
BEGIN
IF @newEnabled = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ENABLED'
END
IF @newEnabled = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been DISABLED'
END
IF @newjobadd <> @oldjobadd
BEGIN
IF @newjobadd = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ADDED'
END
IF @newjobadd = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been Deleted'
END
SET @subjectText = 'SQL Job on ' + @subjectText
-- # SEND EMAIL # --
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profileName,
@recipients = @recipients,
@body = @bodyText,
@subject = @subjectText
END
November 28, 2017 at 8:54 am
You don't appear to have taken ZZartin's advice about using FOR UPDATE, nor mine (five days ago) about making the trigger work for multiple modifications. Nor does it look like you did any research before posting, otherwise you may have come across this (you'll need to register), which I think is exactly what you're looking for.
John
November 28, 2017 at 8:55 am
ok i got it to work by replacing FOR UPDATE to AFTER UPDATE, INSERT, DELETE however the email i get sucks this is all i get New job creation or job modification alert
here is code i am using
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[jobChecker2]
ON sysjobs
AFTER UPDATE, INSERT, DELETE AS
SET NOCOUNT ON
-- # DECLARE VARIABLES # --
DECLARE @username VARCHAR(50),
@hostName VARCHAR(50),
@jobName VARCHAR(100),
@newEnabled INT,
@oldEnabled INT,
@jobCreated INT,
@bodyText VARCHAR(200),
@subjectText VARCHAR(200),
@servername VARCHAR(50),
@profileName VARCHAR(50) = 'DBAAlerts',
@recipients VARCHAR(500) = '
-- # SET VARIABLES # --
SELECT @username = SYSTEM_USER
SELECT @hostName = HOST_NAME()
SELECT @servername = @@servername
SELECT @newEnabled = ENABLED FROM Inserted
SELECT @oldEnabled = ENABLED FROM Deleted
SELECT @jobName = Name FROM Inserted
-- # CHECK FOR ENABLED/DISABLED # --
IF @newEnabled <> @oldEnabled
BEGIN
IF @newEnabled = 1
BEGIN
SET @bodyText = 'The user (' + @username + ') enabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername + ' : [' + @jobName + '] has been ENABLED'
END
IF @newEnabled = 0
BEGIN
SET @bodyText = 'The user (' + @username + ') disabled the job from ' + @hostName + ' on ' + CONVERT(VARCHAR(20),GETDATE(),100) + '.'
SET @subjectText = @servername+' : [' + @jobName + '] has been DISABLED'
END
SET @subjectText = 'SQL Job on ' + @subjectText
-- # SEND EMAIL # --
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profileName,
@recipients = @recipients,
@body = @bodyText,
@subject = @subjectText
END
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply