ApexSQL Audit database audit reporting module

The ApexSQL Audit web-based reporting module allows you to access reports from any computer, including remote computers. It provides a range of built-in reports and a custom report designer. Reports show the audited records captured in the Central Repository Database using the filtering and sorting options you have specified. Besides showing reports in an Internet browser, you can export them into several common formats.

To access ApexSQL Audit reports from the GUI, click Web reports in the menu:

Web reports

Quick tip:

To open reports without using the GUI, open the following address in your browser:
http://localhost:41001/Reports/Common/Common.aspx

ApexSQL Audit Reports page

ApexSQL Audit reporting module has several tabs:

Common reportsApexSQL Audit built-in reports are placed here. You can use them to see captured auditing events, changes in auditing settings and integrity checks, changes in schema and data, data access, the history of roles and users, logon history etc.

Custom reports – here you can create custom reports that show the exact audited records you want to see. You can use various filters to narrow down the result set, order ascending or descending by a specific column value and select the columns you want to see. Custom reports can be saved and reused.

Exported reports – shows a history of reports you have exported into PDF, Word, or Excel so you don’t have to remember where on your hard drive you stored them.

Options – shows available time formatting and search options, as well as the central repository database used for reporting.

Common reports

There are 30 built-in reports divided into several groups. For all reports, you can use a time range filter to filter the records by a specific timeframe. Otherwise, all the records captured in the central repository database will be shown. The records can be further filtered by specifying additional parameters (a specific SQL Server, or database name, login used, etc.) and by selecting operation type (e.g. create, drop, delete, alter, truncate, etc.).

ApexSQL Audit report parameters

Note: Not all of the listed parameters are available in all common reports.
An additional feature that improves report readability is Order by. To order records in a result set by a specific parameter, select the parameter to order by from the drop-down list and ascending or descending order, before clicking the Preview button.

Order by parameter

All of the reports can be exported into PDF, Word and Excel files. To do this, just click Generate and select one of the formats listed.

Exporting a report

Common reports are divided into 4 groups – General, Auditing overview, Change and activity auditing, and Security.

General

Complete audit trail – shows all events that happened on audited SQL Server instances.
Backup and DBBC activities – shows all backup, restore and DBCC commands. To narrow down the results, you can specify time period, SQL Server instance, database, login, client host name and application.

Auditing overview

Auditing settings history – shows all the changes made to Server filter and Database filter settings in the ApexSQL Audit GUI.
Auditing integrity checks – shows the time the auditing integrity was checked and its status.

Change and activity auditing

DDL history – shows all captured Data Definition Language (DDL).
DML history – shows all captured Data Manipulation Language (DML) and Data Control Language (DCL) operations affecting the audited SQL Server instances, including the login which made the changes.
Access history – shows all SELECT and EXECUTE operations and the affected objects

Security

Security configuration history – shows the operations related to SQL Server logins (Create, Drop, and Alter login, Grant and Revoke object permissions) executed on a specific SQL Server instance.
Logon activity history – shows all logon activities and their status, etc. on a specific SQL Server instance by a specific user.
Permission changes – shows all permission changes for a specified login including the login which made the changes.
Unauthorized access – shows all unsuccessful login attempts on a specific SQL Server instance, database, object or machine

As an example, we’ll show how to see all the security changes made on the FujitsuSQL2012 server on 064/04/2015 between midnight and 11 PM. Use the Permission changes report which shows the SQL Server login and role operations (Deny, Grant, and Revoke database and server permissions) executed on a specific SQL Server instance:

Permission changes report

Custom reports

If you need a specific report that is not provided in the Common reports tab, create a report of your own in the Custom reports tab that shows the records you need.
When creating a custom report, create a filter by using a drag-and-drop technique to place a column tile into the Filter section. Any column available in a report can be used to create a filter. Depending on where you drop the tile, the logical operator AND or OR will be added to the filtering condition. In the following example, depending on where we place the Server tile, we can create the following filters:

Start time = 06/04/2015 12:00 AM AND Server = “server_name”
Or
Start time = 06/04/2015 12:00 AM OR Server = “server_name”

Generating Custom reports

When specifying a value for the filter added, select one of the following operators: = “equal to”, != “is not equal to”, > “greater than”, < “less than”, * “like”, and !* “not like”. Note that multiple conditions can be created.

Specifying filter values

To specify the columns that will be shown in the report, either drag the column tiles from the Columns section, or click Add all and then remove the ones you don’t want in the report.

Specifying the columns shown in report

For example, to create a report that will show all the events that happened after 06/04/2015, made by all the users except Spock on all the databases except the master, use the following settings:

Generated Custom report

When you create a report, click Save to export the report definition into an XML file, so you can use it later. To open an XML report, click Load
As in common reports, the options to order by and export into a PDF, Word or Excel file are available.

Exported reports

When the user exports a report in a specified format a template will be saved in a file folder for a future use.

Options

Set the date/time format used in the reports, whether to use the SQL Server local time or convert it into the UCT time and whether to treat the filters set/parameters used as case sensitive.

Formatting options