RE: sql help examining log table
I might be wrong, but this may get you going in the right direction: select count(subject) as count, subject from outgoing where auth='USER' group by subject order by timestamp desc limit 50 This would tell you each subject ever sent by the user, and how many times that subject has been sent. Such as: +---+--+ | count | subject | +---+--+ | 12| Subject 1| | 36| Subject 2| | 2| Another subject | +---+--+ And you can narrow it down if you know the subject such as: select count(subject) as count, subject from outgoing where auth='USER' and subject = 'Subject 2' group by subject order by timestamp desc limit 50 This would return +---+--+ | count | subject | +---+--+ | 36| Subject 2| +---+--+ I think this is right, if not hopefully my logic is. Regardless, hopefully something can be useful. Dan -Original Message- From: Viraj Alankar [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: sql help examining log table Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql help examining log table
Ditch the Limit 50 in those queries I wrote, they won't work, they only limit the result set, not the searched set. I don't know what I was thinking. If you need to searched set to be limited by the last 50 of the user, I cannot think of anything at this time, perhaps after a few more cups of coffee:) -Original Message- From: Dan Vande More [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:44 PM To: [EMAIL PROTECTED] Subject: RE: sql help examining log table I might be wrong, but this may get you going in the right direction: select count(subject) as count, subject from outgoing where auth='USER' group by subject order by timestamp desc limit 50 This would tell you each subject ever sent by the user, and how many times that subject has been sent. Such as: +---+--+ | count | subject | +---+--+ | 12| Subject 1| | 36| Subject 2| | 2| Another subject | +---+--+ And you can narrow it down if you know the subject such as: select count(subject) as count, subject from outgoing where auth='USER' and subject = 'Subject 2' group by subject order by timestamp desc limit 50 This would return +---+--+ | count | subject | +---+--+ | 36| Subject 2| +---+--+ I think this is right, if not hopefully my logic is. Regardless, hopefully something can be useful. Dan -Original Message- From: Viraj Alankar [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: sql help examining log table Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql help examining log table
Hi Viraj, You can do it using temporary table. Create temporary table tmp select subject from outgoing where auth='USER' order by timestamp desc limit 50; Select count(distinct subject) from tmp group by subject; drop table tmp; If the result of the second query is 1 all the last 50 messages have the same subject. It is assumed there are at least 50 rows for auth = 'USER'. Regards Anvar. At 03:34 PM 17/03/2002 -0500, you wrote: Hello, We use mysql to store outgoing email headers from our users and do throttling on users that appear to be spamming based on some simple queries to this table. We use the Communigate mail server and this throttling script is a PERL program implemented as a content filter. More information is here for those interested: http://www.cse.fau.edu/~valankar/ I am trying to figure out what is the best way to do a certain query. My outgoing log table looks like this: mysql desc outgoing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | rpath | varchar(80) | YES | | NULL| | | auth | varchar(80) | | MUL | | | | ip| varchar(80) | YES | | NULL| | | hfrom | varchar(80) | YES | | NULL| | | hto | varchar(80) | YES | | NULL| | | subject | varchar(80) | YES | | NULL| | | messageid | varchar(80) | YES | | NULL| | | timestamp | timestamp(14)| YES | | NULL| | | rcpts | smallint(5) unsigned | YES | | 0 | | +---+--+--+-+-+---+ What I would like to do is reject a message if the last 50 messages have the same subject. In other words, I want to look at the 50 latest entries in this table for a certain user (identified by the auth field) to find out if all of these messages have the same subject. The only way I can think of doing this is basically: select subject from outgoing where auth='USER' order by timestamp desc limit 50 And then going through each one of these rows in my program to see if they are all the same subject. Is there a way I can do this logic in the select query instead? Thanks, Viraj. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
