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:
To open reports without using the GUI, open the following address in your browser:
ApexSQL Audit reporting module has several tabs:
Common reports – ApexSQL 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.
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.).
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.
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.
Common reports are divided into 4 groups – General, Auditing overview, Change and activity auditing, and Security.
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 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 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:
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”
Start time = 06/04/2015 12:00 AM OR Server = “server_name”
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.
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.
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:
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.
When the user exports a report in a specified format a template will be saved in a file folder for a future use.
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.