tmestamp resolution problem (rounding off to a day)
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
timestamp resolution problem (rounding off to a day)
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
Re: tmestamp resolution problem (rounding off to a day)
Nick Sinclair wrote: [.] 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 don't know the type of the field timestamp, but I suspect that the following will work better and faster: WHERE `timestamp` = NOW() - INTERVAL $BLACKLIST_DECAY HOUR; CURDATE() gives you a 'timestamp' of the beginning of today (only date part with time part as 00:00:00) NOW() gives you a 'timestamp' of this moment (including time part) The MySQL optimizer will see that the expression after the = is a constant and will produce a fast query (instead of calculating the DATE_FORMAT() for each row in de table) which can use an index. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tmestamp resolution problem (rounding off to a day)
Jigal van Hemert wrote: Nick Sinclair wrote: [.] 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 don't know the type of the field timestamp, but I suspect that the following will work better and faster: WHERE `timestamp` = NOW() - INTERVAL $BLACKLIST_DECAY HOUR; CURDATE() gives you a 'timestamp' of the beginning of today (only date part with time part as 00:00:00) NOW() gives you a 'timestamp' of this moment (including time part) The MySQL optimizer will see that the expression after the = is a constant and will produce a fast query (instead of calculating the DATE_FORMAT() for each row in de table) which can use an index. Regards, Jigal. Thanks Jigal, all works great - You are *so* right.
rounding off
Hello, I need the ability to round off dollar amounts to the nearest 100th of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11 can this be accomplished with MySQL SQL function ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rounding off
At 16:34 -0600 11/22/03, Mike Blezien wrote: Hello, I need the ability to round off dollar amounts to the nearest 100th of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11 can this be accomplished with MySQL SQL function ?? Sounds like a job for TRUNCATE(). http://www.mysql.com/doc/en/Mathematical_functions.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rounding off
Thanks Paul. but I think the function I was looking for was ROUND(X,D), the TRUNCATE(X,D) just 'trims' it :) IE using the value 123.336 and I needed it to round off to 123.34 in this case. mysql select truncate(123.336,2); +-+ | truncate(123.336,2) | +-+ | 123.33 | +-+ 1 row in set (0.00 sec) mysql select round(123.336,2); +--+ | round(123.336,2) | +--+ | 123.34 | +--+ 1 row in set (0.00 sec) But your suggestion allowed to find the what I was looking for,... appreciate it :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Paul DuBois wrote: At 16:34 -0600 11/22/03, Mike Blezien wrote: Hello, I need the ability to round off dollar amounts to the nearest 100th of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11 can this be accomplished with MySQL SQL function ?? Sounds like a job for TRUNCATE(). http://www.mysql.com/doc/en/Mathematical_functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rounding off
Hi! Use the round() function. round(14.9564, 2) = 14.96 round(32.1123, 2) = 113.11 Bye Hello, I need the ability to round off dollar amounts to the nearest 100th of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11 can this be accomplished with MySQL SQL function ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
