List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 24 2006 6:53pm
Subject:Re: If exists query.
View as plain text  
Paul,

 >> > > I am doing queries that produce a table that looks something 
like this:
 >> > >
 >> > > Count   | IP Address    | First Seen    | Last Seen     | Days
 >> > > 5000      10.0.0.1           2005-12-10    2006-04-15      50*
 >> > > 6500      10.0.0.2           2006-04-01    2006-04-06      3**
 >> > >
 >> > > *So of the 5000 events "count(*) between 2005-12-10 and 2006-04-14"
 >> > > the count was distributed within 50 distinct days.
 >> > >
 >> > > **This time the count is still high and it occured within 3 days
 >> > > between 2006-04-01 and 2006-04-06.
 >> > >
 >> > > I just cant figure out how to come up with the days part  :) .
<snip>

You could  grab the first day for each src_ip with something like ...

SELECT
  COUNT(src_ip) AS CNT,
  INET_NTOA(src_ip),
  MAX(timestamp),
  (SELECT MIN(timestamp) FROM event e2 WHERE e2.src_ip=e1.src_ip) AS First
FROM event e1
WHERE timestamp > '2006-04-24 03:00:00'
  AND sid=1
  AND signature_id<>1
GROUP BY src_ip
ORDER BY cnt DESC LIMIT 10

 >I now want to know; out of all of the days between first seen and last
 >seen which days had events on them. I dont want the count(events) for
 >eah day, just whether there was an event or not so that I know between
 >first seen and last seen what the rate of appearance was.

That's easiest if you maintain a calendar table with one row per day in 
the appropriate range. Then you can simply count the rows you get on a 
ranged events-calendar join.

PB

-----

Paul Halliday wrote:
> On 4/23/06, John Hicks <johnlist@stripped> wrote:
>   
>> Paul Halliday wrote:
>>     
>
>   
>>> I am doing queries that produce a table that looks something like this:
>>>
>>> Count   | IP Address    | First Seen    | Last Seen     | Days
>>> 5000      10.0.0.1           2005-12-10    2006-04-15      50*
>>> 6500      10.0.0.2           2006-04-01    2006-04-06      3**
>>>
>>> *So of the 5000 events "count(*) between 2005-12-10 and 2006-04-14"
>>> the count was distributed within 50 distinct days.
>>>
>>> **This time the count is still high and it occured within 3 days
>>> between 2006-04-01 and 2006-04-06.
>>>
>>> I just cant figure out how to come up with the days part :).
>>>       
>> Well, I guess that is a *bit* more specific, but not much better :)
>>
>> So I'm guessing that the source of this data is perhaps a web access log
>> and that you are tracking IP addresses of visitors.
>> Can we tickle a little more information out of you?
>>     
>
> Sorry,
>
> Ok. The data is IDS events. I am not trying to create any new
> information I just want to extract information. This information will
> be used to relay whether a particular machine has ongoing issues.
>
> For example,
>
> SELECT COUNT(src_ip) AS CNT, INET_NTOA(src_ip), MAX(timestamp) FROM
> event WHERE timestamp > '2006-04-24 03:00:00' AND sid="1" AND
> signature_id<>"1" GROUP BY src_ip ORDER BY cnt DESC LIMIT 10
>
> This will give me the top 10 source addresses for today based on how
> many events they have triggered.
>
> If they make the top ten, I want to see when we first saw that address:
>
> SELECT MIN(timestamp) FROM event WHERE src_ip=INET_ATON('<loop through
> top ten>')
>
> I now want to know; out of all of the days between first seen and last
> seen which days had events on them. I dont want the count(events) for
> eah day, just whether there was an event or not so that I know between
> first seen and last seen what the rate of appearance was.
>
> I could do something crufty like this (the row count would be the
> answer I am looking for):
>
> SELECT DATE_FORMAT(timestamp,'%Y-%m-%d %a') AS DAY, COUNT(*) FROM
> event WHERE timestamp BETWEEN '2006-01-01' AND '2006-04-24' and
> src_ip=INET_NTOA('10.0.0.1') OR dst_ip=INET_ATON('10.0.0.1') GROUP BY
> DAY;
>
> But that seems like a lot of extra processing.
>
> Thanks and sorry for the confusion.
>
>   
>> Ideally, it would be nice to know what task you are trying to accomplish.
>>
>> What is the source of your data?
>>
>> What is the "condition" you are testing for?
>>
>> And what, very specifically, is it that you would like us to help you with.
>>
>> --John
>>
>>
>>     
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.5/322 - Release Date: 4/22/2006
Thread
If exists query.Paul Halliday21 Apr
  • Re: If exists query.John Hicks23 Apr
    • Re: If exists query.Paul Halliday23 Apr
Re: If exists query.Paul Halliday24 Apr
  • Re: If exists query.Shawn Green24 Apr
  • Re: If exists query.Peter Brawley24 Apr