Post Views 5,308 views
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.
|
1 2 3 4 5 6 7 8 9 10 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
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] GO |
Consultando 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!
Portuguese 
English
Excelente me ajudou muito . Obrigado
Aqui não está gravando nada, devo alterar algo?
Perfeito. Ótimo Post. Vem muitas informacoes precisas.