[스크립트] Audit Trace

서버 이전, 모의훈련 혹은 기타 목적으로 특정 계정과 서버의 접근 이력 기록 필요시 사용

 

실시간 trace 조회

 
 declare @path sql_variant
 select  @path =value FROM ::fn_trace_getinfo( 0 ) where cast (value as varchar(1000)) like N'D:\trace\APLoginCheck_%'

-- DISTINCT
select Distinct 	 ServerName, LoginName,hostName 
from  fn_trace_gettable(cast(@path as varchar(max)), default)
where loginname is not null and hostname is not null

-- 전체
select ServerName,StartTime,EndTime,LoginName,hostName,ApplicationName
from  fn_trace_gettable(cast(@path as varchar(max)), default)
where loginname is not null and hostname is not null

trace 중지/생성

-- 테스트용 trace 종료
 declare @trace_id int
 select  @trace_id = traceid FROM ::fn_trace_getinfo( 0 ) where cast (value as varchar(1000)) like N'D:\trace\APLoginCheck_%'
	 
 if (@trace_id is not null)
 begin		  
			EXEC sp_trace_setstatus @trace_id, 0;	--<< 중단
			EXEC sp_trace_setstatus @trace_id, 2;	--<< 종료
 end 
 go

-- 테스트용 trace 생성
declare 
		@trace_ID	    int
		, @filePrefix	nvarchar(100)
		, @maxfilesize  bigint
		, @traceRollOver bit
		, @includeMachineName bit	 
		, @strVal nvarchar(1000)
		, @traceFlag int
		,@DurationValue bigint

	set @filePrefix = 'D:\trace\APLoginCheck_'
	 
	select 
		 @maxfilesize = 100,
		@traceRollOver =  1 ,
		@includeMachineName =  1 
	 
  	 
 set @filePrefix = @filePrefix + cast(ServerProperty('MachineName') as nvarchar(100))  + '_'
	  
 select @traceFlag = CASE WHEN @traceRollOver = 1 THEN 2 ELSE 0 END
 

	  
		-- trace 생성
		declare @rc int
		declare @TraceID int
		declare @fileName nvarchar(1000)
		set @fileName = @filePrefix + replace(replace(replace(convert(varchar(16), getdate(), 121), '-',''), ' ',''),':','')
	    
		exec @rc = sp_trace_create @TraceID output, @traceFlag, @fileName, @maxfilesize, NULL 
		if (@rc != 0) goto error
		    
		 
		declare @on bit
		set @on = 1
	 
		--Audit Login
		exec sp_trace_setevent @TraceID, 14, 1, @on
		exec sp_trace_setevent @TraceID, 14, 8, @on
		exec sp_trace_setevent @TraceID, 14, 10, @on
		exec sp_trace_setevent @TraceID, 14, 11, @on
		exec sp_trace_setevent @TraceID, 14, 12, @on
		exec sp_trace_setevent @TraceID, 14, 13, @on
		exec sp_trace_setevent @TraceID, 14, 14, @on
		exec sp_trace_setevent @TraceID, 14, 15, @on
		exec sp_trace_setevent @TraceID, 14, 16, @on 
		exec sp_trace_setevent @TraceID, 14, 17, @on
		exec sp_trace_setevent @TraceID, 14, 18, @on
		exec sp_trace_setevent @TraceID, 14, 35, @on 
		
		--Audit Logout
		exec sp_trace_setevent @TraceID, 15, 1, @on
		exec sp_trace_setevent @TraceID, 15, 8, @on
		exec sp_trace_setevent @TraceID, 15, 10, @on
		exec sp_trace_setevent @TraceID, 15, 11, @on
		exec sp_trace_setevent @TraceID, 15, 12, @on
		exec sp_trace_setevent @TraceID, 15, 14, @on
		exec sp_trace_setevent @TraceID, 15, 15, @on
		exec sp_trace_setevent @TraceID, 15, 16, @on 
		exec sp_trace_setevent @TraceID, 15, 17, @on
		exec sp_trace_setevent @TraceID, 15, 18, @on
		exec sp_trace_setevent @TraceID, 15, 35, @on
		
		 

		--Audit Login Failed
		exec sp_trace_setevent @TraceID, 20, 1, @on
		exec sp_trace_setevent @TraceID, 20, 8, @on
		exec sp_trace_setevent @TraceID, 20, 10, @on
		exec sp_trace_setevent @TraceID, 20, 11, @on
		exec sp_trace_setevent @TraceID, 20, 12, @on
		exec sp_trace_setevent @TraceID, 20, 14, @on
		exec sp_trace_setevent @TraceID, 20, 15, @on
		exec sp_trace_setevent @TraceID, 20, 16, @on 
		exec sp_trace_setevent @TraceID, 20, 17, @on
		exec sp_trace_setevent @TraceID, 20, 18, @on
		exec sp_trace_setevent @TraceID, 20, 35, @on
		
	 
		--제외할 로그인 설정 
		declare AuditCursor cursor fast_forward for 
				select    name 
			from sys.server_principals 
			where type  in ('R','C')  
				or name in ()
				or name like '%\%'    
  
		open AuditCursor
  
		fetch next from AuditCursor into @strval
  
		while (@@FETCH_STATUS = 0)
		  begin
		   exec sp_trace_setfilter @TraceID, 11, 0, 7, @strVal
		   fetch next from AuditCursor into @strval
		  end
  
		close AuditCursor
		deallocate AuditCursor
 
		-- trace 시작
		exec sp_trace_setstatus @TraceID, 1

		error: 
		select ErrorCode=@rc