ALTER TABLE [dbo].[DOCaudit] ADD CONSTRAINT [DF_DOCaudit_sendTo] DEFAULT ((0)) FOR [SendTo] GO
USE [DPAudit] GO
/****** Object: StoredProcedure [dbo].[spKariyerAudit_MB] Script Date: 21.03.2024 13:19:41 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE proc [dbo].[spKariyerAudit_MB] as DECLARE @auditpath as nvarchar(100)
SET @auditpath = (select distinct log_file_path from sys.server_file_audits where [name] like 'KariyerAudit%')+'*.sqlaudit' declare @lasteventtime as [datetime2](0)
if not exists (SELECT TOP 1 EventTime FROM DOCaudit ORDER BY EventTime DESC) BEGIN SET @lasteventtime = DATEADD(minute,-30,GETUTCDATE()) END ELSE BEGIN DECLARE @a [datetime2](0) = (SELECT MAX(EventTime) FROM DOCaudit) SET @lasteventtime = convert([datetime2](0),SWITCHOFFSET(convert(datetimeoffset, @a),'-03:00')) SET @lasteventtime = DATEADD(SECOND,+1,@lasteventtime) END
INSERT INTO [DPAudit].[dbo].[DOCaudit] ([ObjectName],[EventTime],[Statement],[DatabaseName],[ClassType],[SessionServerPrincipalName],[ServerPrincipalName],[ActionID],[Succeeded],[SessionID]) SELECT [object_name] ,convert([datetime2](0),SWITCHOFFSET(convert(datetimeoffset, [event_time]),'+03:00')) ,[statement] ,[database_name] ,[class_type] ,[session_server_principal_name] ,[server_principal_name] ,[action_id] ,[succeeded] ,[session_id] FROM fn_get_audit_file (@auditpath,NULL,NULL) WHERE [event_time] > @lasteventtime AND [statement] NOT LIKE 'INSERT INTO @myperms%SELECT%FROM sys.fn_builtin_permissions%' UPDATE [DPAudit].[dbo].[DOCaudit] SET [ObjectName] = 'xxxxx' where [ObjectName] like '' OR [ObjectName] is NULL UPDATE [DPAudit].[dbo].[DOCaudit] SET [statement] = '-' where [statement] like '' OR [statement] is NULL UPDATE [DPAudit].[dbo].[DOCaudit] SET [statement] = REPLACE([statement],'''',' ') UPDATE [DPAudit].[dbo].[DOCaudit] SET [DatabaseName] = '-' where [DatabaseName] like '' OR [DatabaseName] is NULL
