MegaCalkins
MegaCalkins

Reputation: 21

Filter Extended Events to get rid of the NULL value

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

Answers (0)

Related Questions