SQL Server – Exetended Events example : failed logins

Posted by

0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- create the extended event session and start it
CREATE EVENT SESSION FailedLogins
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username
    )
    WHERE severity = 14
      AND state > 1
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins.xel',
    METADATAFILE = N'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins.xem'
  );
GO
 
ALTER EVENT SESSION FailedLogins ON SERVER
  STATE = START;
GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Query the files
WITH event_data AS
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins*.xel',
   'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL\MSSQL\Log\xEventSessions\FailedLogins*.xem',
   NULL, NULL
 )
),
tabular AS
(
  SELECT
    [host] = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app] = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [error] = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state] = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message] = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT [host],[app],[state],[message],[date/time]
  FROM tabular
  WHERE error = 18456
  ORDER BY [date/time] DESC;

Leave a Reply

Your email address will not be published. Required fields are marked *