ApexSQL Audit feature highlight: Custom reports

ApexSQL Audit is a SQL Server auditing and compliance tool capable to simultaneously audit multiple SQL Server instances with ability to collect more than 200 server and database level events and to store collected data into a single tamper-evident repository database. Audited data stored in the central repository database are available via 11 built-in reports or using the unique reporting ability designed to meet the most specific needs through the high precision filtering system based on the logical expressions, while ensuring maximum flexibility at the same time since each reporting filter created can be saved and used on demand

The standard built-in reports in ApexSQL Audit are designed for quick coverage of the most common reporting needs. But even for built-in reports, comprehensive filtering is still available, allowing easy filtering out of events that are not needed or required for specific report. Each report is outfitted with a range of filters that matches the specific report’s purpose

If a predefined report comes close but is unable to meet specific requirements, or there is a requirement for creating specialized reports that have to meet particular needs, ApexSQL Audit allows designing and building custom reports tailored for precisely meeting the specific content requirements. The custom reports feature ensures full reporting flexibility in ApexSQL Audit. What’s make this feature particularly user friendly is that it allows saving of any created report and making it available for re-use whenever it is needed in just a mouse click. There is no limitation in how many reports could be created and saved

Based on the set of operators the custom filter is easy to use which allows easily building even the most complex filter expressions when needed. The image below shows the full range of data fields and operator set available for the different types of data fields:

The custom reports filter is not limited in how many filtering criteria can be used for ensuring the required precision of events displayed in report. The criteria used in the filter will define what events will be retrieved from the central repository database and displayed in the report form

Indenting of expressions within the filter is equal to open parenthesis in the logical expression that this filter relies on, while criteria grouping is equivalent to expression closed between the parentheses as shown in the example below

The above filter can be interpreted as showing all events stored in the repository database that match the following criteria:

  1. executed from SERVER2012R2-L1 client host (if application used is not Report server)
  2. executed from SERVER2012R2 client host, but only where the database is not ApexSQL Monitor and if the operation is a DDL event

The following examples will demonstrate just a fragment of the use case scenarios that can be fulfilled using the custom reports feature ( AdwentureWorks2014 database will be used)

Use case #1: An internal security regulation requires that any authorized or unauthorized server level schema related event must be investigated and reported, but there is also requirements that specific logins and their activity must be tracked and reported for specific operations, i.e. ALTER LOGIN, DROP LOGIN or CREATE LOGIN per specific users

In such a case we can create one general report that will report on any DDL change in the specified audited server, and then we can create additional reports for different logins that exist on the server

First we create the general report related to DDL changes, and when we set the filter

Now when the filter is set all that we need is to enter the name of the filter, press the Save button and report filter is created and it will be available in the left filter pane

Now filters for each specific login for ALTER, CREATE and DROP LOGIN operations can be added in the following way

If there are some additional requirements for some logins, than this could be easily created. In this particular case Grant/Deny server permissions

When we create reports for all required logins, we will have something like this

Simply by selecting the desired report and pressing the Preview button we will get the report for the selected login

Example for the SERVER2012R2\Administrator login

Or for the Nik login

Use case #2: In order to match different HIPAA auditor requirements, a general HIPAA report and sufficient number of sub reports that will address security related changes, data changes and data access

At the server level the following server operations are recommended


Recommended DDL events at the database level are shown as well

Recommended DML, security and select events are below

Additionally, it is advisable to include reporting on some error messages, since database error messages are often valuable resource for unauthorized access to SQL Server and insight in the error message might reveal potential threats. ApexSQL Audit can audit and report the database error messages

Now when it is known what HIPAA events to report, it is easy to create the general level report and any specialized reports that are needed. The below list of reports is just a suggestion, but this can be easily crafted to match various needs and requirements

In the image below the required reports are already created and titles are self-explanatory. The general report covers all events in the single filter and it will display all events that are collected on the server or database level

We can quickly generate the report for database level DDL events not related to database security by selecting the report number 05 and Preview

or selecting the report 06 for displaying the DML events report

In the same or similar manner it is possible to create the reporting strategy that will ensure quick and easy report generation on request.

Note: The above requirements are just some suggestions/ideas. The purpose is just to present the advanced abilities and advantages of ApexSQL Audit reporting approach

Our unique, powerful, and highly advanced filter is the common element shared by all three key ApexSQL Audit components – auditing, alerting and reporting. Reporting in ApexSQL Audit allows the user to transcend limitations of fixed “pre-defined” reports and offers virtually unlimited flexibility in report creation

July 1, 2015