How to achieve high quality auditing trail with the minimum number of auditing events

Configuring auditing in ApexSQL Auditing is a quick and easy task. One simply needs to define which specific SQL Server events should be audited on both server and database levels, as can be seen in the Auditing SQL Server databases – initial configuration article and then apply the configuration to complete the setup process. Auditing will immediately commence, and user can then query their central repository databases to create comprehensive reports and use various filters to ensure they can produce high quality and precise reports.

Nevertheless, even though excess information can be filtered out in the reports, the better practice is to not audit events with zero or minimal auditing value at all, since excess auditing may result in various additional expenses, such as:

  • Longer processing time when storing audited information into the central repository
  • Higher disk space requirements – which can take especially heavy toll in the high-traffic SQL Server environments, especially in cases when traces created by ApexSQL Audit have to be temporarily stored on the local drive in case of higher priority SQL Server processing
  • Slower report creation due to more complex filters
  • Time investments when creating a new report every time, due to the requirement to remove excess data

With the above in mind, there is no reason why ‘all’ events should be audited in any case and add unnecessary encumbrance on their auditing or production environments. In this article, we are going to show how to use different ApexSQL Audit assets, including auditing filters and features which will allow high-end auditing jobs to be put in motion over different SQL Server instances with minimal auditing trails ensuring best performance and lowest requirements.

Server filters

Once ApexSQL Audit is installed, auditing does not commence immediately – the auditing filters must first be configured.

First, add SQL Server instance for auditing by choosing SQL Server for auditing from the drop menu in the Server pane in the Configuration tab (or typing the name manually) and clicking on the ‘Add’ button.

Also, provide appropriate credentials and choose connection options before completing the process.

Now, specific server-level events can be added by checking the checkboxes next to each server event in the Operations tab.

Next, on the Applications tab, it is generally advised to exclude all changes made through the applications which are not required for auditing. For instance, if you have an application performing frequent logins, checks, or changes which is not part of the auditing requirements, be sure to exclude those in this filter by checking them in the applications tab

Quick tip icon

Quick tip:

If the application, login or object name is not shown in the displayed filter lists, type its name in the ‘custom entry’ field, click ‘Add’ and ensure item is checked in the list to add this specific filter

Another important filtering tool is the ‘Logins’ filter. Here, we can choose to include or exclude any SQL logins which do not need to be audited per existing requirements. As before, simply check specific logins, or add custom ones, and opt to either include or exclude them by choosing the appropriate radio button.

Database filters

Adding databases for auditing is also simple and easy. As before with the SQL Servers, in the Server pane, first select the SQL Server instance which hosts the database, and click ‘Add database’ button.

Now, in the ‘Add databases’ pane, check databases for auditing and click on the ‘Add’ button.

As before, it is required to check the database events which will be included in the auditing task.

One of the most frequent mistakes made when auditing job is being set is skipping the ‘Object’ filter on the database levels. More often than not, there are hundreds or thousands of tables, triggers, stored procedures and other database objects with a vast amount of traffic which is frequently irrelevant for the auditing requirements and should be hence excluded to minimize the trail and traffic size. Choosing to include only a specific object in the auditing task can decrease auditing trail tenfold or more in most environments.

To configure object filters, as was the case with already mentioned filters, check appropriate objects and opt for a radio button which will allow you to include or exclude them from the auditing job.

Quick tip icon

Quick tip:

A great starting point for auditing configuration are predetermined compliance templates based on the most existing compliance standards such are GDPR, HIPAA, PCI and more. These include minimal auditing events to meet compliance standards, making them ideal starting points before adding more operation, application, login or object filters, both on server and database levels

Advanced filters

Now that we’ve seen how to use various filters to control the auditing output using ApexSQL Audit simple filters, let’s take this job one step further, straight to the advanced auditing filter.

Advanced filters in ApexSQL Audit take a different approach and combine both server and database levels together. They are created by combining an unlimited number of different conditions to filter the auditing trail. They can be accessed by clicking on the ‘Advanced’ button and cannot be used together with the simple filters – these will be converted and overwritten.

Conditions are added by clicking on the green ‘plus’ sign and can be combined in various ways. Different operators, grouping and more can be used to achieve pinpoint precision and ensure that the auditing trail is indeed minimal. With the advanced filter, filters can be configured to audit specific database or server events for one login, and completely different operations for another one, why monitoring others only for the third set of events etc.

  • More on how ApexSQL Audit advanced filters can be used and configured for highest auditing precision is described in great details in the ApexSQL Audit feature highlight: Advanced filtering article which also offers several great tips on how to group and combine conditions

Before-after auditing

In the Before and after auditing in SQL Server article we show how to setup and configure before-after auditing on a table column level to capture changes before and after the insert, update or delete operation has been executed. DML changes audited by before-after auditing are usually the most frequent changes in the databases since they actually feed and change the data in the database tables. With this in mind, it is important to give some special attention when configuring auditing of DML operations since incorrect configuration can get out of control pretty quickly.

The first thing to keep in mind when configuring before-after auditing is that it goes all the way up to the column-level, which means that we can include or exclude only specific columns in our auditing job, minimizing the output this way. So, first use ‘Add table’ button to include specific tables in the configuration, and then dive deep down to the column levels and choose which one will be part of the auditing job.

The second thing to keep in mind regarding before-after auditing is that it can combine advanced filter with the table filter above. So, once the column-level configuration is completed, we can add conditions to further filter our auditing. For example, we can choose to only audit operations on our included table columns in case when a SQL Server Management Studio is used by a particular SQL login connecting from a specific client host

With this, we conclude our auditing configuration recommendations and best practices.

Keep in mind that you may not be able to achieve minimal auditing trails immediately and that some tweaking will be needed. So, a good practice would be to:

  1. Configure auditing filters for minimal auditing trail as per known requirements
  2. Run auditing reports to investigate auditing trail and isolate any events which may not actually be required and can be removed
  3. Keep a watch for any ApexSQL Audit alerts which may indicate huge growth of the central repository database and alert you on potential flaws.
  4. Manually create or schedule regular auditing reports to be automatically created on a predetermined frequency to keep track of your auditing trail. If the latest report is several time larger than those before it, some changes have probably occurred in your SQL Server environment which could be a flag to check and appropriately adjust your auditing filters to accommodate for the latest changes

FAQ

How can I ensure my auditing job does not impose impactful performance hinderance on my production environment?

Aside from checking or keeping track on any potential CPU or memory usage in low-performance environments, it is a good practice to check if traces (including audited data) created by ApexSQL Audit are timely processed by SQL Server, or if they are piling up in the temporary folder (default: c:\ProgramData\ApexSQL\ApexSQLAudit\Auditing\<SQL Instance Name>\ActiveTraces\), which can be a flag that the audited data is being added faster than it can be processed which suggests an immediate reevaluation of the auditing configuration.

What is the expected performance impact of ApexSQL Audit on central and audited instances?

ApexSQL Audit has a minimal performance overhead, equivalent to running the default trace which means it can audit huge amounts of events with minimal/no performance impact.