info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Using Extended Events to capture failed logins
by BF (Principal Consultant; Architecture; Engineering)
2018-03-22








Create Extended Events Session:

CREATE EVENT SESSION EECaptureFailedLogins
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.client_hostname,
sqlserver.client_app_name,
sqlserver.nt_username
)
WHERE severity = 14 AND [state] > 1
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = N'C:\temp\EECaptureFailedLogins.xel',
METADATAFILE = N'C:\temp\EECaptureFailedLogins.xem'
--SQL Server 2008 and SQL Server 2008 R2 accept trace results generated in XEL and XEM format. SQL Server 2012
--Extended Events only support trace results in XEL format. We recommend that you use SQL Server Management Studio
--to read trace results in XEL format

);
GO


Start Extended Events Session:

ALTER EVENT SESSION EECaptureFailedLogins ON SERVER
STATE = START;
GO



Query Extended Events Data:

SELECT [Files] = CONVERT(XML, file_name)
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EECaptureFailedLogins*.xel',
'C:\temp\EECaptureFailedLogins*.xem',
NULL, NULL
);

SELECT [Event Data] = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EECaptureFailedLogins*.xel',
'C:\temp\EECaptureFailedLogins*.xem',
NULL, NULL
);

;WITH ee_event_data AS
(
SELECT data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file(
'C:\temp\EECaptureFailedLogins*.xel',
'C:\temp\EECaptureFailedLogins*.xem',
NULL, NULL
)
),
cte AS
(
SELECT
[HostName] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
[ClientName] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
[error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
[message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)'),
[state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
[Timestamp] = data.value('(event/@timestamp)[1]','datetime2')
FROM ee_event_data
)
SELECT [HostName],[ClientName],[Message],[State],[Timestamp]
FROM cte
WHERE error = 18456
ORDER BY [Timestamp] DESC;


Stop Extended Events Session:

ALTER EVENT SESSION EECaptureFailedLogins ON SERVER
STATE = STOP;
GO