How to work with the ApexSQL Log continuous auditing repository directly, including querying and reporting

Performing continuous auditing with ApexSQL Log enables users to seamlessly audit SQL Server databases for all DML and DDL changes that occur on the audited database, directly from the transaction log. The audited data is then stored in the repository tables which can be created in any SQL Server database on any connectible SQL Server instance, and these tables will then be continuously updated with new auditing data.

ApexSQL Log uses two specific sets of repository tables for the purpose of storing the audited information, depending on the type of auditing that is being performed, whether the user if performing general transaction log auditing or before-after auditing.

The first set of tables, used for general auditing consists of two tables. These are “APEXSQL_LOG_OPERATION” and “APEXSQL_LOG_OPERATION_DETAIL”. The “APEXSQL_LOG_OPERATION” table is used to store all basic information on audited transactions, including unique LSN for each transaction, operation information, user information, time information and more. The second table, “APEXSQL_LOG_OPERATION_DETAIL” is used to store the extended transaction information of before-after change information (before-after values)

The second set of tables, used for before-after auditing consists of three tables: “APEXSQL_BEFORE_AFTER_DATABASE”, “APEXSQL_BEFORE_AFTER_DETAIL”, “APEXSQL_BEFORE_AFTER_OPERATION”. The “APEXSQL_BEFORE_AFTER_DATABASE” table contains the information on the audited server and database. The “APEXSQL_BEFORE_AFTER_DETAIL” table is used to store the extended transaction information, including the actual before and after values, while “APEXSQL_BEFORE_AFTER_OPERATION” table stores information on the operations that have occurred and related information. More information on the repository topography and detailed information on mentioned tables structure can be found in this article.

While all tables can be queried separately for the specific information via SQL Server Management Studio or any other similar tool, in order to get the full auditing information out of the tables, the easiest way to achieve this is to join the tables together and connect them using the unique “LSN” column which is the same for both table sets. The easiest way to achieve this is with ‘INNER JOIN’ command as follows:

SELECT *
FROM dbo.APEXSQL_LOG_OPERATION t
INNER JOIN dbo.APEXSQL_LOG_OPERATION_DETAIL r ON r.LSN = t.LSN;

For the second tables set, the join will look like this:

SELECT *
FROM dbo.APEXSQL_BEFORE_AFTER_DATABASE A
INNER JOIN dbo.APEXSQL_BEFORE_AFTER_OPERATION B ON A.ID = B.DATABASE_ID
INNER JOIN dbo.APEXSQL_BEFORE_AFTER_DETAIL C ON B.LSN = C.LSN

For the purpose of advanced reporting, here is how to extract the information with some advanced queries/procedures.

 

Auditing results

This procedure allows users to fine-tune the result set. In order to filter results by specific condition, simply add values for @Server, @Database, @ObjectName, and/or @User variables and the query result will be appropriately filtered.

 

Before-after report

CREATE PROCEDURE BEFORE_AFTER_REPORT @Server NVARCHAR(128),
	@Database NVARCHAR(128),
	@Table NVARCHAR(128),
	@User NVARCHAR(128)
AS
BEGIN
	SELECT A.[SERVER],
		A.[DATABASE],
		B.[TIME],
		B.[OPERATION_TYPE],
		B.[SCHEMA],
		B.[TABLE],
		B.[USER_NAME],
		B.[DURATION],
		B.[LSN],
		B.[RECONSTRUCTED],
		C.[COLUMN_NAME],
		C.[BEFORE],
		C.[AFTER]
	FROM dbo.APEXSQL_BEFORE_AFTER_DATABASE A
	INNER JOIN dbo.APEXSQL_BEFORE_AFTER_OPERATION B ON A.ID = B.DATABASE_ID
	INNER JOIN dbo.APEXSQL_BEFORE_AFTER_DETAIL C ON B.LSN = C.LSN
	WHERE (
			@Server IS NULL
			OR A.[SERVER] = @Server
			)
		AND (
			@Database IS NULL
			OR A.[DATABASE] = @Database
			)
		AND (
			@Table IS NULL
			OR B.[TABLE] = @Table
			)
		AND (
			@User IS NULL
			OR B.[USER_NAME] = @User
			)
	ORDER BY A.[SERVER],
		A.[DATABASE],
		B.[TIME]
END

Similar to the previously shown procedure, BEFORE_AFTER_REPORT creates a before-after centered result set describing changes (who, when, what etc).

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.

Last updated

June 27, 2018