ApexSQL Audit feature highlight: Advanced filtering

ApexSQL Audit is a SQL Server auditing and compliance tool that audit multiple SQL Server instances and is capable to collect over 200 SQL Server events and stores audited data into a tamper-evident central repository database. Captured information is available through a range of built-in reports predefined reports or via custom report ability designed to meet specific granular user requirements.

The standard audit filter in ApexSQL Audit is based on the so called Simple audit filter, which allows fast and easy selection of events that will be audited for each audited SQL Server instance separately. Even though it is called “simple”, it still allows high level of granularity.

Here is an example of server-level filter:

And here is an example of a database-level filter:

Besides allowing selection of each single auditing event, it also allows including/excluding specific logins, application and/or object from the process of auditing. As such, this filter can fulfil all standard auditing demands.

The advanced filter allows the highest level of granularity in creating auditing filter rules and thus allowing precise auditing and collecting exactly the required data. This allows the application to minimize the negative effects that auditing process imposes on SQL Server where the repository database is hosted, but also to maximize resources availability to other parts of the system by reducing event collection and network/storage overhead.

The Advanced filter relies on an intuitive set of operators that can be chosen when creating a filter condition for auditing. The data fields selected in the filter determine the operators that will be offered for filtering. The following shows which operators are available for the different types of data fields:

Application name, Client host, Database name, Login name, Object name, Schema name, Text date – is, is not, contains and does not contain

Database operation, Object type, Server, Server operation – Is and is not

The image below lists all data fields that are available in the advanced filter.

There is no limit to the number of filtering criteria use to achieve the desired precision of auditing results. The filtering criteria used will form the auditing rule that will determine within which SQL Server instances/databases SQL Server events will be audited and when collected stored in the central repository database.

Additionally, the ApexSQL Audit advanced filter can use criteria grouping. Grouped criteria will operate as a single unit related to the parent condition to additionally determine parent query criteria or to make this more understandable, grouping is similar to using parentheses around a mathematical calculation or logical expression to alter the sequence of evaluation.

The above filtering rule can be presented as a logical expression:

Server is ZWERKA\SQL2014 AND Database is Adventure Works 2016 AND Operation is DDL
	AND ( Application is Microsoft SQL Server Management Studio AND Client host is not MAKINA
	AND ( Text data does not contain " START POPULATION "
		OR Text data does not contain " SET _TRACKING"))

The condition indentation should be treated as the open parenthesis in front of that condition while conditions that are grouped are conditions enclosed between the parentheses.

As is visible from the above example, the AND or OR operators in front of the group applies to the whole group implying that those grouped conditions will be evaluated as a single unit within the parent query

The following examples will demonstrate how the auditing filter can allow meeting precise auditing requirements, using conditional filtering

  1. A defined set of requirements on what events have to be collected is established. For two users there are specific requirements to audit security events related to login/user changes

    In the particular example the filter is set to audit the databases specified in the Database name is condition when DDL or DML events specified in the Database operation is condition are fulfilled but also only two users Adi and Nik will be additionally audited when they perform any server or database operation specified in the Server operation is/Database operation is filter conditions. The logical expression would be:

    Database is TestApexSWQLLog, Adventure Works 2016 
    	AND Databaseoperation is DDL,DML
    	    OR (Login is Zwerka\Nebojsa
    		AND (Server operation is Alter login, CreateLogin, DropLogin
    		    OR Database operation is AlterUser, CreateUser, DropUser))
  2. In this case only specific users are to be excluded from auditing but only for some specific auditing events. and to be excluded completely from auditing when working with specific database

    Here, the auditing filter is set to fully audit databases specified in the Where Database name is condition for any server level security operations or DDL, DML, Security database level operations except for login name Zwerka\Nebojsa, as they will be excluded from auditing (defined by the rule created via the sub-group):

    • any operations on the databases (defined via the Database name is not condition in the group)
    • Insert, Update, Bulk insert statements (defined via the Database operation is not condition in the group) or Grant server permissions, Revoke server permissions (via the Server operations is not condition in the group)

    For those who prefer to visualize this auditing condition via the logical expression:

    Database is TestApexSQLLog, Adventure Works 2016
    	AND (Server operation is Security OR Database operation is DDL,DML, 
    OR (Login is Zwerka\Nebojsa AND (Database operation is Insert, Update, Bulk insert OR Server operation is Grant server
    permission, Revoke server permission
  3. Besides the ability of utilizing the filter condition related to the SQL Server or database level conditions, there is also a Text data filtering condition. It allows you to set an auditing filter that will check the T-SQL query text of the executed events to find the matching string and to reject or to include that event according to the condition set. The Text data filter allows using Is, is not, contain, do not contain condition

    If we want to audit SELECT statements for example, but we don’t want to audit when counting different values in column using the SELECT DISTINCT clause. Here is the example:

    SELECT DISTINCT JobTitle FROM HumanResources.Employee

    If we don’t want to collect SELECT DISTINCT when it is executed on specific column (JobTitle column in this particular case), we can exclude that from auditing via Text data filter condition

    The defined auditing filter will audit all SELECT operations when executed against AdwentureWoorks2014 database HumanResources.Employee table, but will exclude from auditing every event where T-SQL contain words “DISTINCT” or “JobTitle”. In the similar way it is possible to set the rule that can fulfill a very complex/precise auditing requirements. The Text data filter should be used when the highest level of auditing precision is required.

Besides being able to ensure more precise auditing, it can be additionally used for applying the same filtering conditions to specific groups of SQL Server instances. To achieve this, users can simply export auditing configuration of specific server and import it directly to other audited SQL Server instances.

Practically, there is no limitation in the way the advanced filter can be used and it can be determined by users to define what granularity/precision level they want to achieve and how they want to achieve that

Finally, but not less important is that ApexSQL Audit alerting system utilize the same advanced filter technology which allows equally high precision of alerting that can fully match and complement the auditing precision and thus ensuring alerts to be raised exactly and only for the precisely specified events and under strict condition that users can define

ApexSQL Audit utilizes a highly developed advanced filtering system that breaks all boundaries on conventional filtering empowering users to fully customize audits and alerts precisely to their specifications.

June 11, 2015