Report Login Failures | SansSQL

Thursday, September 30, 2010

Report Login Failures

It sometimes become very critical in some environments that there are a huge login failures and needs to be reported to take appropriate action. To accommodate this type of situations, I have developed a stored proc which gives a report of Failed Login attempts in a specified Time Frame.
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:


When there are failures reported then the output will be:



No comments:

Post a Comment