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 BLACKLIST_DECAY_TMP="$BLACKLIST_DECAY"
if [ $# -eq 1 ]; then
BLACKLIST_DECAY_TMP="$1"
printf "\n%s" "Using supplied decay variable: $BLACKLIST_DECAY_TMP Hours"
printf "%s\n\n" " OR approx $(($BLACKLIST_DECAY_TMP/24)) Days"
else
printf "\n%s" "Current decay variable is: $BLACKLIST_DECAY_TMP Hours"
printf "%s\n\n" " OR approx $(($BLACKLIST_DECAY_TMP/24)) Days"
fi
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
SELECT inet_ntoa(ip_src),\
date_format(timestamp, '%T %d-%m-%Y'),sig_name \
FROM acid_event WHERE date_format(timestamp, '%Y-%m-%d %T') <= \
DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY_TMP HOUR)"
exit 0
-------------------------------------------------------------------------------
#!/bin/bash
###> We're not insane
umask 0027
shopt -s -o nounset
declare -rx SCRIPT=${0##*/}
source /usr/local/etc/ip_bot/ip_bot.conf
declare -r date=`date +%d.%m.%y`
declare -r time=`date +%r`
declare ip_purge_table
declare table_entry
declare table_sid
declare table_cid
declare table_ip
declare table_timestamp
declare ip_address
declare ip_ref
### check to make sure that the path to iptables is correct
if [ ! -f "$MYSQL" ]; then
printf "%s\n" "You fsckwit, no MySQL here: $MYSQL"
exit 127
fi
### Clean up tmp file for new entries ###
: > "$IP_BOT_DIR"/tmp/ip_black_list_decay.tmp
### Extract the cid and sid of items that match the timestamp criteria
ip_purge_table=`"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
SELECT acid_event.sid,acid_event.cid,inet_ntoa(ip_src),\
date_format(timestamp, '%T %d-%m-%Y') \
FROM acid_event WHERE date_format(timestamp, '%Y-%m-%d %T') <= \
DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)"`
printf "%s\n" "$ip_purge_table" | sed -e 1d \
> "$IP_BOT_DIR"/tmp/ip_black_list_decay.tmp
while read table_entry; do
table_sid=`echo $table_entry | awk '{print $1}'`
table_cid=`echo $table_entry | awk '{print $2}'`
table_ip=`echo $table_entry | awk '{print $3}'`
table_timestamp=`echo $table_entry | awk '{print $4" "$5}'`
### Get our IP address and make sure it's a /24 address for iptables
ip_address=`echo "$table_ip" | sed -e 's/\.[0-9]*$/\.0\/24/g'`
### Match the ip address against our reference table and get the alert
type
ip_ref=`echo "$table_ip" | sed -e 's/\.[0-9]*$/\./g'`
alert_types=`grep "$ip_ref" "$IP_BOT_DIR"/ip_black_list_table | \
sed -e 's/^/ >>> /g'`
# printf "%s\n" "$table_sid $table_cid $table_ip $table_timestamp"
### MYSQL SECTION ###
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from event where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from iphdr where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from tcphdr where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from udphdr where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from icmphdr where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from opt where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from data where sid=$table_sid and cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from acid_ag_alert where ag_sid=$table_sid and ag_cid=$table_cid"
"$MYSQL" --user="$DB_USER" --password="$DB_PASS" \
--exec="use $DB_NAME; \
delete from acid_event where sid=$table_sid and cid=$table_cid"
### IPTABLES SECTION & LOGGING ###
## Input chain first ###
$IPTABLES -D INPUT_"$CHAIN" -i eth1 -s "$ip_address" -j DROP &>/dev/null
if [ $? -eq 0 ]; then
printf "%s\n" \
"$date $time $SCRIPT:\
Removed $ip_address from INPUT_$CHAIN chain" >> "$LOGFILE_DECAY"
fi
### Now do the custom "forward" chain, probably called "BLACKLIST"
$IPTABLES -D "$CHAIN" -i eth1 -s "$ip_address" -j DROP &>/dev/null
if [ $? -eq 0 ]; then
{ printf "%s\n" \
"$date $time $SCRIPT:\
Removed $ip_address from $CHAIN chain" >> "$LOGFILE_DECAY"
printf "%s\n" " +++ Event(s) had timestamp of: $table_timestamp" \
>> "$LOGFILE_DECAY"
printf "%s\n\n" "$alert_types" >> "$LOGFILE_DECAY";
}
fi
done < "$IP_BOT_DIR"/tmp/ip_black_list_decay.tmp
exit 0
