List failed logins from the error log over seven days
Count SQL Server failed logon attempts from the last seven days using the error log archives.
This script reads the SQL Server error log archives and groups the failed logon messages of the last seven days by message text. For each message it shows the number of attempts and the first and last time it was logged.
Script
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() - 7
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() - 7
GROUP BY TEXT
ORDER BY NumberOfAttempts DESC
SET NOCOUNT OFF
How it works
sp_enumerrorlogslists the error log archives.sp_readerrorlogreads each archive into a table variable.- The final
SELECTkeeps the rows whoseProcessInfoisLogon, whose text containsfailand whose date is within the last seven days. - The results are sorted by number of attempts, the most frequent message first.
Notes
- The result depends on the error log retention: archives that no longer exist cannot be read.
- Each distinct message text is one row, so the same account name in different messages appears as separate rows.