Olá Pessoal,
Bom dia.
Hoje venho trazer pra vocês, a solução de algumas perguntas que eu me fazia nos ambientes em que trabalho, mas que eu não tinha como responder:
– Quem criou/alterou/excluiu um determinado job?
– Apaguei sem querer um Job.. Como faço pra recriá-lo rapidamente, sem precisar de restaurar backup ?
– Alguém desabilitou um determinado Job.. Quem foi ?
– Alguém alterou a descrição do Job.. Preciso identificar quem foi..
Para responder essas questões, iremos criar uma forma de monitorar a criação, modificação e exclusão de Jobs no SQL Server, através de uma trigger na tabela msdb.dbo.sysjobs.
Criando a tabela de histórico
Antes de criar a trigger que irá recuperar as informações, precisaremos criar a tabela que irá guardar as informações e criar o histórico.
CREATE TABLE [dbo].[Job_Audit] (
[Id_Auditoria] [INT] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[Dt_Evento] [DATETIME] NULL DEFAULT (GETDATE()),
[Ds_Usuario] [VARCHAR](50) NULL,
[Ds_Job] [sysname] NULL,
[Ds_Hostname] [VARCHAR](50) NULL,
[Ds_Query] [VARCHAR](MAX) NULL,
[Fl_Situacao] [TINYINT] NULL
)
WITH (DATA_COMPRESSION=PAGE)
Criação da trigger
Agora que criamos a tabela que terá as nossas informações, vamos criar a trigger que irá populá-la.
USE [msdb]
GO
/***************************************************************************************************
-- Trigger para os Jobs
***************************************************************************************************/
IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgJobs_Status') > 0) DROP TRIGGER dbo.trgJobs_Status
GO
CREATE TRIGGER trgJobs_Status ON sysjobs
AFTER INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE
@UserName VARCHAR(50) = SYSTEM_USER,
@HostName VARCHAR(50) = HOST_NAME(),
@JobName sysname,
@New_Enabled INT,
@Old_Enabled INT,
@ExecStr VARCHAR(100),
@Qry VARCHAR(MAX)
SELECT @New_Enabled = [enabled] FROM Inserted
SELECT @Old_Enabled = [enabled] FROM Deleted
SELECT @JobName = [name] FROM Deleted
IF (@JobName IS NULL)
SELECT @JobName = [name] FROM Deleted
-- Identificando a query executada
CREATE TABLE #inputbuffer (
[EventType] NVARCHAR(60),
[Parameters] INT,
[EventInfo] VARCHAR(MAX)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
-- Verifica se houve alteração de status
IF (@New_Enabled != @Old_Enabled)
BEGIN
IF (@New_Enabled = 1)
BEGIN
INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao )
SELECT @UserName, @JobName, @HostName, @Qry, 1
END
IF (@New_Enabled = 0)
BEGIN
INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query, Fl_Situacao )
SELECT @UserName, @JobName, @HostName, @Qry, 0
END
END
ELSE BEGIN
INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query )
SELECT @UserName, @JobName, @HostName, @Qry
END
END
GO
/***************************************************************************************************
-- Trigger para os Schedules dos Jobs
***************************************************************************************************/
IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Schedules
GO
CREATE TRIGGER [dbo].[trgAudit_Schedules] ON [dbo].[sysschedules]
AFTER UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE
@UserName VARCHAR(50) = SYSTEM_USER,
@HostName VARCHAR(50) = HOST_NAME(),
@JobName VARCHAR(MAX) = '',
@ExecStr VARCHAR(100),
@Qry VARCHAR(MAX)
IF ((SELECT COUNT(*) FROM Inserted) > 0)
BEGIN
SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name]
FROM msdb.dbo.sysjobs A
JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id
JOIN Inserted C ON B.schedule_id = C.schedule_id
END
ELSE BEGIN
SELECT @JobName += (CASE WHEN @JobName != '' THEN ' | ' ELSE '' END) + A.[name]
FROM msdb.dbo.sysjobs A
JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id
JOIN Deleted C ON B.schedule_id = C.schedule_id
END
-- Identificando a query executada
CREATE TABLE #inputbuffer (
[EventType] NVARCHAR(60),
[Parameters] INT,
[EventInfo] VARCHAR(MAX)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
IF (@JobName != '')
BEGIN
INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query )
SELECT @UserName, @JobName, @HostName, @Qry
END
END
GO
ALTER TABLE [dbo].[sysschedules] ENABLE TRIGGER [trgAudit_Schedules]
GO
/***************************************************************************************************
-- Trigger para os Schedules
***************************************************************************************************/
IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgAudit_Jobs_Schedules') > 0) DROP TRIGGER dbo.trgAudit_Jobs_Schedules
GO
CREATE TRIGGER [dbo].[trgAudit_Jobs_Schedules] ON [dbo].[sysjobschedules]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@UserName VARCHAR(50) = SYSTEM_USER,
@HostName VARCHAR(50) = HOST_NAME(),
@JobName sysname,
@ExecStr VARCHAR(100),
@Qry VARCHAR(MAX)
IF ((SELECT COUNT(*) FROM Inserted) > 0)
BEGIN
SELECT @JobName = A.[name]
FROM msdb.dbo.sysjobs A
JOIN Inserted B ON A.job_id = B.job_id
END
ELSE BEGIN
SELECT @JobName = A.[name]
FROM msdb.dbo.sysjobs A
JOIN Deleted B ON A.job_id = B.job_id
END
-- Identificando a query executada
CREATE TABLE #inputbuffer (
[EventType] NVARCHAR(60),
[Parameters] INT,
[EventInfo] VARCHAR(MAX)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
INSERT INTO dbo.Job_Audit ( Ds_Usuario, Ds_Job, Ds_Hostname, Ds_Query )
SELECT @UserName, @JobName, @HostName, @Qry
END
GO
ALTER TABLE [dbo].[sysjobschedules] ENABLE TRIGGER [trgAudit_Jobs_Schedules]
GOConsultando as informações
Agora que nossa trigger está ativada, faça algumas alterações em seus Jobs e veja como as informações são gravadas. É possível identificar a data/hora que o evento foi acionado, o usuário que realizou o evento e mesmo a query que foi executada no banco, seja para criação, exclusão ou alteração do Job.
Até mais!
Comentários (0)
Carregando comentários...