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
|
|
|
|
|
|