Extended events

  • MY EXTENDED EVENT SCRIPT:

    CREATE EVENT SESSION [CLogins] ON SERVER

    ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)

    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)

    WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'SMSPHtndTAL0') AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[nt_user],N'RESDM50\L0TASQLSRF')))

    ADD TARGET package0.event_file(SET filename=N'W:\ArchiveData-01\ExtendedEvents\AuditLogins.xel',max_file_size=(5),max_rollover_files=(1000))

    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=ON)

    GO

    WITH XEvents AS

    (

    SELECT object_name, CAST(event_data AS XML) AS event_data

    FROM sys.fn_xe_file_target_read_file ( 'C:\Capture*.xel', NULL, NULL, NULL )

    )

    SELECT object_name AS EventName,

    event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [Time],

    event_data.value ('(/event/action[@name=''SESSION_NT_USERNAME'']/value)[1]', 'VARCHAR(128)') AS SessionName,

    event_data.value ('(/event/action[@name=''NT_USERNAME'']/value)[1]', 'VARCHAR(128)') AS NTName,

    event_data.value ('(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS sql_text,

    --event_data.value ('(/event/action[@name=''session_id'']/value)[1]', 'BIGINT') AS session_id,

    event_data.value ('(/event/data[@name=''object_name'']/value)[1]', 'VARCHAR(128)') AS object_name,

    event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS Duration,

    --event_data.value ('(/event/data[@name=''physical_reads'']/value)[1]', 'BIGINT') AS physical_reads,

    --event_data.value ('(/event/data[@name=''logical_reads'']/value)[1]', 'BIGINT') AS logical_reads,

    --event_data.value ('(/event/data[@name=''writes'']/value)[1]', 'BIGINT') AS writes,

    event_data.value ('(/event/data[@name=''statement'']/value)[1]', 'VARCHAR(MAX)') AS statement

    FROM XEvents

    --where event_data.value ('(/event/data[@name=''statement'']/value)[1]', 'VARCHAR(MAX)') like '%Alter%'

    The above statement is supposed to help me generate information in a more readeable format from the xml data.

    But all values under SessinoName and NTName column are carrying no values- show as NULL.

    How can I also capture the NTLogin and Session NT Username column values in the above statement.

    Reponses are appreciated.

    Thanks

    • This topic was modified 1 year ago by  mtz676.
  • Have you tried using lower case?

    event_data.value ('(/event/action[@name=''session_nt_username'']/value)[1]', 'VARCHAR(128)') AS SessionName,
    event_data.value ('(/event/action[@name=''nt_username'']/value)[1]', 'VARCHAR(128)') AS NTName,

    the attribute names may be case sensitive.

  • Thanks.That worked

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply