How to add multiple filters on Server Audit of SQL Server?

Question

I want to filter SQL Audits so that I do not want to capture events triggered by certain users and certain schema. In one of the existing Server Audit, I found the filter predicate as

(
[schema_name]<>'sys' AND 
[server_principal_name]<>'SILVER\Distributor' AND 
[server_principal_name]<>'SILVER\Replicator' AND 
[server_principal_name]<>'SILVER\Merger' AND 
[server_principal_name]<>'SILVER\Collecter' AND 
[server_principal_name]<>'SILVER\Reporter' AND 
[server_principal_name]<>'SILVER\Starter' AND 
)

I think it should be OR and not AND. As per TSQL, it looks like above condition will never be satisfied. AND means all of the conditions must be satisfied. I did read the logs using function sys.fn_get_audit_file and did not see any records belonging to above restricted users and schema. It looked like above predicate worked though.

Is AND here acting like a separator of the rules.

Could you please explain this?

It you prefer `or` then the predicate should be `NOT ([schema_name] = 'sys' OR [server_principal_name] = 'SILVER\Distributor' /*more*/)` — Alex Kudryashev, Nov 01 '18 at 21:55
Sorry @AlexKudryashev. I did not understand. I do not want to capture from `sys` schema and I do not want to capture events from all above users. — Merin Nakarmi, Nov 01 '18 at 21:57
Predicates `a<>b and c<>d` and `not (a=b or c=d)` are equivalent. — Alex Kudryashev, Nov 01 '18 at 22:07
@AlexKudryashev You made things so clear. Could you post it as an answer? — Merin Nakarmi, Nov 01 '18 at 22:10

score 0 · Accepted Answer · answered Nov 01 '18 at 22:20

You can change your predicate

(
[schema_name]<>'sys' AND 
[server_principal_name]<>'SILVER\Distributor' AND 
[server_principal_name]<>'SILVER\Replicator' AND 
[server_principal_name]<>'SILVER\Merger' AND 
[server_principal_name]<>'SILVER\Collecter' AND 
[server_principal_name]<>'SILVER\Reporter' AND 
[server_principal_name]<>'SILVER\Starter' AND 
)

to its equivalent which uses or

NOT (
[schema_name] = 'sys' OR 
[server_principal_name] = 'SILVER\Distributor' OR 
[server_principal_name] = 'SILVER\Replicator' OR 
[server_principal_name] = 'SILVER\Merger' OR 
[server_principal_name] = 'SILVER\Collecter' OR 
[server_principal_name] = 'SILVER\Reporter' OR 
[server_principal_name] = 'SILVER\Starter'  
)

or even better readable

[schema_name]<>'sys' AND 
[server_principal_name] NOT IN (
'SILVER\Distributor',
'SILVER\Replicator', 
'SILVER\Merger',
'SILVER\Collecter',
'SILVER\Reporter',
'SILVER\Starter' 
)

I get a syntax error when using NOT IN with SQL 2012. – RonJohn Jun 29 '21 at 02:44 — RonJohn, Jun 29 '21 at 02:44

How to add multiple filters on Server Audit of SQL Server?

1 Answers1