Catach failed logins with xp_readerrorlog

Question

Post reply

Catach failed logins with xp_readerrorlog

quackhandle1975

SSChampion

Points: 11055
More actions
September 12, 2012 at 6:51 am

#263967

Hi,
I have a requirement to catch a number of failed logins within SQL Server using xp_readerrorlog. Basically I have to check if the instance is being hacked, ie - the number of logins will increase by 100 over a 5 minute period. This is my code:
[font="Courier New"]
if object_id('tempdb..#tbl2') is not null
drop table #tbl2
-- Create a table varible
Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
LogDate datetime null,
ProcessInfo nvarchar(100) null,
LogText nvarchar(4000) null)
-- Insert error log data into table
Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
Exec master..xp_readerrorlog
-- Find all rows relating to logon
Select * into #tbl2 From @ErrorLog
Where ProcessInfo = 'Logon' AND LogText LIKE 'Login failed%'
--Where CharIndex('Backup', ProcessInfo) = 0
--GROUP BY LogID, LogDate, ProcessInfo, LogText
Order By LogID asc
select count(*) from #tbl2
select top 1 (LogDate) from @ErrorLog
-- If the rowcount increases by 100 over 5 minutes fire email alert
declare @cnt int
declare @LDate datetime
set @cnt = (select count(*) from #tbl2)
set @LDate = select top 1 (LogDate) from @ErrorLog
if @cnt > @cnt + 100 AND @LDate > (select dateadd(mi,5,getdate()))
begin
print 'we are being attacked!'
end
print 'we are okay'[/font]
I am getting some errors but that's probably down to my code (I'm a prod DBA and not a developer so ready to be corrected).
Any ideas if I am doing this in the wrong way? Not asking for a code rewrite just point me in the right direction. 🙂
Thanks
qh
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

just a basic syntax issue when you are assigning values to variables;

isntead of this:

--incorrect code

set @cnt = (select count(*) from #tbl2)

set @LDate = select top 1 (LogDate) from @ErrorLog

--valid code

select @cnt = count(*) from #tbl2

select top 1 @LDate =LogDate from @ErrorLog

here's your whole statement, fixed and tested ("we are okay" on my server 🙂 )

if object_id('tempdb..#tbl2') is not null

drop table #tbl2

-- Create a table varible

Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,

LogDate datetime null,

ProcessInfo nvarchar(100) null,

LogText nvarchar(4000) null)

-- Insert error log data into table

Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)

Exec master..xp_readerrorlog

-- Find all rows relating to logon

Select * into #tbl2 From @ErrorLog

Where ProcessInfo = 'Logon' AND LogText LIKE 'Login failed%'

--Where CharIndex('Backup', ProcessInfo) = 0

--GROUP BY LogID, LogDate, ProcessInfo, LogText

Order By LogID asc

select count(*) from #tbl2

select top 1 (LogDate) from @ErrorLog

-- If the rowcount increases by 100 over 5 minutes fire email alert

declare @cnt int

declare @LDate datetime

select @cnt = count(*) from #tbl2

select top 1 @LDate =LogDate from @ErrorLog

if @cnt > @cnt + 100 AND @LDate > (select dateadd(mi,5,getdate()))

begin

print 'we are being attacked!'

end

print 'we are okay'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

quackhandle1975 SSChampion Points: 11055 More actions · Answer 2

Thanks Lowell, much appreciated.

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]