This article addresses the following three topics:
- Table name and attribute name from which logs are being collected.
- Trace file generation and deletion.
- Exporting the trace files from DB server to SA.
Table name and attribute name from which logs are being collected
We are looking for type=U for User tables as per the Microsoft TechNet article aa260447 on sysobjects.
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_end_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_curr_rec]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_init_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_wait]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_update_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_set_events]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_start_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_data]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_test_aud_swap_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_swap_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[auto_nic_aud_swap_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_prev_idx]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_get_filename]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace_eps]') AND type in (N'U'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace]') AND type in (N'U'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[rsa_delete_trace_files]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[rsa_file_exists]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_trace_backup]') AND type in (N'U'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_process_dead_trace]') AND type in (N'P', N'PC'))
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[nic_aud_create_temp]') AND type in (N'P', N'PC'))
From the SQL user tables we are creating:
- nic_aud_trace_eps
- nic_aud_trace
- nic_aud_trace_backup
Trace file generation and deletion
Trace Creation:
Trace is getting created by the following line:
exec @rc = sp_trace_create @traceid output, 2, @filename, @maxfilesize, @endtime
Trace Size Restriction:
The following line which restricts trace file maximum size to 100 MB
set @maxfilesize = 100
Trace Deletion:
The following procedure is used to delete trace files
create procedure rsa_delete_trace_files
Exporting the trace files from DB server to Security Analytics
Log Collector ODBC collection is obtaining these trace files as explained in the Microsoft SQL Server Event Source Configuration Guide.
The /etc/netwitness/ng/logcollection/content/collection/odbc/mssql.xml on Log Collector can see for a particular date range [StartTime - current date]
The following query is being issued where procedure nic_aud_swap_trace is being explicitly called:
exec nic_aud_swap_trace 30, '%file_name%', 1, 'WHERE StartTime > %TRACKING%'