declare @numberDaysBack int = 1
declare @errorLogCount int
declare @lastLogDate datetime
declare @errorLogInfo table(
	LogDate datetime,
	ProcessInfo nvarchar(50),
	[Text] nvarchar(max)
)
declare @enumErrorLogs table(
	Archive# int,
	[Date] datetime,
	LogFileSizeMB int
)
insert into @enumErrorLogs
exec sp_enumerrorlogs
select @errorLogCount = MIN(Archive#), @lastLogDate = MAX([Date])
from @enumErrorLogs
while @errorLogCount is not null
	begin
		insert into @errorLogInfo
		exec sp_readerrorlog
			@errorLogCount
		select @errorLogCount = MIN(Archive#), @lastLogDate = MAX([Date])
		from @enumErrorLogs
		where Archive# > @errorLogCount
					and @lastLogDate > getdate() - @numberDaysBack
	end

-- List all last week failed logins count of attempts and the Login failure message
select COUNT(TEXT) as NumberOfAttempts, TEXT as Details, MIN(LogDate) as MinLogDate, MAX(LogDate) as MaxLogDate
from @errorLogInfo
where ProcessInfo = 'Logon'
			and TEXT like '%fail%'
			and LogDate > getdate() - @numberDaysBack
group by TEXT
order by NumberOfAttempts desc