May 31, 2021 at 4:50 pm
Result set sample:
event_nameTIMESTAMPIsCachedIsRecoveredIsDacDurationCpuTimePhysicalReadsLogicalReadsWritesClientConnectionIDContextInfoSessionIDClientAppNameSessionNTUserNameUserNameDatabaseNameDatabaseIDEventSequenceCollectSystemTimeClientHostNameServerPrincipalNameNTUserNameActivityIDXferActivityIDdatabase_name
logout2021-05-31T16:29:46.776Zfalsefalsefalse000760NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
login2021-05-31T16:29:46.775ZfalsefalsefalseNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLDBA_Services7NULLNULLNULLNULLNULLNULLNULLDBA_Services
I am unable to capture nt_username and other column data etc.. in the select result as seen above but i do see the username etc when I view the extended events live data.
Got the below code from the web.
USE master;
GO
-- Create the Event Session
IF EXISTS
(
SELECT
*
FROM sys.server_event_sessions
WHERE name = 'LoginOutAudit'
)
DROP EVENT SESSION LoginOutAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'D:E';
GO
CREATE EVENT SESSION LoginOutAudit
ON SERVER
ADD EVENT sqlserver.login
(SET
collect_database_name = (1)
, collect_options_text = (1)
ACTION
(
sqlserver.sql_text
, sqlserver.nt_username
, sqlserver.server_principal_name
, sqlserver.client_hostname
, package0.collect_system_time
, package0.event_sequence
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.username
, sqlserver.session_nt_username
, sqlserver.client_app_name
, sqlserver.session_id
, sqlserver.context_info
, sqlserver.client_connection_id
)
),
ADD EVENT sqlserver.logout
(
ACTION
(
sqlserver.sql_text
, sqlserver.nt_username
, sqlserver.server_principal_name
, sqlserver.client_hostname
, package0.collect_system_time
, package0.event_sequence
, sqlserver.database_id
, sqlserver.database_name
, sqlserver.username
, sqlserver.session_nt_username
, sqlserver.client_app_name
, sqlserver.session_id
, sqlserver.context_info
, sqlserver.client_connection_id
)
)
ADD TARGET package0.event_file
(SET filename = N'D:\DatabaseA*.xel', max_file_size = (5120), max_rollover_files = (10))
WITH
(
STARTUP_STATE = OFF
, TRACK_CAUSALITY = ON
);
/* start the session */
ALTER EVENT SESSION LoginOutAudit ON SERVER STATE = START;
GO
SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(event/data[@name="is_cached"]/value)[1]','varchar(max)') AS IsCached
,event_data.value('(event/data[@name="is_recovered"]/value)[1]','varchar(max)') AS IsRecovered
,event_data.value('(event/data[@name="is_dac"]/value)[1]','varchar(max)') AS IsDac
,event_data.value('(event/data[@name="duration"]/value)[1]','varchar(max)') AS Duration
,event_data.value('(event/data[@name="cpu_time"]/value)[1]','int') AS CpuTime
,event_data.value('(event/data[@name="physical_reads"]/value)[1]','varchar(max)') AS PhysicalReads
,event_data.value('(event/data[@name="logical_reads"]/value)[1]','varchar(max)') AS LogicalReads
,event_data.value('(event/data[@name="writes"]/value)[1]','varchar(max)') AS Writes
,event_data.value('(event/data[@name="client_connection_id"]/value)[1]','varchar(max)') AS ClientConnectionID
,event_data.value('(event/data[@name="context_info"]/value)[1]','varchar(max)') AS ContextInfo
,event_data.value('(event/data[@name="session_id"]/value)[1]','int') AS SessionID
,event_data.value('(event/data[@name="client_app_name"]/value)[1]','int') AS ClientAppName
,event_data.value('(event/data[@name="session_nt_username"]/value)[1]','varchar(max)') AS SessionNTUserName
,event_data.value('(event/data[@name="username"]/value)[1]','int') AS UserName
,event_data.value('(event/data[@name="database_name"]/value)[1]','varchar(max)') AS DatabaseName
,event_data.value('(event/data[@name="database_id"]/value)[1]','varchar(max)') AS DatabaseID
,event_data.value('(event/data[@name="event_sequence"]/value)[1]','varchar(max)') AS EventSequence
,event_data.value('(event/data[@name="collect_system_time"]/value)[1]','vrchar(max)') AS CollectSystemTime
,event_data.value('(event/data[@name="client_hostname"]/value)[1]','varchar(max)') AS ClientHostName
,event_data.value('(event/data[@name="server_principal_name"]/value)[1]','int') AS ServerPrincipalName
,event_data.value('(event/data[@name="nt_username"]/value)[1]','varchar(max)') AS NTUserName
,event_data.value('(event/data[@name="attach_activity_id_xfer"]/value)[1]','varchar(max)') AS ActivityIDXfer
,event_data.value('(event/data[@name="attach_activity_id"]/value)[1]','varchar(max)') AS ActivityID
,event_data.value('(event/data[@name="database_name"]/value)[1]','varchar(max)') AS database_name
FROM ( SELECT CONVERT(XML, t2.event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\DatabaseA*.xel',NULL,NULL,NULL) t2
) AS evts ( event_data )
ORDER BY [TIMESTAMP] desc;
Thanks
June 1, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 1, 2021 at 5:41 pm
As a thought - are you able to see ANY data or does it return no rows or does it give you an error?
I am not 100% certain, but I am pretty sure that a "*" in a file name is not allowed and it may be that your extended event session didn't even start due to that and as such, you aren't able to do the SELECT either as it will give an error. Note - that is just a guess. It may be that XE is seeing the * and implicitly replacing it with an appropriate value.
What would be helpful is:
1 - do you get any errors running your query?
2 - do any errors pop up in the logs?
3 - if you see any errors, do they contain useful information to help diagnose and debug the problem?
There are a few lines in your script that make me think errors are likely coming up such as:
EXECUTE xp_create_subdir 'D:E';
which to me doesn't look like a valid subdirectory to create as :'s are not allowed in directory names (or file names) in Windows. Note - I am assuming it is windows due to you trying to read and write to the D:\ drive which would not exist in Windows.
Now, assuming the above is all incorrect, my next step would be to take your last SELECT and what data you are actually getting. What I mean is JUST run this part of the select:
SELECT CONVERT(XML, t2.event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('D:\DatabaseA*.xel',NULL,NULL,NULL) t2
What I am suggesting here is to look at the raw XML and verify that the results you are seeing are in-fact correct. When working with XML data, my approach is to ALWAYS check that the raw XML is correct and has values before I start parsing it. Once you are certain that the XML is good, then start building out the xml parsing like you did. Also, while trying to debug the query, I would remove the ORDER BY as it will give you a performance boost. I would only put the order by back into the query once I had some useable data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 7, 2021 at 2:28 pm
* is one of many characters to avoid when naming a file or directory.
That is the first thing I would check.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply