1

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:\_VTY\LastLoginAudits\audit_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.

Improve this question
2
  • what version of sql server are you using ?
    – Kin Shah
    Commented Mar 17, 2016 at 14:37
  • In your question you mention is_cached, but your code sample attempts to filter on is_dac. Which one of the two are you trying to filter?
    – spaghettidba
    Commented Mar 17, 2016 at 14:50

1 Answer 1

Reset to default
3

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

WHERE is_cached = 0
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.