Understanding the sqlServerAuditxxxx.sql script used for SQL device integration with RSA Security Analytics

Tasks

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.

Resolution

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%'

Product Details

RSA Product Set: Security Analytics
RSA Product/Service Type: Log Collector
Platform (Other): SQL

Approval Reviewer Queue

ASOC Approval Group