Spam Filter ISP Support - SQL Guru Needed!

Print Page | Close Window

SQL Guru Needed!

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=4446
Printed Date: 13 March 2025 at 5:36pm

Topic: SQL Guru Needed!

Posted By: kspare
Subject: SQL Guru Needed!
Date Posted: 21 October 2004 at 12:49am

I came up with an idea, but I can't make it work because of my limited SQL knowledge.

I want to create a script that will run once or twice a day that will generate an email to be send to my users to let them know there is a spam message addressed to them in the quaruntine. Can anyone help me with this?

Kevin

Replies:

Posted By: Desperado
Date Posted: 21 October 2004 at 11:40am

Kevin,

This can actually get semi complex but ... how familiar are you with stored procedures? You can write a procedure that gets all new blocked messages and use the SQL scheduler as the "trigger" for it. What you do from there depends on your person programming style. You can either have the procedure RE-WRITE a database table and use the addresses from there to generate massages or you can have the procedure overwrite an "flat file" for the same purpose. From there you can use CDO (Don't use CDONTS as it has been superceded and it will not work on 2003 server) to send out messages (my preference) or, I suppose you could actually use SpamFilter to send it out using the same methodology at the self registering ASP sample.

Also, I believe someone wrote a VB app to do this and posted it somewhere. I haven't really looked at that though.

Regards,

Dan S.

Posted By: kspare
Date Posted: 21 October 2004 at 11:44am

Hey Dan, as you know i'm not much of a programmer, but I can come up with great ideas :)

Basically, what I would it to do is this.

Count the # of spam for a user, and email them with a generic email to the counted email address, containing the email address mailed to, the number of spam counted and a link to where they can check the spam?

can this be done easily?

What I don't want is a spam sent out EACH time a spam enters the q. I want this to happen only twice a day. Maybe even onyl once a day.

Kevin

Posted By: Desperado
Date Posted: 21 October 2004 at 11:53am

Kevin,

"can this be done easily?" I guess that depends on how you define "easily". I need to think about it some more. I will get back to you. However, I do not call myself a "Guru" ... more like a "Hack"

Dan

Posted By: kspare
Date Posted: 21 October 2004 at 11:57am

heh your one step ahead of me Dan! When you come up with some ideas, could you email me?

Posted By: Desperado
Date Posted: 21 October 2004 at 12:02pm

No problem

Dan

Posted By: Guests
Date Posted: 21 October 2004 at 12:47pm

Kevin -

I've had this working since I first started using SpamFilterISP. It's dependant on the Windows scheduler (on the SF server) to run one page(auto_notify.asp') in IE hourly during business hours, and a second page('auto_reminder.asp') at 6am each business day. I am not an ISP; I use SF at my business... so this will be different for those of you processing 100's of thousands of messages a day (server load to process these pages, script timeout, etc.)... we average under 10K messages daily.

Some users are annoyed with messages throughout the day, and just want one reminder in the morning, so I gave them both options (some choose to have both). The morning reminder is limited (you can easily change this) to only notify those with more than 50 messages in the quarantine.

I modified the 'tbllogins' table to add two fields - 'notify' and 'reminder'. (I'm using MySQL). They are both type 'bool' fields, defaulting to -1 (on).

At the top of the 'listspam.asp' page I added:
--------------------------------------------------------------------------------
<%
'update the reminder field for this user
IF Request.QueryString("Reminder") = "on" THEN
 SQL = "UPDATE tblLogins SET Reminder = -1 WHERE (Email = '" & EmailTo & "')"
 Set rs = con.Execute(SQL)
ELSEIF Request.QueryString("Reminder") = "off" THEN
 SQL = "UPDATE tblLogins SET Reminder = 0 WHERE (Email = '" & EmailTo & "')"
 Set rs = con.Execute(SQL)
END IF

'update the notification field for this user
IF Request.QueryString("Notify") = "on" THEN
  SQL = "UPDATE tblLogins SET Notify = -1 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL)
ELSEIF Request.QueryString("Notify") = "off" THEN
  SQL = "UPDATE tblLogins SET Notify = 0 WHERE (Email = '" & EmailTo & "')"
  Set rs = con.Execute(SQL)
END IF
%>
--------------------------------------------------------------------------------

At the bottom of the 'listspam.asp' page, I have added the following:
--------------------------------------------------------------------------------
<%
SQL = "SELECT Reminder FROM tblLogins WHERE (Email = '" & EmailTo & "')"
Set rs = con.Execute(SQL)
IF clng(rs("Reminder")) = -1 THEN
response.write "Reminder is ON. Turn Reminder <a href=""ListSpam.asp?Reminder=off"">Off</a>" & vbCRLF
ELSE
response.write "Reminder is OFF. Turn Reminder <a href=""ListSpam.asp?Reminder=on"">On</a>" & vbCRLF
END IF
response.write "  - - A daily reminder to check your quarantined messages will be sent to you if this feature is turned on. " & vbCRLF

SQL = "SELECT Notify FROM tblLogins WHERE (Email = '" & EmailTo & "')"
Set rs = con.Execute(SQL)
IF clng(rs("Notify")) = -1 THEN
response.write "Auto-Notify is ON. Turn Auto-Notify <a href=""ListSpam.asp?notify=off"">Off</a>" & vbCRLF
ELSE
response.write "Auto-Notify is OFF. Turn Auto-Notify <a href=""ListSpam.asp?notify=on"">On</a>" & vbCRLF
END IF
response.write "  - - An immediate notification of possible spam will be sent to you if this feature is turned on. " & vbCRLF
%>
--------------------------------------------------------------------------------

The above code gives the users a 'toggle' to turn on and off their notification messages.

Next, I have the following saved as 'auto_notify.asp':
--------------------------------------------------------------------------------
<html><body>
Sending Reminders to those with messages in queue and reminders tuned on. 
<%
X = 0
SQL = "SELECT * FROM tbllogins WHERE Notify = -1 ORDER BY Email ASC"

Set rs = con.Execute(SQL)
IF NOT (rs.eof AND rs.bof) THEN
 rs.MoveFirst
 DO UNTIL rs.eof
 SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
 Set rs2 = con.Execute(SQL)
 IF clng(rs2("MessageCount")) > 0 THEN
 X = X + 1
 SendMessage rs("Email"), rs2("MessageCount")
 response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... notification sent. " & vbcrlf
 END IF
 rs2.close
 set rs2 = nothing
 rs.MoveNext
 LOOP
END IF

response.write " " & X & " notification messages sent. " & vbCRLF

SUB SendMessage(strEmail, lMessageCount)
Set rs3 = Server.CreateObject("ADODB.Recordset")
Set rs3.ActiveConnection = con
rs3.CursorType = 1
rs3.LockType = 3
'Send message to user by way of the quarantine.
MessageDate = Now
Message = "From: System Administrator <>" & vbCRLF
Message = Message & "To: " & strEMail & vbCRLF
Message = Message & "Subject: possible SPAM Notification: You have messages being held in quarantine" & vbCRLF
Message = Message & "Date: " & FormatDateTime(MessageDate, vbLongDate) & " " & FormatDateTime(MessageDate, vbLongTime) & vbCRLF
Message = Message & "Content-Type: text/html;charset=""ISO-8859-1""" & vbCRLF & vbCRLF
Message = Message & "Your have " & lMessageCount & " messages being held in the quarantine area" & vbCRLF
Message = Message & "For your email account '" & strEmail & "'." & vbCRLF & vbCRLF
Message = Message & "THESE MESSAGES **MAY** HAVE ALREADY BEEN CLEARED BY THE SYSTEM ADMINISTRATOR FOR YOU" & vbCRLF & vbCRLF
Message = Message & "Please log into http://(your" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - http://" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - http:// (your quarantine website) and check your messages." & vbCRLF & vbCRLF
Message = Message & "This message was sent because you turned auto-notification on." & vbCRLF
Message = Message & "." & vbCRLF
SQL = "SELECT * FROM tblMsgs WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("Msg") = Message
rs3.Update
MsgID = rs3("MsgID")
response.write "MsgID= " & msgID & " " & vbCRLF
rs3.Close
SQL="SELECT * FROM tblQuarantine WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("EMailFrom") = "System Administrator <>"
rs3("EMailTo") = strEMail
rs3("Subject") = "Daily Reminder: You have messages being held in quarantine"
rs3("MsgID") = MsgID
rs3("MsgDate") = MessageDate
rs3("Expire") = 0
rs3("Deliver") = 1
rs3("RejectDetails") = ""
rs3("RejectID") = 0
rs3("ServerID") = 1
rs3.Update
rs3.Close
Set rs3 = Nothing
END SUB
%>
</body>
</html>
--------------------------------------------------------------------------------

Thew following is saved as 'auto_reminder.asp':
--------------------------------------------------------------------------------
<html>
<body>
Sending Reminders to those with messages in queue and reminders tuned on. 
<%
X = 0
SQL = "SELECT * FROM tbllogins WHERE Reminder = -1 ORDER BY Email ASC"
Set rs = con.Execute(SQL)
IF NOT (rs.eof AND rs.bof) THEN
 rs.MoveFirst
 DO UNTIL rs.eof
 SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
 Set rs2 = con.Execute(SQL)
 IF clng(rs2("MessageCount")) > 0 THEN
 X = X + 1
 SendMessage rs("Email"), rs2("MessageCount")
 response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... reminder sent. " & vbcrlf
 ELSE
 response.write rs("Email") & " has 0 messages in queue... reminder NOT sent. " & vbcrlf
 END IF
 rs2.close
 set rs2 = nothing
 rs.MoveNext
 LOOP
END IF
response.write " " & X & " reminder messages sent. " & vbCRLF
response.write "Sending reminders to those with excessive messages in queue (50 or more). " & vbCRLF & vbCRLF
X = 0
 SQL = "SELECT DISTINCT EmailTo "
 SQL = SQL & "FROM tblquarantine "
 SQL = SQL & "WHERE Expire = 0 "
 SQL = SQL & "ORDER BY EmailTo"
 Set rs = con.Execute(SQL)
 If rs.EOF Then
 Response.Write "<h2>No addresses in database!</h2>"
 Else
 rs.movefirst
 While Not rs.EOF
 &nb

Posted By: Guests
Date Posted: 21 October 2004 at 12:56pm

OOPS! overflowed.... didn't realize there was a limit to length of posts...

Here's the rest:

The following is saved as 'auto_reminder.asp':
--------------------------------------------------------------------------------
<html>
<body>
Sending Reminders to those with messages in queue and reminders tuned on. 
<%
X = 0
SQL = "SELECT * FROM tbllogins WHERE Reminder = -1 ORDER BY Email ASC"
Set rs = con.Execute(SQL)
IF NOT (rs.eof AND rs.bof) THEN
rs.MoveFirst
DO UNTIL rs.eof
 SQL = "SELECT COUNT(*) AS MessageCount FROM tblQuarantine WHERE (EmailTo = '" & rs("Email") & "') AND Expire = 0 AND Deliver = 0"
 Set rs2 = con.Execute(SQL)
 IF clng(rs2("MessageCount")) > 0 THEN
 X = X + 1
 SendMessage rs("Email"), rs2("MessageCount")
 response.write rs("Email") & " has " & rs2("MessageCount") & " messages in queue... reminder sent. " & vbcrlf
 ELSE
 response.write rs("Email") & " has 0 messages in queue... reminder NOT sent. " & vbcrlf
 END IF
 rs2.close
 set rs2 = nothing
 rs.MoveNext
LOOP
END IF
response.write " " & X & " reminder messages sent. " & vbCRLF
response.write "Sending reminders to those with excessive messages in queue (50 or more). " & vbCRLF & vbCRLF
X = 0
SQL = "SELECT DISTINCT EmailTo "
SQL = SQL & "FROM tblquarantine "
SQL = SQL & "WHERE Expire = 0 "
SQL = SQL & "ORDER BY EmailTo"
Set rs = con.Execute(SQL)
If rs.EOF Then
 Response.Write "<h2>No addresses in database!</h2>"
Else
 rs.movefirst
 While Not rs.EOF
 EmailTo = lcase(rs("EmailTo"))
 OKToMail = True
 SQL2 = "SELECT * FROM tbllogins WHERE Email = '" & EmailTo & "'"
 Set TestRS = con.Execute(SQL2)
 IF not (TestRS.eof AND TestRS.bof) THEN
 IF TestRS("Reminder") = "-1" THEN
 OKToMail = False
 END IF
 END IF
 TestRS.close
 Set TestRs = nothing
 IF OKToMail THEN
 SQL = "SELECT COUNT(EmailTo) AS MessageCount FROM tblQuarantine WHERE (lcase(EmailTo) = '" & lcase(EmailTo) & "')"
 Set MessageCountRS = con.Execute(SQL)
 IF MessageCountRS(0) > 50 THEN
 SendMessage EmailTo, MessageCountRS(0)
 X = X + 1
 response.write EmailTo & " has " & MessageCountRS(0) & " messages in queue... reminder sent. " & vbcrlf
 ELSE
 response.write EmailTo & " has " & MessageCountRS(0) & " messages in queue... reminder NOT sent. " & vbcrlf
 END IF
 END IF
 rs.MoveNext
 Wend
END IF
rs.close
set rs = nothing
con.close
set con = nothing
response.write " " & X & " reminder messages sent foir excessive messages in queue. " & vbCRLF

SUB SendMessage(strEmail, lMessageCount)
Set rs3 = Server.CreateObject("ADODB.Recordset")
Set rs3.ActiveConnection = con
rs3.CursorType = 1
rs3.LockType = 3
MessageDate = Now
Message = "From: System Administrator <>" & vbCRLF
Message = Message & "To: " & strEMail & vbCRLF
Message = Message & "Subject: Daily Reminder: You have messages being held in quarantine" & vbCRLF
Message = Message & "Date: " & FormatDateTime(MessageDate, vbLongDate) & " " & FormatDateTime(MessageDate, vbLongTime) & vbCRLF
Message = Message & "Content-Type: text/html;charset=""ISO-8859-1""" & vbCRLF & vbCRLF
Message = Message & "Your have " & lMessageCount & " messages being held in the quarantine area" & vbCRLF
Message = Message & "For your email account '" & strEmail & "'." & vbCRLF & vbCRLF
Message = Message & "THESE MESSAGES **MAY** HAVE ALREADY BEEN CLEARED BY THE SYSTEM ADMINISTRATOR FOR YOU" & vbCRLF & vbCRLF
Message = Message & "Please log into http://(your" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - http://" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - http:// (your quarantine website) and check your messages." & vbCRLF
Message = Message & "." & vbCRLF
SQL = "SELECT * FROM tblMsgs WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("Msg") = Message
rs3.Update
MsgID = rs3("MsgID")
response.write "MsgID= " & msgID & " " & vbCRLF
rs3.Close
SQL="SELECT * FROM tblQuarantine WHERE 0 = 1"
rs3.Open(SQL)
rs3.AddNew
rs3("EMailFrom") = "System Administrator <>"
rs3("EMailTo") = strEMail
rs3("Subject") = "Daily Reminder: You have messages being held in quarantine"
rs3("MsgID") = MsgID
rs3("MsgDate") = MessageDate
rs3("Expire") = 0
rs3("Deliver") = 1
rs3("RejectDetails") = ""
rs3("RejectID") = 0
rs3("ServerID") = 1
rs3.Update
rs3.Close
Set rs3 = Nothing
END SUB
%>
</body>
</html>
--------------------------------------------------------------------------------

It's not the cleanest code, I just hacked it together... but I hope this gives you some ideas to help you with your quest.

Ric Marques
Systems Manager
Chico Community Publishing

Posted By: kspare
Date Posted: 21 October 2004 at 1:12pm

Wow, thanks for the quick reply!

Just so I understand this, the auto-notify only sends to people who switch this option on, and autoreminder sends to everyone in the database?

Posted By: Guests
Date Posted: 21 October 2004 at 1:27pm

Kevin

If you set both of the new fields (reminder and notify) in the tbllogins table to default to -1, then both messages will automatically be sent. The toggles at the bottom of the listspam page let the users control if they get the messages or not. You can take away the toggle for the reminder message so that they can't turn it off if you'd like.

-Ric

Posted By: kspare
Date Posted: 21 October 2004 at 2:27pm

Well, i'm playing around with the code, I do want to allow people who have setup an account in spamfilter to turn off notification, right now i'm just playing around with trying to get the web code working so they can turn it on and off lol

Posted By: kspare
Date Posted: 21 October 2004 at 3:56pm

Well. I'm looking through this, Great Idea. I just gotta figure out how to make this work with MS-SQL!! lol

Posted By: kspare
Date Posted: 21 October 2004 at 10:44pm

Hey Ric, when you get a chance, can you fire me an email to mailto:kevin@pare.ca" CLASS="ASPForums" TITLE="WARNING: URL created by poster. - kevin@pare.ca so I can talk to you about this code? The logic is perfect, I just can't get it to work!

Kevin

Print Page | Close Window