--- Paul Halliday <[EMAIL PROTECTED]> wrote:
> On 4/23/06, John Hicks <[EMAIL PROTECTED]> 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
> >
> >
>
I think what you are looking for is the DISTINCT modifier to the
COUNT() aggregate function. That way you count only how many different
values exist in the list, not how many items are in the list.
Count | IP Address | First Seen | Last Seen | Days
SELECT
COUNT(src_ip) AS CNT
, INET_NTOA(src_ip)
, MIN(timestamp) first
, MAX(timestamp) last
, COUNT(DISTINCT DATE(timestamp)) days
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
That is close to what you want. There are several ways to convert
timetsamps values into something that can be counted as a unique day
but I think the DATE function will be fast enough.
Shawn Green
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
