ApexSQL Log continuous auditing repository topography (4209592)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

ApexSQL Log continuous auditing repository topography

Resolution

.SQLCodeBlock { margin-left: 0px !important; }

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.