List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:April 24 2006 12:27pm
Subject:Re: If exists query.
View as plain text  
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
>
>
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