This SP expects a parameter @TimeInMin which has a default value of 15 Minutes.
Create Proc ReportLoginFailure (@TimeinMin int =15)
As
/*
Author :Sandesh Segu
Purpose :To report the failed loing attempts if any in given
range of time.
Version :1.0
More Scripts :http://sanssql.blogspot.com
*/
Declare @Count int
Create Table #ErrorLog
(ErrorLoggedDate DateTime,
ProcessInfo nvarchar(50),
ErrorText nvarchar(1000))
Insert into #ErrorLog exec sp_readerrorlog
Select @Count=Count(*) from #ErrorLog where ErrorText like 'Login failed for user%'
and datediff(Minute,ErrorLoggedDate,getdate())<=@TimeinMin
If @Count>0
Begin
Select Report=Cast(@Count As Varchar)+' Login Failures Reported in past '+Cast (@TimeinMin As varchar)+' Minutes.'
Select * from #ErrorLog where ErrorText like 'Login failed for user%'
and datediff(Minute,ErrorLoggedDate,getdate())<=@TimeinMin
End
Else
Select Report='No Login Failures Reported in the Specified Time Frame.'
Drop table #ErrorLog
GO
/*
Usage: Specify the time limit parameter in minutes for the SP. Default is 15 mins.
Exec ReportLoginFailure 600
*/
When there are no failures reported then the output will be:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJbJF36SEs1eJB5I1RxPfxi0VVSnRxxnkpCCmjz1TOJYXRjdLd2oXCXyVZ1Jy9V-BKmdSkyePprJQZH99X5wSS71pmQ19gCIp0RgKE_Buje779MOzke3rpRi6Bn1tNJm9n4w4vx-jJ5l4/s320/Not+Reported.png)
When there are failures reported then the output will be:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-bdk1NjpHvVkQ3pHQx_0U59cDC9o51aMApizrZ6IEpX0cNiEAbONhc6FF62YSZ3AsYxS8VvMAqhi38QrP3lWGuDIh7chPscZRobLzgaB6xUaM6eB4D0k77iZ4h1bv-Eo4yDj7lnvIzso/s400/Reported.png)