/******************************
** FileName: QueryDefaultTrace.sql 
** Description: This scripts returns all Failed login attempts registered in the current default trace file
** Author: Arthur Baan
** Date: 06-09-2013
**************************
** Change History
**************************
** Date 		Author		Decription	
** --------		-----------	------------------------------------
** 06-09-2013   	Arthur Baan	Initial version
*******************************/
use master
go
--Get the name (including location) of the current trace file
declare @CurrentTraceFile sql_variant
set @CurrentTraceFile=(select top 1 value from sys.fn_trace_getinfo(NULL) WHERE property= 2)
select @CurrentTraceFile [CurrentTraceFile]

-- Retrieve the information about the failed login attempts
select
  t.[HostName]
, t.[NTUserName]
, t.[NTDomainName]
, t.[LoginName]
, t.[StartTime]
, t.[ApplicationName]
, t.[DatabaseName]
, t.[Error]
, te.[name]
, t.[TextData]
 from
 fn_trace_gettable(cast(@CurrentTraceFile as varchar(250)),default) as t
 inner join sys.trace_events as te on t.EventClass=te.trace_event_id
 where
 t.EventClass = 20 --Audit Login Failed
order by StartTime