Windows Server bruteforce GUARD

[llewxam]

I found that your wevtutil command was malformed a little, this cleared it up:

$wevtutil_query = "*[System[band(Keywords,4503599627370496) and TimeCreated[timediff(@SystemTime) <= 86400000]]]" ;after initail run dont read all log only 24 h
$cmd = 'wevtutil qe Security /q:' & Chr(34) & $wevtutil_query & Chr(34) & ' /c:' & $initial_event_count & ' /rd:true /f:xml>C:\wevtutil.xml'

I see where you got your example code from, but it was causing the script to not update at all after the initial run, I left the script running all night and even restarted it this morning but the number of attacks had not changed. I used the EventViewer and created a custom filter which gave me the correct path to use, the 4503599627370496 is for any Audit Failure, and the 86400000 equates to 24 hours in milliseconds. I'd like to see the milliseconds be user-adjustable but maybe it isn't necessary... Also, you were placing the $wevtutil_query after the $cmd itself, if there is a reason that I didn't catch then keep it the way you had it, but the updated $wevtutil_query will give you what you were after.

Ian

Edited March 11, 2014 by llewxam

[llewxam]

Oh, and awesome work on the StringRegExp work jchd, huge speed boost!

Ian

[slaughter]

I found that your wevtutil command was malformed a little, this cleared it up:

$wevtutil_query = "*[System[band(Keywords,4503599627370496) and TimeCreated[timediff(@SystemTime) <= 86400000]]]" ;after initail run dont read all log only 24 h
$cmd = 'wevtutil qe Security /q:' & Chr(34) & $wevtutil_query & Chr(34) & ' /c:' & $initial_event_count & ' /rd:true /f:xml>C:\wevtutil.xml'

I see where you got your example code from, but it was causing the script to not update at all after the initial run, I left the script running all night and even restarted it this morning but the number of attacks had not changed. I used the EventViewer and created a custom filter which gave me the correct path to use, the 4503599627370496 is for any Audit Failure, and the 86400000 equates to 24 hours in milliseconds. I'd like to see the milliseconds be user-adjustable but maybe it isn't necessary... Also, you were placing the $wevtutil_query after the $cmd itself, if there is a reason that I didn't catch then keep it the way you had it, but the updated $wevtutil_query will give you what you were after.

Ian

 

Good notice abuot $wevtutil_query. It was used before to create initial scan and then read one hour events. So i have removed it. Thanks.

And one more time thanks for cleared updated line.

[slaughter]

Thanks for supplying a larger sample input.

Nonetheless I don't find any discrepancy between the results of running your old code and mine. The only difference I can see is that I insert rows in the same order found in the input while you're inserting in the reverse order, hence rowids are not equal.

To test I created a separate ip_listold table with the same DDL and loaded it with the (old) code in the If 0 Then branch.

Then I used SQLite Expert (an absolute must have if you work with SQLite) to run each of the following SQL in turn:

select log_id, date, ip, user from ip_list except select log_id, date, ip, user from ip_listold;
SELECT ip, count(*) as 'Cnt' FROM ip_list GROUP BY ip except SELECT ip, count(*) as 'Cnt' FROM ip_listold GROUP BY ip;
SELECT user, count(*) as 'Cnt' FROM ip_list GROUP BY user except SELECT user, count(*) as 'Cnt' FROM ip_listold GROUP BY user;

All 3 above queries returned no rows. Thus I'm fairly confident that the code I provided doesn't change or ignore data from the input.

I suspect what you experience is due to later modification in the code you use. I'm still based on the original posted code. If you have issues finding where things go astray with your new code let me know and I'll take a look.

Thanks for testing. may be my fault

[slaughter]

Lots of variable errors on the above script: $minutes,$atempts,$WhatIP,$date_to

Also, error: _Date_Time_SystemTimeToDateTimeStr() called with Const or expression on ByRef-param(s).

Ian

Edit:

Fixed _Date_Time_SystemTimeToDateTimeStr() error by doing this:

$Temp = _Date_Time_GetLocalTime()
$time = _Date_Time_SystemTimeToDateTimeStr($Temp)

Hmm, doesn't like being nested? Also, set globals for above variables, not yet tested but passed syntax check/compile.

Edit2:

Blocked IP tab uses a different time format and the wrong time than my local time, probably due to the same _Date_Time_SystemTimeToDateTimeStr issue, so my fix looks like it is not a fix....

Edit3:

Sorry for so many edits!!

