List:General Discussion« Previous MessageNext Message »
From:Simcha Younger Date:November 4 2010 5:34pm
Subject:Re: Missing results.
View as plain text  
On Thu, 4 Nov 2010 08:07:02 -0300
Paul Halliday <paul.halliday@stripped> wrote:


> 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
....
> 1	 2010-11-03 13:41:50
> 1	 2010-11-03 13:19:20
> 1	 2010-11-03 13:18:37
> 


Presumably those records were absorbed into your 'group by' clause, since there was an
entry, from a later time, which had the same values for all the group by fields.


-- 
Simcha Younger <simcha@stripped>
Thread
Missing results.Paul Halliday4 Nov
  • Re: Missing results.Simcha Younger4 Nov
    • Re: Missing results.Paul Halliday4 Nov