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.