Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active January 12, 2022 21:27
Show Gist options
  • Save EitanBlumin/49e64fdc5752ee1c48c9fc92a7d85b15 to your computer and use it in GitHub Desktop.
Save EitanBlumin/49e64fdc5752ee1c48c9fc92a7d85b15 to your computer and use it in GitHub Desktop.
Extended event session to track all failed logins with full details (more info: https://eitanblumin.com/2020/03/09/finding-details-missing-sql-server-failed-logins-audit/ )
CREATE EVENT SESSION [TrackFailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.database_id,sqlserver.session_id)
WHERE (([severity]=(20) OR [severity]=(14) OR [severity]=(16))
AND ([error_number]=(18056)
OR [error_number]=(17892)
OR [error_number]=(18061)
OR [error_number]=(18452)
OR [error_number]=(11248)
OR [error_number]=(17806)
OR [error_number]=(18456)
OR [error_number]=(18470)
OR [error_number]=(18487)
OR [error_number]=(18488)
OR [error_number]=(17817)
OR [error_number]=(17828)
OR [error_number]=(17830)
OR [error_number]=(17832)
OR [error_number]=(17897)
OR [error_number]=(18401)
OR [error_number]=(18451)
OR [error_number]=(18458)
OR [error_number]=(18459)
OR [error_number]=(18460)
OR [error_number]=(18461)
OR [error_number]=(18486)
OR [error_number]=(26078)
OR [error_number]=(33147)
OR [error_number]=(40623)
)))
ADD TARGET package0.event_file(SET filename=N'TrackFailedLogins.xel',max_file_size=(5),max_rollover_files=(4))
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=OFF,STARTUP_STATE=ON)
;
ALTER EVENT SESSION [TrackFailedLogins]
ON SERVER STATE = START;
;WITH event_data AS
(
SELECT data = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file
('TrackFailedLogins*.xel', default, NULL, NULL)
),
tabular AS
(
SELECT
 [timestamp] = data.value('(event/@timestamp)[1]','varchar(30)'),
 [client_hostname] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
 [client_app_name] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
 [nt_username] = data.value('(event/action[@name="nt_username"]/value)[1]','nvarchar(4000)'),
 [database_id] = data.value('(event/action[@name="database_id"]/value)[1]','int'),
 [database_name] = DB_NAME(data.value('(event/action[@name="database_id"]/value)[1]','int')),
 [session_id] = data.value('(event/action[@name="session_id"]/value)[1]','int'),
 [error_number] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
 [severity] = data.value('(event/data[@name="severity"]/value)[1]','int'),
 [state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
[message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)'),
 [data] = data.query('.')
FROM event_data
)
SELECT *
FROM tabular AS t
ORDER BY [timestamp] DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment