November 25, 2019 at 9:09 am
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
November 25, 2019 at 10:07 pm
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.
November 26, 2019 at 7:05 am
Thanks.That worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply