I've been trying to solve this problem for a couple of days now. I've only started with DB's a month ago so I am no expert in SQL. So, I present this problem to you guys in hopes of getting a solution.
Thanks in advance, Mike ========= Question: ========= Using the query below I get the result set as shown below. Everything is correct except the "Last Src" and "Last Dst" fields. I'm getting the same values in "Last Src" and "Last Dst" that I have in "First Src" and "First Dst". What I want shown is the "First Time" an event occurs along with the source and destination ips for that first time and the "Last Time" an event occurs along with the source and destination ips for that last time. The event and iphdr tables are related by the primary keys sid and cid which are present in both tables. The event and signature tables are related by event.signature and signature.sig_id. The timestamps for First and Last time are located in the event table whereas the ips that I want to correspond to the First and Last time are in the iphdr table. The query is grouped by signature.sig_name. I've tried using min and max (inet_ntoa(iphdr.ip_src)) etc. However, this gives me the largest ip address numerically (not a big surprise [:)] ). This is not what I want. ====== Query: ====== select count(*) as Count, min(event.timestamp) as "First Time", inet_ntoa(iphdr.ip_src) as "First Src", inet_ntoa(iphdr.ip_dst) as "First Dst", max(event.timestamp) as "Last Time", inet_ntoa(iphdr.ip_src) as "Last Src", inet_ntoa(iphdr.ip_dst) as "Last Dst" from event, signature, iphdr where event.signature = signature.sig_id and event.sid = iphdr.sid and event.cid = iphdr.cid and signature.sig_name not like "spp_portscan%" group by signature.sig_name order by count desc; ======= Tables: ======= event ----- Field Type Null Key Default Extra sid int(10) unsigned PRI 0 cid int(10) unsigned PRI 0 signature int(10) unsigned MUL 0 timestamp datetime MUL 0000-00-00 00:00:00 signature --------- Field Type Null Key Default Extra sig_id int(10) unsigned PRI NULL auto_increment sig_name varchar(255) MUL sig_class_id int(10) unsigned MUL 0 sig_priority int(10) unsigned YES NULL sig_rev int(10) unsigned YES NULL sig_sid int(10) unsigned YES NULL iphdr ----- Field Type Null Key Default Extra sid int(10) unsigned PRI 0 cid int(10) unsigned PRI 0 ip_src int(10) unsigned MUL 0 ip_dst int(10) unsigned MUL 0 ip_ver tinyint(3) unsigned YES NULL ip_hlen tinyint(3) unsigned YES NULL ip_tos tinyint(3) unsigned YES NULL ip_len smallint(5) unsigned YES NULL ip_id smallint(5) unsigned YES NULL ip_flags tinyint(3) unsigned YES NULL ip_off smallint(5) unsigned YES NULL ip_ttl tinyint(3) unsigned YES NULL ip_proto tinyint(3) unsigned 0 ip_csum smallint(5) unsigned YES NULL ======== Result: ======== Count First Time First Src First Dst 4621 2002-03-15 09:40:44 192.168.1.210 63.105.25.187 2798 2002-03-12 15:59:05 24.60.17.13 192.168.1.210 2275 2002-03-12 15:48:13 12.125.139.54 192.168.1.210 2103 2002-03-12 16:06:25 195.30.18.4 192.168.1.210 1507 2002-03-15 09:50:37 199.230.29.74 192.168.1.210 1118 2002-03-12 15:54:14 208.185.54.14 192.168.1.119 1109 2002-03-12 16:34:35 192.168.1.210 66.77.73.155 > Last Time Last Src Last Dst > 2002-03-20 09:20:23 192.168.1.210 63.105.25.187 > 2002-03-20 10:50:38 24.60.17.13 192.168.1.210 > 2002-03-20 11:06:09 12.125.139.54 192.168.1.210 > 2002-03-20 11:16:09 195.30.18.4 192.168.1.210 > 2002-03-20 10:31:08 199.230.29.74 192.168.1.210 > 2002-03-20 10:46:29 208.185.54.14 192.168.1.119 > 2002-03-20 11:02:12 192.168.1.210 66.77.73.155 ... (snip) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
