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

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 SERVER2012R2-L1 AND Database is TestSignature AND Operation is DDL 
     AND ( Application is Report Server AND Client host is not Computer_T1 
          AND (Text data does not contain " START FULL POPULATION "
               OR Text data does not contain " SET CHANGE_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 bagety2, ArchiveCrd_20150524_063042, TestSignature AND Database 
    operation is DDL,DML 
         OR (Login is Adi, Nik  
              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 names Adi and Nik, as they will be excluded from auditing (defined by the rule created via the sub-group):

    • any operations on the ArchiveCrd_20150524_063042 database (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 bagety2, ArchiveCrd_20150524_063042, TestSignature  
    AND (Server operation is Security OR Database operation is DDL,DML, Security  
         OR (Login is Adi, Nik   
              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 i.e. server grouping per filtering rule. In order to achieve this, for each server which have to be grouped the All servers filter type radio button must be selected

After the All server radio button is selected, the filter for that instance is delegated to the settings defined in the All servers tab and all filter settings must be defined there. All SQL Server instances for which All servers is selected can be now set only from the All servers filter tab

In the following example, all eight audited instance’s filter type is set to All servers and instances will be grouped where each group will utilize its own set of filter rules

As it can be seen from the image, three auditing groups are defined, each with its own unique filtering rules that applies only to the SQL Server instances defined within the each group via the Server is condition. In this way, full control of all auditing settings for all instances can be established from one place. This allows easy and clear overview of defined auditing filters and simple change of the filter rules

Of course, if there is a need for any specific instance to comply not just to the filtering rule set for that group, but also to filtering rules set for the others group, it can be just added in the required groups via the Server is condition that defines that groups.

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

Additional benefits from the filtering organization in ApexSQL Audit will be particularly pronounced in situations when a temporary requirement for more precise or different auditing rule for any of the auditing instances is required. Switching to a new auditing requirement will not require any change in the grouped filter settings, since the Advanced and Simple filter types can be used as independent filtering solutions. Just select the appropriate Filter type radio button in the Auditing tab and set a required auditing filter. Once selected, the advanced or simple filter will fully override the settings defined in the All servers filter and the filter sign will be added next to the instance name which is configured individually via advanced or simple filter. If needed, both advanced and simple filters could be defined and ApexSQL Audit will recognize and store each of these settings, so all that has to be done is simple mouse click on the desired Filter type radio button to change the auditing requirement for the specified SQL Server instance

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