Hello everyone,
Basically I'm trying to make our own auditing piece using extended events.
The process is any XEL file recreated by the extended events (once it reaches the maximum file size and creates the rollover file) are moved to another Server to be processed into readable SQL data.
So the code is something like below:
declare @source varchar(200)
declare @destination varchar(200)
declare @cmd varchar(500)
set @source = 'E:\SensitiveColsAudit'
set @destination = '\DestinationServer.domain.com\z$\SQL Sensitive Column Trace'
SET @Cmd = 'Move "' + @source + '*.xel" "' + @destination + '"'
exec master..xp_cmdshell @CMD
Problem is, it's also trying to move the XEL files that are currently in use (which we don't want the extra resource being used for moving un-movable files without recreating the extended event).
The closest I can get to knowing the filenames in use so that I can exclude them from being moved (via some temptable and looping code) is the query below:
select A.name, B.Value from sys.server_event_sessions A inner join sys.server_event_session_fields B
ON A.event_session_id = B.event_session_id
where A.name like '%sensitivecolaudit%' and isnumeric(convert(varchar(50),B.value)) <>1
But, I am unable to find the exact query to get those random numbers generated when the XEL files are created.
Any advice for this?.. if it isn't possible, can you just share some queries to check first if the file is being used before moving them via xp_cmdshell?
Appreciate your response on this.
Regards,
Jerome