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