Forum Discussion

Alktor's avatar
Alktor
Copper Contributor
Jul 05, 2024

Extended Event with app_name doesn't work properly

Hello,

 

When I create an extended event to capture all the SELECT on the instance, it works :

CREATE EVENT SESSION [YourSession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%')))
ADD TARGET package0.event_file(SET filename=N'S:\Program Files\Microsoft SQL Server\MSSQL16.FORMATIONMSSQL\MSSQL\Backup\YourSession_Target.xel',max_file_size=(2),max_rollover_files=(2))
WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

 

I can see all the SELECT that I execute on SSMS, but, when I use the app_name ACTION, it doesn't work anymore :

CREATE EVENT SESSION [YourSession] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(SQLSatellite.AppName,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%')))
ADD TARGET package0.event_file(SET filename=N'S:\Program Files\Microsoft SQL Server\MSSQL16.FORMATIONMSSQL\MSSQL\Backup\YourSession_Target.xel',max_file_size=(2),max_rollover_files=(2))
WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

 

Do you have any clue about why the extended event doesn't capture the SELECT when the action AppName is selected ?

It is a bug ?

It is because like I select the AppName field, if my session doesn't have any "app name" it doesn't capture it ?

 

Regards,

Alktor

  • rpi_dwillis77's avatar
    rpi_dwillis77
    Copper Contributor

    Hello,

    I'm having the exact same issue when trying to capture login events - if I include the AppName field in the session, I get no results. If I remove it, I get results as expected.

    This is on SQL 2019. If anyone knows why this is happening, some insight would be much appreciated!

    Best regards

Resources