0
(0)

Below script is used to find logins which are failing repeatedly since 1 day. you can change the number of days as per your requirement. this script is taken from Here

SET NOCOUNT ON

	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
	)

	CREATE TABLE #FailedLogins 
	(
	Server_Name nVARCHAR(100),
	NumberofAttempts int,
	Details nVARCHAR(500),
	MinLogDate DateTime,
	MaxLogDate DateTime
	)

	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() - 1
  
	END

	INSERT INTO #FailedLogins
	SELECT @@servername as Server_Name, 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() - 1
	GROUP BY Text, CAST(LogDate as Date)
	ORDER BY NumberOfAttempts DESC;

	WITH CTE AS
	(
		SELECT Server_Name, NumberofAttempts, Details, MinLogDate, MaxLogDate, RANK() OVER (Partition by Details Order BY  MinLogdate) RepeatOff
		FROM #failedlogins
	)
	SELECT *
	FROM CTE
	WHERE RepeatOff = 1

	DROP TABLE #failedlogins
SET NOCOUNT OFF

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.