ApexSQL Log continuous auditing repository topography

When continuous auditing is performed, audited data is stored in one of two sets of repository tables, depending if user is performing general auditing or before-after auditing. In this article we are going to provide information on the structures of these tables.

General auditing

The first set of tables includes 2 repository tables that are used to stored audited data when general auditing is performed.

The tables are:

  • APEXSQL_LOG_OPERATION
  • APEXSQL_LOG_OPERATION_DETAIL

All audited data will be stored in these tables indefinitely, the information is always inserted into these tables and never updated by ApexSQL Log.

APEXSQL_LOG_OPERATION

This table is used to store basic information on audited transactions, such is operation, user and time information, transaction details and more. 

CREATE TABLE [dbo].[APEXSQL_LOG_OPERATION] (
	[LSN] [char](22) NOT NULL,
	[OPERATION_TYPE] [varchar](128) NULL,
	[OBJECT_NAME] [nvarchar](128) NULL,
	[USER_NAME] [nvarchar](128) NULL,
	[TRANSACTION_ID] [char](13) NULL,
	[TRANSACTION_BEGIN] [datetime] NULL,
	[TRANSACTION_END] [datetime] NULL,
	[TRANSACTION_DESCRIPTION] [nvarchar](500) NULL,
	[ROW_RECONSTRUCTED] [bit] NULL,
	[PAGE_ID] [char](13) NULL,
	[SLOT_ID] [int] NULL,
	[ID_KEY] [nvarchar](1000) NULL,
	[SPID] [smallint] NULL,
	[SERVER] [nvarchar](128) NULL,
	[DATABASE] [nvarchar](128) NULL,
	[TRANSACTION_STATE] [varchar](10) NULL,
	[SCHEMA_NAME] [nvarchar](128) NULL,
	[ROW_PARTIALLY_RECONSTRUCTED] [bit] NULL,
	[ROW_ORDINAL] [smallint] NOT NULL,
	[PARENT_SCHEMA_NAME] [nvarchar](128) NULL,
	[PARENT_OBJECT_NAME] [nvarchar](128) NULL,
	[PREVIOUS_LSN] [char](22) NULL,
	[DURATION] [float] NULL,
	[LOGON_TIME] [datetime] NULL,
	CONSTRAINT [PK_APEXSQL_LOG_OPERATION] PRIMARY KEY CLUSTERED (
		[LSN] ASC,
		[ROW_ORDINAL] ASC
		) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

Column name Data type Allow nulls Description
LSN char (22) Log sequence number
OPERATION_TYPE varchar (128)
✔
 Operation type (insect, delete, create, drop…)
OBJECT_NAME nvarchar (128)
✔
 Name of the object affected by the specified operation
USER_NAME nvarchar 128)
✔
 Name of the user that has executed transaction
TRANSACTION_ID char (13)
✔
 Unique transaction id number
TRANSACTION_BEGIN datetime
✔
 Time when the transaction has started
TRANSACTION_END datetime
✔
 Time when the transaction has ended
TRANSACTION_DESCRIPTION nvarchar (500)
✔
 Transaction description
ROW_RECONSTRUCTED bit
✔
 Marks if the row was fully reconstructed or not
PAGE_ID char (13)
✔
 Unique page id number
SLOT_ID int
✔
 Unique slot id number
ID_KEY nvarchar (1000)
✔
 Unique id key
SPID smallint Server process id number
SERVER nvarchar (128)
✔
 Server name
DATABASE nvarchar (128)
✔
 Database name
TRANSACTION_STATE varchar (10)
✔
 State of the transaction (committed, aborted…)
SCHEMA_NAME nvarchar (128)
✔
 Object schema name
ROW_PARTIALLY_RECONSTRUCTED bit
✔
 Marks if the row was partially reconstructed or not
ROW_ORDINAL smallint Position (ordinal) of the specific record in the table
PARENT_SCHEMA_NAME nvarchar (128)
✔
 Parent object schema name
PARENT_OBJECT_NAME nvarchar (128)
✔
 Parent object name
PREVIOUS_LSN char (22)
✔
 Previous log sequence number
DURATION float
✔
 Transaction duration
LOGON_TIME datetime
✔
 Time when user logged on

 

APEXSQL_LOG_OPERATION_DETAIL

This table is used to store information on before-after changes as well as related information. 

