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

Document created by RSA Customer Support Employee on Jun 30, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000032521
Applies ToRSA Product Set:  Security Analytics
RSA Product/Service Type:  Log Collector
Platform (Other):  SQL
TasksThis 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.
ResolutionTable 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%'

Attachments

    Outcomes