This article describes how to configure and run before-after auditing, reporting and alerting in ApexSQL Audit.
Before-after is database auditing solution designed to track information on who and when made the change, what objects were affected, what application and host are used, and most importantly what was the original (before) and after value upon data change.
This feature leverages SQL triggers to capture data changes for DML transactions in SQL databases. It is an essential tool to track information for Inserts, Updates, and Deletes against database objects and sensitive data.
To configure Before-after auditing feature in ApexSQL Audit, follow these steps:
Run the application interface and go to Before-after tab in the main ribbon
Add database for auditing in this step
Continue by adding database tables from the Add table pane
Check the columns and DML operations that will be audited across the selected tables and hit the Apply button to finish
The auditing criteria can be also further polished via advanced filters to achieve greater precision via logical conditions that specify exact cases on which before-after auditing will be triggered.
To specify conditions, advanced filters have to be enabled before adding and combining the logical expressions:
Once the configuration is applied, the set of database triggers is created upon selected tables to collect the data on DML activities and fill the auditing trail, the next section will explain how to run reports and configure alerts.
Reporting and alerting
The easiest way to review before-after database auditing data is by using the pre-determined reporting template Before-after auditing that is available when clicked on the New button in the Reports tab:
Additionally, we can use multiple filters to enhance the precision of the report by specifying servers, databases, logins, application and many more such as before and after values, also as before-after columns that will allow one to precisely create a report upon specific data:
To run the audit report, click the Preview button which will extract data directly into the application interface, or Generate to export the data in available output formats:
Apart from reporting, it is also very easy to create an alert to trigger whenever a specific case on data change occurs, which helps to identify any potential unwanted DML changes. The quick alert configuration wizard allows utilizing on the before-after alert type and set up the conditions on which an alerting notification is being sent:
Moving forward through the wizard, the key step is to configure alert conditions upon which will alert raise a notification:
Historical alert notification data is available in the History alert tab, while real-time notifications can be also delivered via e-mail and identify any alerting activity fast enough.
To learn more on how to configure it, consult this How to create a custom alert with an email notification guide: