Parse Sql Extended event for Login Audits

SELECT
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],
xevents.event_data.value(‘(event/action[@name=”nt_username”]/value)[1]’, ‘nvarchar(128)’) AS [nt_username],
xevents.event_data.value(‘(event/action[@name=”username”]/value)[1]’, ‘nvarchar(max)’) AS [username]
FROM sys.fn_xe_file_target_read_file
(‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\LoginAccounts_*.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

SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file( ‘C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014\MSSQL\Log\LoginAccounts_*.xel’ , DEFAULT , DEFAULT);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s