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 <[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
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