CREATE TABLE [dbo].[APEXSQL_LOG_OPERATION_DETAIL] (
	[LSN] [char](22) NOT NULL,
	[LINE_NO] [int] NOT NULL,
	[COLUMN_NAME] [nvarchar](128) NULL,
	[COLUMN_TYPE] [nvarchar](128) NULL,
	[OLD_VALUE] [ntext] NULL,
	[NEW_VALUE] [ntext] NULL,
	CONSTRAINT [PK_APEXSQL_LOG_OPERATION_DETAIL] PRIMARY KEY CLUSTERED (
		[LSN] ASC,
		[LINE_NO] ASC
		) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Column name Data type Allow nulls Description
LSN char (22) Log sequence number
LINE_NO int Unique line number
COLUMN_NAME nvarchar (128)
✔
 Column name
COLUMN_TYPE nvarchar (128)
✔
 Column type
OLD_VALUE ntext
✔
 Before (change) value
NEW_VALUE ntext
✔
 After (change) value

 

Before and after auditing

The second set of tables consists of three repository tables that are used in order to store auditing information when before-after auditing is performed.

These tables are

  • APEXSQL_BEFORE_AFTER_DATABASE
  • APEXSQL_BEFORE_AFTER_DETAIL
  • APEXSQL_BEFORE_AFTER_OPERATION

Note that these tables have no actual connection or dependency with the previously mentioned table set for general auditing, and are used separately.

 

APEXSQL_BEFORE_AFTER_DATABASE

This table stores information on the audited SQL Server instance and database 

CREATE TABLE [dbo].[APEXSQL_BEFORE_AFTER_DATABASE] (
	[ID] [int] NOT NULL,
	[SERVER] [nvarchar](128) NULL,
	[DATABASE] [nvarchar](128) NULL,
	PRIMARY KEY CLUSTERED ([ID] ASC) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

Column name Data type Allow nulls Description
ID Int Unique id number
SERVER nvarchar (128)
✔
 Server name
DATABASE nvarchar (128)
✔
 Database name

 

APEXSQL_BEFORE_AFTER_DETAIL

This table stores information on before-after values and related details 

CREATE TABLE [dbo].[APEXSQL_BEFORE_AFTER_DETAIL] (
	[LSN] [char](22) NOT NULL,
	[DATABASE_ID] [int] NOT NULL,
	[ROW_ORDINAL] [smallint] NOT NULL,
	[LINE_NO] [smallint] NOT NULL,
	[IS_PRIMARY_KEY] [bit] NOT NULL,
	[COLUMN_NAME] [nvarchar](128) NULL,
	[BEFORE] [nvarchar](max) NULL,
	[AFTER] [nvarchar](max) NULL,
	CONSTRAINT [PK_APEXSQL_BEFORE_AFTER_DETAIL] PRIMARY KEY CLUSTERED (
		[LSN] ASC,
		[DATABASE_ID] ASC,
		[ROW_ORDINAL] ASC,
		[LINE_NO] ASC,
		[IS_PRIMARY_KEY] ASC
		) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Column name Data type Allow nulls Description
LSN char (22) Log sequence number
DATABASE_ID int Unique database id number
ROW_ORDINAL smallint Position (ordinal) of the specific record in the table
LINE_NO smalllint Unique line number
IS_PRIMARY_KEY bit Marks if the row is primary key for the table
COLUMN_NAME nvarchar (128)
✔
 Column name
BEFORE nvarchar (max)
✔
 Before (change) value
AFTER nvarchar (max)
✔
 After (change) value

 

APEXSQL_BEFORE_AFTER_OPERATION

This table stores information on the SQL Server operations and related information 

CREATE TABLE [dbo].[APEXSQL_BEFORE_AFTER_OPERATION] (
	[LSN] [char](22) NOT NULL,
	[DATABASE_ID] [int] NOT NULL,
	[TIME] [datetime] NULL,
	[OPERATION_TYPE] [varchar](128) NULL,
	[SCHEMA] [nvarchar](128) NULL,
	[TABLE] [nvarchar](128) NULL,
	[USER_NAME] [nvarchar](128) NULL,
	[DURATION] [float] NULL,
	[RECONSTRUCTED] [bit] NULL,
	CONSTRAINT [PK_APEXSQL_BEFORE_AFTER_OPERATION] PRIMARY KEY CLUSTERED (
		[LSN] ASC,
		[DATABASE_ID] ASC
		) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

Column name Data type Allow nulls Data type
LSN char (22) Log sequence number
DATABASE_ID int Unique database id number
TIME datetime
✔
 Time when transaction was executed
OPERATION_TYPE varchar (128)
✔
 Operation type (insect, delete, create, drop…)
SCHEMA nvarchar (128)
✔
 Object schema name
TABLE nvarchar (128)
✔
 Table name
USER_NAME nvarchar (128)
✔
 Name of the user that has executed transaction
DURATION float
✔
 Transaction duration
RECONSTRUCTED bit
✔
 Marks if the row was reconstructed or not

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

 

Related posts:

  1. How to use custom SQL script alerts in ApexSQL Monitor
  2. How to work with the ApexSQL Log continuous auditing repository directly, including querying and reporting
  3. Pre-synchronization warnings and messages in ApexSQL Diff
  4. PCI compliance checklist for ApexSQL Audit
  5. ApexSQL Log continuous auditing FAQs