This is a first-time user guide to help with learning the ApexSQL Audit options and capabilities during the evaluation period and prepare SQL audit controls to create and wrap the auditing documentation for data reviews.
Familiarizing with report controls
After the initial configuration is set up as described in the previous series – Evaluating ApexSQL Audit – Auditing configuration, status overview, and alerting article, creating the first report to review data is considered as the next milestone. ApexSQL Audit provides a variety of options to create, adjust and run audit data report overview so let us quickly examine it.
The SQL audit reports are manageable profiles in the Reports tab and they consist of four major control blocks as displayed in the below screenshot:
This block displays a list of report templates in use, ApexSQL Audit provides a subset of pre-defined and editable report templates to quickly create a custom or definition-ready profile that is used as a base condition when creating a report. The header options in this block provide the ability to:
- Create new report
- Export/Import report templates to/from file
Report filters enable fine-tuning the criteria for generating data output. In this section, the report template definition is created via:
- Associating a familiar report name
- Data source origin
- Filter inputs for audit data trail arguments such as: Server name, database name, logins, etc.
- Operations – filter by operation type for all supported SQL commands
- Report data grid overview – SQL audit reports can be quickly reviewed directly through the user interface that provides details on audit data via paginated output
- Report data summary – a quick overview of the report template definition in use
In addition to these control blocks, there are few options to consider in creating report data, as follows:
Preview – is a button to display audit data report preview in the data application grid
-Generate – control to extract the SQL audit report into different file formats: PDF, Word, Excel, and CSV
Date/time controls – allow you to specify data time filter and reconstruct the audit report in certain time frames and support 3 different sub-options:
All days – create the report for the entire available audit history
Relative time filter – an ability to create a report for the last X days, weeks, months
Exact time frame using from – to notation
Columns – A drop-down selection of available columns in the output data report preview and exports
Advanced – this option switch the controls for report definition into the “advanced” filter conditioning, which provides unlimited and yet precise conditioning using logical expressions
Now when all the options and controls are explained, you can easily dig into creating the first SQL audit report template, and start the auditing review data journey.
Related features and options
The aspired goal when implementing a sound auditing solution is to have proactive and up-to-date auditing reports available prior to the audit review process. ApexSQL Audit provides the ability to put reports to run automatically on a schedule. The report output can be generated and stored on a drive or sent via an e-mail notification attachment.
A complete guide on how to achieve report automation can be found on this How to create a scheduled audit report with an e-mail notification article.
Report output can be further formatted to define Date/time formats, metadata output in the header of the generated files, or manage the archive data source list:
In this configuration block, you might choose between a variety of date/time formats that will be used as a format in the audit data trail for each auditing event telling the time of event occurrence in a format of choice.
Optional output elements
Each time the report data is being generated to a file format, optional elements will be included in a document header to include some of the available items:
- Author (User that generated the output)
- Date and time when the report is created
- Legal tags
- Filter used as a condition to retrieve data output
- Total events being generated in a report
The list of databases in use when creating the report output. Over time you might generate a bunch of archive databases that are systematically created to help with normalizing the data input and also boost efficiency in managing SQL audit records retention.
Reader application role
As a part of application-level security, access to the application interface needs to be granted for an individual or group of Windows logins. There are three levels of access defined via their respective roles:
- Administrator – Has a full application access
- Power-user – Configuration and application management access
- Reader – User with the ability to create and run reports only
The Reader role can be a handy tool for non-technical persons that are mostly interested only in SQL audit report output rather than configuration or other management application features, for more visit How to allow or deny certain user access to the ApexSQL Audit GUI article.