List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:November 4 2010 11:07am
Subject:Missing results.
View as plain text  
I can't wrap my head around this one..

I have this query:

SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
INET_NTOA(src_ip), src.cc, INET_NTOA(dst_ip), dst.cc FROM event LEFT
JOIN mappings AS src ON event.src_ip = src.ip LEFT JOIN mappings AS
dst ON event.dst_ip = dst.ip WHERE timestamp BETWEEN '2010-11-03
13:00:00' AND '2010-11-03 14:00:00' AND (signature LIKE '%ET POLICY
facebook apps activity%') AND (INET_NTOA(src_ip) LIKE '10.13.11.29' OR
INET_NTOA(dst_ip) LIKE '10.13.11.29') GROUP BY src_ip, src.cc, dst_ip,
dst.cc ORDER BY maxTime DESC;

which returns something like:

1	 2010-11-03 13:41:50
380	 2010-11-03 13:41:46 <-
24	 2010-11-03 13:22:39 <-
1	 2010-11-03 13:19:20 <-
1	 2010-11-03 13:19:20
1	 2010-11-03 13:18:37

Now, if I add 6 hours to the end timestamp (the only change I made to
the query) it returns this:

30	 2010-11-03 19:58:55
209	 2010-11-03 19:40:00
95	 2010-11-03 19:28:28
1	 2010-11-03 19:04:30
5	 2010-11-03 19:02:19
8	 2010-11-03 19:02:19
4	 2010-11-03 19:02:18
2	 2010-11-03 18:44:02
765	 2010-11-03 18:21:44
5	 2010-11-03 18:09:22
2	 2010-11-03 18:09:22
1	 2010-11-03 17:50:19
1	 2010-11-03 17:33:39
1	 2010-11-03 15:54:57
5	 2010-11-03 15:02:14
1	 2010-11-03 15:02:00
2	 2010-11-03 14:50:19
1	 2010-11-03 13:41:50
1	 2010-11-03 13:19:20
1	 2010-11-03 13:18:37


Where did 13:41:46, 13:19:20 and 13:22:39  go?

Thanks!
-- 
Paul Halliday
Ideation | Individualization | Learner | Achiever | Analytical
http://www.pintumbler.org
Thread
Missing results.Paul Halliday4 Nov
  • Re: Missing results.Simcha Younger4 Nov
    • Re: Missing results.Paul Halliday4 Nov