0

I have an extended event session setup. This is the definition.

CREATE EVENT SESSION [Successful-Logins] ON SERVER
ADD EVENT sqlserver.connectivity_ring_buffer_recorded (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_principal_name,
    sqlserver.session_id, sqlserver.sql_text ) ),
ADD EVENT sqlserver.login ( SET collect_options_text = ( 0 )
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) ),
ADD EVENT sqlserver.logout (
    ACTION ( sqlserver.client_app_name, sqlserver.client_connection_id,
    sqlserver.client_hostname, sqlserver.context_info,
    sqlserver.database_id, sqlserver.server_instance_name,
    sqlserver.server_principal_name, sqlserver.session_id,
    sqlserver.sql_text ) )
ADD TARGET package0.event_file ( SET filename = N'Successful-Logins' ,
                                  max_file_size = ( 10 ) ,
                                  max_rollover_files = ( 0 ) )
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 = ON ,
        STARTUP_STATE = OFF )
GO

My intention is to query sys.fn_xe_file_target_read_file regularly with a filter on server_principal_name. Unfortunately, this field is not always populated. From the same client app and client host, I have some events where server_principal_name is NULL and other where it is populated.

I'm using this query for testing.

SELECT  --TOP 10 
        CONVERT(VARCHAR(19),DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')),120) AS [timestamp] ,
        xevents.event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
        DB_NAME(xevents.event_data.value('(event/action[@name="database_id"]/value)[1]', 'int')) AS [database_id],
        xevents.event_data.value('(event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(max)') AS [server_principal_name],
        xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') AS [client_app_name],
        xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname]
FROM sys.fn_xe_file_target_read_file
            ('C:\Program Files\Microsoft SQL Server\MSSQL11.INSTANCENAME\MSSQL\Log\Successful-Logins*.xel',
            NULL,null, null) f
CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
ORDER BY DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
        xevents.event_data.value('(event/@timestamp)[1]','datetime2')) DESC 
  • Which events are returning NULL? – SQLRockstar Jan 15 '15 at 19:20
  • Both login and logout. Out of 3387 events only 15 are NOT NULL. – SQL Hammer Jan 15 '15 at 19:37
  • What is @@VERSION? I tried to repro and both SQL and Windows auth logins populated correctly in all cases. Is there a logon trigger? Are any of these logins coming in through AD group membership without an explicit login name for them? How about contained database users? – Aaron Bertrand Jan 16 '15 at 3:41
  • Great questions. No logon triggers. No these particular users are all AD logins (non-groups). No contained database users. @@VERSION = "Microsoft SQL Server 2012 - 11.0.5532.0 (X64) Jul 14 2014 15:00:27 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)" – SQL Hammer Jan 16 '15 at 13:34
  • 1
    It's funny, four years ago I challenged Derik for a repro. Today I hit it myself (it was a domain user accessing via a group through SSMS) and I'm still not sure how - but I added username in addition to server_principal_name to the session definition, and then coalesce on the output. Haven't hit NULL again since (but I'll keep watching). – Aaron Bertrand Feb 23 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.