Reputation: 21
How do I configure the filter in Extended Events in SQL Server 2016 so that it does not log when certain columns have a NULL value, e.g. sqlserver.nt_user
. I can't use IS NOT NULL
or length(sqlserver.nt_user) > 0
in the WHERE
condition.
I know I can filter later when pulling data from the .xel files but I want these files to be as small as possible because the process of querying .xel files is lengthy.
This is my attempt, but it is not valid:
CREATE EVENT SESSION [AuditLogins] ON SERVER
ADD EVENT sqlserver.login (
ACTION (
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.server_principal_name,
sqlserver.username
)
WHERE
(
sqlserver.session_nt_user IS NOT NULL
AND sqlserver.username IS NOT NULL
AND sqlserver.nt_user IS NOT NULL
)
)
ADD TARGET package0.event_file (
SET filename = N'D:\MySQLAudit\AuditLogins.xel',
max_file_size = (50),
max_rollover_files = (10)
)
WITH (
MAX_MEMORY = 4096 KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION [AuditLogins] ON SERVER STATE = START;
GO
Upvotes: 1
Views: 79