Find brute force attempts to guess a password

BEGIN

CREATE TABLE #ErrorLog (
LogDate datetime,
ProcessInfo varchar(250),
[Text] varchar(8000)
)


--read the current error log
INSERT INTO #errorLog (LogDate, ProcessInfo, [Text])
EXEC sp_readerrorlog 0, 1 --0 = current(0), 1 = error log


--find brute force attempts to guess a password
SELECT
REPLACE(right([Text],CHARINDEX(' ', reverse([Text]))-1), ']', '') AS IP, SUBSTRING([Text], CHARINDEX('''', [Text]) + 1,
CHARINDEX('.', [Text]) - CHARINDEX('''', [Text]) - 2  ) AS [User],
COUNT(LogDate) AS [Number of login attempts],
MIN(LogDate) AS [Attack started],
MAX(LogDate) AS [Attack ended],
DATEDIFF(MINUTE, MIN(LogDate),
MAX(LogDate)) AS [Attack duration in minutes],
CAST(CAST(COUNT(LogDate) AS DECIMAL(18,2))/ISNULL(NULLIF(DATEDIFF(MINUTE,MIN (LogDate),
MAX(LogDate)),0),1) AS DECIMAL(18,2)) AS [Attack intensity - Login attempts per minute]

FROM #errorLog

WHERE
--limit data to unsuccessful login attempts in the last 24 hours
ProcessInfo = 'Logon'
and [Text] like 'Login failed for user%'
and DATEDIFF(hour, LogDate, GETDATE()) <= 24

group by [Text]
having COUNT(LogDate) > 3 --filter out users just typing their passwords incorrectly

ORDER BY [Number of login attempts] DESC,[Attack ended] DESC

--clean up temp tables created
--DROP TABLE #ErrorLog


END