Hi All,
I seem to be having a problem with the resolution using the timestamp
function. I am accessing the database snort on an ACID/SNORT/MySQL
installation utilizing a collection of shell scripts that are run as
cron jobs and function as a bot adding and removing firewall rules
(iptables). The problem is with the script that removes entries, thus
creating a decaying blacklist.
* The problem seems to be that, even though this script is run many
times a day, regardless of comparing timestamps and a range of
times/dates using this function in a shell script:
[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality is taken directly from one of the larger scripts in my
package. This is apparent, as the only time any entries are removed, is
once a day, as the number of hours is rounded off to a day, and I can
see this relected in the logs. Also, FYI, the $BLACKLIST_DECAY
variable from the sourced config file is in hours.
I have also included the full ip_decy (script that removes MySQL
entries) below.
Also, as an aside. Is there a way to run multiple queries within a shell
script without having to make a connection to MySQL evey time?
Any help is welcomed. TIA
---
Here is the output of the debugging script (included below):
[EMAIL PROTECTED] root]# ip_decay_report_mysql
Current decay variable is: 469 Hours OR approx 19 Days
*nothing*
Here is the output of the debugging script providing the number of hours.
*** I get this same result ALL DAY!
[EMAIL PROTECTED] root]# ip_decay_report_mysql 466
Using supplied decay variable: 466 Hours OR approx 19 Days
+---+---+---+
| inet_ntoa(ip_src) | date_format(timestamp, '%T %d-%m-%Y') |
sig_name |
+---+---+---+
| 203.15.17.32 | 13:16:27 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:16:03 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:51 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:45 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:43 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.72.197.172| 13:09:00 09-07-2005 |
(http_inspect) OVERSIZE REQUEST-URI DIRECTORY |
| 62.105.37.129 | 12:19:42 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
+---+---+---+
FYI: SNORT TABLE EXAMPLE
Here is a brief abstract of 3 rows from the main acid_event table using
a modified format timestamp:
mysql SELECT
acid_event.sid,acid_event.cid,inet_ntoa(ip_src),date_format(timestamp,
'%Y-%m-%d %T') FROM acid_event LIMIT 3;
+-+--+---+---+
| sid | cid | inet_ntoa(ip_src) | date_format(timestamp, '%Y-%m-%d %T') |
+-+--+---+---+
| 2 | 5692 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5691 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5690 | 194.24.131.163| 2005-07-25 05:22:21 |
+-+--+---+---+
3 rows in set (0.01 sec)
Here is a brief abstract using the regular timestamp:
mysql SELECT acid_event.sid,acid_event.cid,inet_ntoa(ip_src),timestamp
FROM acid_event WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL 466 HOUR);
+-+--+---+-+
| sid | cid | inet_ntoa(ip_src) | timestamp |
+-+--+---+-+
| 2 | 3278 | 203.15.17.32 | 2005-07-09 13:16:27 |
| 2 | 3277 | 203.15.17.32 | 2005-07-09 13:16:03 |
| 2 | 3276 | 203.15.17.32 | 2005-07-09 13:15:51 |
| 2 | 3275 | 203.15.17.32 | 2005-07-09 13:15:45 |
| 2 | 3274 | 203.15.17.32 | 2005-07-09 13:15:43 |
| 2 | 3273 | 203.72.197.172| 2005-07-09 13:09:00 |
| 2 | 3272 | 62.105.37.129 | 2005-07-09 12:19:42 |
+-+--+---+-+
7 rows in set (0.14 sec)
#!/bin/sh
shopt -s -o nounset
umask 0027
declare -rx SCRIPT=${0##*/}
source /usr/local/etc/ip_bot/ip_bot.conf
declare
