I would try these 3 query statement to find the users for the bracketing
times (start and stop):
SELECT @starttime = max(time)
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time <='16:15:00'
AND RecordType = 'Start';
SELECT @endtime = min(time)
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time >= @starttime
AND RecordType = 'Stop';
SELECT *
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time in (@starttime, @stoptime)
That way, if the user's name matches for both records, you are sure you
have identified the correct user for a particular time
I know it appears to be a lot of steps but with a composite index on (ID,
Date, Time, RecordType) each of these queries should respond very quickly.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
[EMAIL PROTECTED] wrote on 07/21/2004 07:49:35 AM:
> Ok, this may or may not be a tricky one I will try and be succinct in my
> statement.
>
> I have a database (mysql 4.0) with radius log entries for each day, we
> receive emails about Acceptable Use Abuses and must figure out exactly
> who was online with a certain IP address when the abuse occurred. As you
> will see below there are multiple starts and stops for any given IP
> address so here is the scenario:
>
> Problem: Spam Abuse
> IP of offender: 66.50.xxX.245
> Date of offense: 2004-07-05
> Time of offense: 16:15
>
> Now if I query the database based on date and ip address, I get the
> following:
> Id Date Time Record Type Full
> Name IP Address
> ====== ==== ============ ============ ============
> =========================
>
> 349 2004-07-05 11:21:08 Start [EMAIL PROTECTED]
> 66.50.xxX.245
> 345 2004-07-05 11:21:09 Start [EMAIL PROTECTED]
> 66.50.xxX.245
> 413 2004-07-05 11:22:32 Stop [EMAIL PROTECTED]
> 66.50.xxX.245
> 118984 2004-07-05 17:22:26 Start [EMAIL PROTECTED]
> 66.50.xxX.245
> 149049 2004-07-05 18:36:19 Stop [EMAIL PROTECTED]
> 66.50.xxX.245
> 90344 2004-07-05 16:09:40 Start [EMAIL PROTECTED]
> 66.50.xxX.245
> 90380 2004-07-05 16:09:40 Start [EMAIL PROTECTED]
> 66.50.xxX.245
> 97630 2004-07-05 16:28:20 Stop [EMAIL PROTECTED]
> 66.50.xxX.245
> 97671 2004-07-05 16:28:20 Stop [EMAIL PROTECTED]
> 66.50.xxX.245
> 97598 2004-07-05 16:28:20 Stop [EMAIL PROTECTED]
> 66.50.xxX.245
>
> now of course I changed the usernames and modified the IP for this
> mailing but that doesn't matter, now, the time field in the Database IS
> a time data type. What I need to be able to do is find the start before
> the offense time, and the stop after the offense time so I know that the
> person with the start and the stop is the one that committed the abuse.
>
> I haven't actually put code to bits yet, because I am not exactly sure
> how to go about creating this logic code. I don't think I can just say
> if on Date , if $timefield < time of offense and $timefield > time of
> offense; return some stuff.
>
> So any help on how to start with this would be greatly appreciated.
>
> Chris Hood
> Investigator Verizon Global Security Operations Center
> Email: <mailto:[EMAIL PROTECTED]>
> [EMAIL PROTECTED]
> Desk: 972.399.5900
>
>
