Applies to
Summary
This article will show SQL Server audit report customization capabilities through specific use case presentations.
Description
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 the plethora of 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. This article will show those capabilities through a couple of examples.
SQL Server audit report overview
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 should meet needs, ApexSQL Audit allows designing and building custom reports tailored for precisely meeting the specific content requirements. The customization capabilities ensure full reporting flexibility in ApexSQL Audit. What’s make this particularly user-friendly is that it allows saving of any created report configuration and making it available for re-use whenever it is needed in just a mouse click. There is no limitation in how many configurations could be created and saved.
- More comprehensive overview can be found in the article: SQL Server auditing tool reporting feature overview
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 to how many filtering criteria can be used for ensuring the required precision of events displayed in the 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:
- executed from RECHIE-PC\RECHIE client host (if application used is not Microsoft SQL Server Management Studio)
- executed from RECHIE-PC\RECHIE client host, but only where the database is not Adventure Works 2014 and if the operation is a DDL event
- Details about the logic of the advanced filters can be found in the article: Advanced SQL Server auditing filters
The following examples will demonstrate just a fragment of the use case scenarios that can be fulfilled using the custom reports feature (Adventure Works 2014 database will be used).
Use case #1
An internal security regulation requires that any authorized or unauthorized server-level DDL event must be investigated and reported, but there is also requirement 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 panel:
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, then 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:
Use case #2
In order to match different HIPAA auditor requirements, a general HIPAA report and a sufficient number of sub-reports will address security-related changes, data changes, and data access.
At the server level the following server and database operations are recommended:
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:
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 – SQL Server auditing, alerting, and reporting. Reporting in ApexSQL Audit allows the user to transcend the limitations of fixed “pre-defined” reports and offers virtually unlimited flexibility in report creation.
