How can I filter an extended event session by is_cache attribute?

Posted on

Question :

I created an extended event to log all successful logins into my prod server. As you can guess there are hundreds of them coming in one second so I just need to filter some of those according to whether it is a cached connection or not. I couldn’t create a filter on is_cached attribute of login event. I already filtered by [sqlserver].[client_app_name] attribute. When I watch Live Data, I can filter on is_cached attribute btw.

Here is the script of event:

CREATE EVENT SESSION deneme ON SERVER 
ADD EVENT sqlserver.login(    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')    
ADD TARGET package0.event_file(SET filename=N'D:_VTYLastLoginAuditsaudit_logon.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

I tried adding a predicate in where clause like this but it didn’t work:

WHERE ([sqlserver].[client_app_name]<>N'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
    AND [sqlserver].[is_dac]<>N'True') 

Thanks in advance.

Answer :

You just need to specify is_cached, without prefixing it with “sqlserver.”:

WHERE is_cached = 0

Leave a Reply

Your email address will not be published.