List:General Discussion« Previous MessageNext Message »
From:Nick Sinclair Date:July 29 2005 7:36am
Subject:timestamp resolution problem (rounding off to a day)
View as plain text  
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):

[root@adriana 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!

[root@adriana 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

Thread
timestamp resolution problem (rounding off to a day)Nick Sinclair29 Jul