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

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