Re: find out who was online at a given time
You're absolutely right about the := not just = (Sorry!!) Actually, I DID want to use an IN and not a BETWEEN. What I was doing was picking the two boundary times around a given point in time so once I got those numbers, I wanted to get both records. I could have written the WHERE like this: WHERE [EMAIL PROTECTED] OR [EMAIL PROTECTED] Excellent catch for the logic failure of not detecting a starttime before midnight. I also just realized that instead of trying to make *date* matches in the final SELECT, I should have collected the row IDs in the first two queries. That would change the whole group to look like this (and I added some parametric variables to make it more generic): SET @targetIP ='66.50.xxX.245', @targetDate = '2004-07-05', @targetTime = '16:15:00'; SELECT @starttime := max(ID) FROM logtable WHERE IP = @targetIP AND ( (Date= @targetDate AND Time [EMAIL PROTECTED]) OR (Date @targetDate AND Date (@targetDate - interval 1 week)) ) AND RecordType = 'Start'; SELECT @endtime := min(ID) FROM logtable WHERE IP = @targetIP AND ID @starttime AND Date = (@targetDate + interval 1 week) AND RecordType = 'Stop'; SELECT * FROM logtable WHERE ID in (@starttime, @stoptime) I limited the search for a Start or a Stop record to +/- 1 week from the target date. Depending on the actual usage patterns, that range may need to change. I also suggested earlier today (off list) that the original poster try to combine his separate date and time columns to a single column so that a true index date search would be possible. John, thank you. Humbly, Shawn Green Database Administrator Unimin Corporation - Spruce Pine John David Duncan [EMAIL PROTECTED] wrote on 07/22/2004 02:50:44 PM: The three query solution looks like a good one; but here are some notes... SELECT @starttime = max(time) There's a colon necessary in the syntax here -- SELECT @starttime := max(time) SELECT * FROM logtable WHERE IP='66.50.xxX.245' AND Date='2004-07-05' AND Time in (@starttime, @stoptime) ... but that should be between instead of in, right? 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. ... and I would put the index on (IP,RecordType,Date), because I don't thing the index on time is going to be usable. The flaw here is that the query will fail to find spammers who dial in before midnight on the previous day. I think the best solutions would all require a slightly different data structure, where you somehow have a single database record per session which records both the start time and the stop time. (In MySQL 4.1, you could even use geometric data types, where the call as stored as a line between the start point and the stop point, and the query is treated as a search for lines containing a certain point.) - JD -- John David Duncan, Consulting Engineer MySQL AB, www.mysql.com Office: +1 415 664-7396 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find out who was online at a given time
Actually, I DID want to use an IN and not a BETWEEN. What I was doing was picking the two boundary times around a given point in time so once I got those numbers, I wanted to get both records. Ah, yes, I see I didn't really understand it the first time -- and using IDs instead of timestamps is even better... Cheers, JD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find out who was online at a given time
[EMAIL PROTECTED] wrote: 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 TypeFull Name IP Address == = 349 2004-07-0511:21:08 Start [EMAIL PROTECTED] 66.50.xxX.245 345 2004-07-0511:21:09 Start [EMAIL PROTECTED] 66.50.xxX.245 413 2004-07-0511:22:32 Stop [EMAIL PROTECTED] 66.50.xxX.245 [...] 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. what a about the very simple approach? This should be very fast if you habe indexes on ip, date, time and record_type. select * from table where ip=1.1.1.1 and datefield=2004-07-05 and timefield=16:15:00 and record_type=Start order by timefield desc limit 1; select * from table where ip=1.1.1.1 and datefield=2004-07-05 and timefield=16:15:00 and record_type=Stop order by timefield limit 1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find out who was online at a given time
Jan Kirchhoff [EMAIL PROTECTED] wrote on 21/07/2004 13:08:38: what a about the very simple approach? This should be very fast if you habe indexes on ip, date, time and record_type. I would have though it would be best to have a composite index on date/time/record_type, which is what both halves of the query will want. I would expect MySQL to handle this query very well. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find out who was online at a given time
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 TypeFull Name IP Address == = 349 2004-07-0511:21:08 Start [EMAIL PROTECTED] 66.50.xxX.245 345 2004-07-0511:21:09 Start [EMAIL PROTECTED] 66.50.xxX.245 413 2004-07-0511:22:32 Stop [EMAIL PROTECTED] 66.50.xxX.245 118984 2004-07-0517:22:26 Start [EMAIL PROTECTED] 66.50.xxX.245 149049 2004-07-0518:36:19 Stop [EMAIL PROTECTED] 66.50.xxX.245 90344 2004-07-0516:09:40 Start [EMAIL PROTECTED] 66.50.xxX.245 90380 2004-07-0516:09:40 Start [EMAIL PROTECTED] 66.50.xxX.245 97630 2004-07-0516:28:20 Stop [EMAIL PROTECTED] 66.50.xxX.245 97671 2004-07-0516:28:20 Stop [EMAIL PROTECTED] 66.50.xxX.245 97598 2004-07-0516: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
