Spam Filter ISP Forums : SQL Guru Needed!

SQL Guru Needed! : Hey Ric, when you get a chance,...

Thu, 21 Oct 2004 22:44:00 +0000

Author: kspare
Subject: 4446
Posted: 21 October 2004 at 10:44pm

Hey Ric, when you get a chance, can you fire me an email to 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

SQL Guru Needed! : Well. I'm looking through...

Thu, 21 Oct 2004 15:56:00 +0000

Author: kspare
Subject: 4446
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

SQL Guru Needed! : Well, i'm playing around...

Thu, 21 Oct 2004 14:27:00 +0000

Author: kspare
Subject: 4446
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

SQL Guru Needed! : Kevin If you set both of the...

Thu, 21 Oct 2004 13:27:00 +0000

Author: Guests
Subject: 4446
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

SQL Guru Needed! : Wow, thanks for the quick reply! Just...

Thu, 21 Oct 2004 13:12:00 +0000

Author: kspare
Subject: 4446
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?

SQL Guru Needed! : OOPS! overflowed.... didn't...

Thu, 21 Oct 2004 12:56:00 +0000

Author: Guests
Subject: 4446
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':
--------------------------------------------------------------------------------

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 "

No addresses in database!

"
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 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
%>

--------------------------------------------------------------------------------

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

SQL Guru Needed! : Kevin - I've had this working...

Thu, 21 Oct 2004 12:47:00 +0000

Author: Guests
Subject: 4446
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 Off" & vbCRLF
ELSE
response.write "Reminder is OFF. Turn Reminder On" & 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 Off" & vbCRLF
ELSE
response.write "Auto-Notify is OFF. Turn Auto-Notify On" & 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':
--------------------------------------------------------------------------------

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 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
%>

--------------------------------------------------------------------------------

Thew following is saved as 'auto_reminder.asp':
--------------------------------------------------------------------------------

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 "

No addresses in database!

"
  Else
   rs.movefirst
   While Not rs.EOF
  &nb

SQL Guru Needed! : No problem Dan

Thu, 21 Oct 2004 12:02:00 +0000

Author: Desperado
Subject: 4446
Posted: 21 October 2004 at 12:02pm

No problem

Dan

SQL Guru Needed! : heh your one step ahead of me...

Thu, 21 Oct 2004 11:57:00 +0000

Author: kspare
Subject: 4446
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?

SQL Guru Needed! : Kevin, "can this be done...

Thu, 21 Oct 2004 11:53:00 +0000

Author: Desperado
Subject: 4446
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