Return to Question

added 1 character in body; edited title

Source Link

edited Dec 29, 2016 at 11:28

25.1k
13
53
98

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

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 iI 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 iI watch Live Data, I can filter on is_Cachedis_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 didntdidn'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.

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

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 it is 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 didnt work:

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

Thanks in advance.

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

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.

Source Link

asked Mar 17, 2016 at 14:34

Bahtiyar Samet Çoban

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

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 didnt work:

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

Thanks in advance.

sql-server