I love the White List idea, that was a concern I had a while ago and I'm glad you thought of it too. Also very thankful for the option to choose different types and time intervals for failed login statistics, looking forward to that functioning!!! Last request for now, how about setting the auto block interval to minutes?

 

fixed by static line now should be ok. Check updated script

[slaughter]

jchd culd you help to sort out date isues, now wehn i truy to select event that ocured in last ~2 min

SELECT ip, count(*) as 'count', status FROM ip_list WHERE date >= date('now','-1 minutes') GROUP BY ip ORDER by count DESC

i get worng result where is the error in query or in database stucture

And to help sort out this query for autoban

auto_ban("SELECT ip, COUNT(*) as 'count' FROM ip_list WHERE date >= date('now','-"&$attempts_time&" minutes') AND count >= "&$attempts_count&" AND status!='Blocked' GROUP BY ip ORDER by count DESC")

I get error that i cant use count in where statment, I tried to do select in select as in mysql but i guess sqlite does not support it.

[jchd]

count(*) is an aggregate function which doesn't make sense in a where clause. Where clauses apply on individual rows.

You can nonetheless use count() in a group by clause by means of having:

auto_ban("SELECT ip, COUNT(*) as 'count' FROM ip_list WHERE date >= date('now','-"&$attempts_time&" minutes') AND status!='Blocked' GROUP BY ip HAVING count >= "&$attempts_count&" ORDER by count DESC")

Some SQL engines don't let you specify HAVING on conditions not specified in GROUP BY, but SQLite is smart enough to accept it.

About date and date(): let first note that a column named date can be pretty ambiguous but the issue you have isn't there.

Look at that clause:

WHERE date >= date('now','-1 minutes')

the date column is a simple string (SQLite doesn't have a datatime type). What is

select date('now','-1 minutes')

the result is "2014-03-12", not what you expect. Even if you use datetime():

select datetime('now','-1 minutes')

the result is "2014-03-12 13:19:19". Still hard to compare to strings in your date column.

The correct query should be something like:

select * from ip_list where datetime("date") between datetime('2014-03-10T21:56:40.00000Z') and datetime('2014-03-10T21:56:40', '+1 hours');

You get the idea and you'll know how to adjust to your context. You can also use epoch times or julian days to express timestamps in a uniform format easier to compare. To do that easily without extra effort, you can use a trigger on insert on your table to change the timestamp stored format.

Hope this clears up things.

[slaughter]

count(*) is an aggregate function which doesn't make sense in a where clause. Where clauses apply on individual rows.

You can nonetheless use count() in a group by clause by means of having:

auto_ban("SELECT ip, COUNT(*) as 'count' FROM ip_list WHERE date >= date('now','-"&$attempts_time&" minutes') AND status!='Blocked' GROUP BY ip HAVING count >= "&$attempts_count&" ORDER by count DESC")

Some SQL engines don't let you specify HAVING on conditions not specified in GROUP BY, but SQLite is smart enough to accept it.

About date and date(): let first note that a column named date can be pretty ambiguous but the issue you have isn't there.

Look at that clause:

WHERE date >= date('now','-1 minutes')

the date column is a simple string (SQLite doesn't have a datatime type). What is

select date('now','-1 minutes')

the result is "2014-03-12", not what you expect. Even if you use datetime():

select datetime('now','-1 minutes')

the result is "2014-03-12 13:19:19". Still hard to compare to strings in your date column.

The correct query should be something like:

select * from ip_list where datetime("date") between datetime('2014-03-10T21:56:40.00000Z') and datetime('2014-03-10T21:56:40', '+1 hours');

You get the idea and you'll know how to adjust to your context. You can also use epoch times or julian days to express timestamps in a uniform format easier to compare. To do that easily without extra effort, you can use a trigger on insert on your table to change the timestamp stored format.

Hope this clears up things.

Yes I found my mistake to few minutes ago to. Feeling so stupid. Thanks for replay!

To make more simple compare in SQL I reformatted event log date line in your given import funcion

For $i = 0 To (UBound($captures) / 4) - 1
            $reformated_time = StringSplit($captures[4 * $i], "T")
            $reformated_time = $reformated_time[1] & " " & StringLeft($reformated_time[2],8)
            $sql &= "('" & $reformated_time & "', '" & $captures[4 * $i + 1] & "', '" & $captures[4 * $i + 2] & "', '" & $captures[4 * $i + 3] & "'),"

Edited March 12, 2014 by slaughter

[jchd]

Yes you can do that as well. You have plenty of ways to perform that correctly..

[slaughter]

Application almost finished

[slaughter]

full code published

[Fire]

Fantastic Job! Thank you very much and keep up!