query to help detect /select internal mail spam

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query to help detect /select internal mail spam

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-02-23 : 04:31:08

Hi,

I'm working a community site that has problems with members abusing the internal mail system. Users reporting abuse is the main method of detection however we would ideally like to improve detection even further as it seems less used than we hoped it would.

running some type of statement that selects the "fromUserID" of people that are sending frequent messages (small amounts of time between messages sent) sounds like a very heavy query..

does anybody have any ideas on approaches / suggestions on possible ways to query something like this? We are working to implement some front end features to prevent this but right now I'm wondering if theres any backend detection queries we can write to help check on whats getting thru currently and not being reported.

any help or ideas is much appreciated as always

thanks everyone!
mike123

the mail table structure is simple like this

tblMail

fromUserID , toUserID, message, sendDate

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-23 : 06:53:31

One way to see who is sending a lot of emails in the last 24 hours is:


select fromUserID, count(*) as cntMailSent
from tblMail
where sendDate between getdate()-1 and getdate()
group by fromUserID
Order by cntMailSent desc

This will give you a count of all users which have sent mails, those who have sent the most first..

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-02-23 : 07:31:06

How about adding a sort of "spam reporting tool" so that if say 3 users report the same message as spam you delete the rest of them automatically? Or you could add a datetime-field in your users-table that has a timestamp with the last time they sent a message, and only allow one message say every x minutes...?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-23 : 09:02:14

quote:
Originally posted by RickD

One way to see who is sending a lot of emails in the last 24 hours is:

select fromUserID, count(*) as cntMailSent
from tblMail
where sendDate between getdate()-1 and getdate()
group by fromUserID
Order by cntMailSent desc

This will give you a count of all users which have sent mails, those who have sent the most first..

I think that's a good approach; it is much more efficient than trying to measure the intervals between emails. If someone has sent out 2,000 emails in 1 day, that should be a pretty good clue they are up to something.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-24 : 03:57:28

minor adjustment/suggestion:
--< (nolock) if need to be concerned about contention? This is only reporting
-- have a limit variable so that the report size is more managable ...


declare @ReportLimit int
set @ReportLimit = 2000

select fromUserID, count(*) as cntMailSent
from tblMail (nolock) 
where sendDate between getdate()-1 and getdate()
group by fromUserID
having count(*) >= @ReportLimit 
Order by cntMailSent desc

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-02-24 : 20:31:28

I also work with a community site, and we have the same issue.

Our approach is very much like what Wanderer posted here; it runs every 10 minutes as a SQL Agent job, and emails the offending user info to our customer support group, who can then look more deeply at it or just click a link to delete email from that user.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources