SQL Server auditing tool reporting feature overview

Applies to

ApexSQL Audit

Summary

In this article, we are going to demonstrate the reporting functionality of the SQL Server auditing tool through the step-by-step scenario that can be utilized to build your own working routine while dealing with the creation of new reporting templates, generating desired output, and configuring scheduled reports.

Description

ApexSQL Audit is a comprehensive SQL Server audit solution which is storing complete information on the auditing evidence inside the Central Repository database that is queried to create and run customized reports through the robust application user interface.

One can choose from a voluminous library of pre-defined reports with the ability to determine highly granulated filter conditions that address security auditing and compliance regulatory requirements.

Complete feature management is done inside the Reports tab in the main application ribbon. By default, the reporting templates list is populated with the Complete audit trail item, a pre-made and ready use template to generate the full SQL Server auditing report evidence:

Creating a new report

To create new report template, find the New button in the header controls of the Reports pane that is located on the left side of the application interface in the Reports tab. The next step is to choose from the library of pre-determined reporting templates that are organized in a few blocks around security, compliance regulation standards, and more. For the demonstration purpose, we decided to create a monthly GDPR template to use:

Finding the most suitable template to be used as a base for further report customization will load operation filters criteria which can save an immense amount of time while fine-tuning the report filter conditions.

Once the reporting template is selected, the very next step is to define the descriptive name of the report and adjust the filters upon which data will be generated:

Data is classified into four different Event source types:

• Auditing – retrieves the audited data that is collected accordingly to the auditing configuration
• Before-after – shows the actual data changes performed with DML statements with before and after values
• Sensitive columns – shows the read accesses against columns that contain sensitive data
• Internal – Historical data that demonstrates user’s activity and provides self-auditing reports to track configuration change updates, integrity checks, maintenance tasks and more

Filter condition is further determined with the selection of the objects from the drop-down lists for the following entities:

• Servers
• Databases
• Schemas
• Objects
• Logins
• Client host
• Applications
• Internal event type
• State
• Text data
• Before value
• After value
• Before-after column
• Sensitive column

The next and very important configuration block is to include the SQL operations to be filtered against, with a plethora of choice since there are almost 200 different SQL statements are being evident in SQL server audit log.

Once the aforementioned details are finite, let’s jump to defining the time span and therefore determine the data time range which can be defined in three different manners:

• All days – retrieves the data ever since auditing was put to motion
• Relative time filter – robust filter to retrieve data for the Last “x”:
  • Minute(s)
  • Day(s)
  • Month(s)
  • Year(s)
• Specific time range “from” – “to” filter

The SQL Server audited data is displayed in the form of a data grid with the ability to include or exclude certain columns that will be shown in a result set. The customization is made by selecting the list of desired columns in the Columns drop-down list:

The complete list of reports’ columns include:

• Date – Exact time when the statement is executed
• Server – What SQL Server was affected with the transaction
• Database – What was the database context of the SQL statement
• Login – Who performed the transaction
• Application – What application was used
• Client host – Information on client host from which the transaction is made
• Schema – Schema name
• Object – Database object name
• Operation – SQL operation
• Text data – Text of the SQL statement
• Access list – List of accessed objects in a transaction
• State – Success/Fail operation state
• Internal event type – Classification of internal events by type

The preview of the configured template can be reviewed and tested at any given time during the setup which is a handy option since it allows validation of the current state of the template even before saving changes by choosing the Preview button:

Once the sweet spot is found and all the customization is done, and audit report template is ready for use, the final step is to commit the changes by clicking the Save button:

Once the audit report template is created and saved, the list of saved reports will be updated in the Reports pane on the left side of the application interface. These custom reports will be grouped per user that created them:

The list is populated with saved templates that are defined even when multiple personas manage the configurations where the templates are visible and shareable between them.

It is also recommended to utilize the Export/Import options which is a handy tool to replay the configuration fast in specific cases where needed e.g. Central Repository migration and similar events:

Advanced configuration management

The explained configuration process is considered to be used in the majority of use cases, but for advanced needs, advanced measures have to be taken. The configuration mode is switched by choosing the Advance button:

The advanced filter allows the highest level of granularity in creating reporting filter rules and thus allowing precise reporting of the required data. 

The advanced filter is founded on an intuitive set of operators that can be chosen when creating a filter condition for reporting. The following shows available for the different types of data fields:

Application name, After value, Before value, Before-after column, Client host, Database name, Database operation, Date, Event source, Event type, Login name, Object name, Schema name, Server, Server operation, State and Text data

Besides very luxury data fields portfolio the following available operators, is, is not, contains and does not contain – allows one to make tremendous granularity in a single expression with no limits of the number of filtering criteria applied, consult the Advanced SQL Server auditing filters article to learn more about advanced filtering and grouping.

Here is the quick example of the advanced filter conditions for the previously made GDPR – Monthly reporting template:

Output generating

The reports can be generated in four available formats:

• PDF
• Word
• Excel
• CSV

To build a desired file format of the selected template, select the proper item in the drop-down list of the Generate option:

Once initiated, the save the exported audit report as dialog will be prompted to define the location path on the file system with pre-loaded default path:

C:\Users\[USER_NAME]\Documents\ApexSQL\ApexSQL Audit

The report output can be also scheduled to run automatically and deliver the output files via the e-mail notification.