Need a query to determine who has turned on notifications

[jdgretz]

Subject pretty much says it all.

I need to know who has turned on notifications on a given board so I can bounce that against the list of emails SMF sends out when a new topic is posted.  I am trying to run down why the folks I believe have subscribed to notifications are not getting them.  I've checked the configuration and it looks good.  The test email php script works fine and Announcing a post works, but email does not seem to be sending to all of the folks it should.

Thanks,

jdg

[shawnb61]

Hmmm...  I've never done that before...  Some guesswork here, but I would start with something like this:

Code Select Expand

select b.id_board, b.name, m.id_member, m.member_name, m.email_address, m.notify_announcements, m.notify_regularity, m.notify_types, m.is_activated, ln.sent
from smf_log_notify AS ln, smf_members as m, smf_boards as b
where ln.id_member = m.id_member
and ln.id_board = b.id_board
and ln.id_board = 40
order by m.member_name


Change the id_board in the where clause as you see fit.
You can change it from board notifications to topic notifications if needed.
You may find looking at log_digests helpful.

If using smtp, you should be able to logon as the master account on your email provider and see information on what is getting kicked back. 

When I have seen this in the past, host specific kickbacks, it had to do with host A temporarily blacklisting host B, accusing it of allowing spam...  Host B (your guy) would need to contact Host A.

[jdgretz]

Thanks - that helps.  Now if I can figure out why SMF is not sending notifications to those folks, I'll really be happy.

jdg

[Ricky.]

It could be problem more with server configuration, if it is mostly Yahoo and hotmail not receiving mail then SMF is not the culprit. If its SMF 2.x.x then turn on email queue and you can see what is being sent .

[jdgretz]

It could be problem more with server configuration, if it is mostly Yahoo and hotmail not receiving mail then SMF is not the culprit. If its SMF 2.x.x then turn on email queue and you can see what is being sent .

I already know about what is being sent - something like 25-30 notifications.  The problem is that number should be closer to 1200.  The absolute lowest number it should ever be is around 60.

First step is to determine exactly how bad the problem is.  So far, no one seems to have an answer why SMF (currently 2.0.17) is not sending, or even queueing those messages to be sent.

jdg

[shawnb61]

I already know about what is being sent - something like 25-30 notifications.  The problem is that number should be closer to 1200.  The absolute lowest number it should ever be is around 60.

Are 25-30 getting sent when you run it manually?  If so, that may make sense.

There are many hurdles to getting a mail out the door.  There are hurdles there specifically to avoid spamming folks & sending notes too often. 
- User must OK receiving emails
- User must request for certain types (e.g., board or topic notifications)
- You cannot send digests too close together, IIRC, you have to wait at least 1/2 the time
- The best way to test is to setup a test account & wait a week...  Otherwise you'll never see a complete mailing.

If you don't queue the email, you won't see it.  And the queue moves very fast unless you change the settings & deliberately slow it down (max emails per minute settings)...

As noted above, on multiple occasions I've seen hosts blacklist each other as spammers.  The only way I've gotten confirmation on these errors was to configure email as SMTP and to check the master account on the host.  You can also email directly from there, making it much easier to diagnose real mail issues.  You actually see the kicked back email, & it will usually have a code, e.g., "550 Blocked for abuse", & a link to address.  If you're already SMTP, find out from your host how to logon to the main email account; you may find a lot of surprises in your inbox...

[shawnb61]

An example of a 550 is attached.  In this instance somebody blacklisted my host, Hostgator, & I received this notice this morning after my weekly digests went out.

My point is that you will never see any of your kickbacks if you don't look.  Logon to your main account on your host.

I believe kickbacks will show there whether SMTP or "PHP" is selected. 

One more diagnostic tool: In your php.ini file, there is an option to log your mail activity.  That might help as well.  I have never resorted to that, but it may help.

[jdgretz]

An example of a 550 is attached.  In this instance somebody blacklisted my host, Hostgator, & I received this notice this morning after my weekly digests went out.

My point is that you will never see any of your kickbacks if you don't look.  Logon to your main account on your host.

I believe kickbacks will show there whether SMTP or "PHP" is selected. 

One more diagnostic tool: In your php.ini file, there is an option to log your mail activity.  That might help as well.  I have never resorted to that, but it may help.

Ahh, I'll find that option in the php.ini and turn it on, at least for a week or so.

Addiditonal stuff -

We do email via a queue.  Here is how the board works -

Socalpgr.org is the main landing page.  The board is located at socalprg.org/pgr.  The board is used to announce missions (funerals to which we are invited), and a bit of For Sale stuff in a sub board.  Our members receive notices as new missions are posted.

At some point about a year or so ago, we started getting calls from our members, that, even though they had signed up for notifications, they were receiving nothing.  Our provider (member of the organization) was not seeing bounced messages, so that was discounted (we went through quite a thing with our AOL users when we first set this thing up).  So, what we would do, is post a  mission and immediately take a look at the mail queue in another window.  What we found was about 10 people always got the notice, while another random 15-25 people would also get a notice.  This out of 1700+ users.  We started using the Announce Topic feature to dump to all our members, thus everyone started receiving mission notifications/announcements again.  Those of us who are in the lucky 10 get both.

I can use the manual mail checking script, and it always sends to whoever.  But, it does not help with figuring out our problem. Hopefully, this php.ini switch will give me some answers.

The problem with the Announce Topic feature is that the mail is queued with LOW priority, where normal notifications go out with NORMAL priority, and clear the queue quickly.  We have very little board traffic, so it can take days to clear the queue of LOW priority messages - which does not work for us.

I know, way more information than you probably needed, but there it is anyway.

All suggestions are welcome.  I'm willing to give just about anything a shot.  I could also give you access to the test board on our new provider, which exhibits the same symptoms.

Thanks,

jdg

[shawnb61]

Two notes... 

First - There are more notifications settings your users should be aware of - look under Profile | Modify Profile | Settings for users not receiving emails.  Some of this is set initially upon registration, and users usually forget they instinctively turned off receiving emails...  That first one MUST be checked.  How the others are set determines whether mails are sent daily/weekly/instantly or once-only.  I included all of these in the query above. 

Second - I've been thru this before.  And I had to ditch the email from the small host.  Simply put, they get picked on by the big guys.  They need staff to followup on all the inter-host issues like the 550s.  And they don't have the staff.  I ran (got suckered into running... didn't flee when asked if someone knew something about computers...) the website for a small parochial school for a while, and a parishoner was a co-founder of a mid-sized ISP here in SF.  Not tiny at all.  But we faced these issues constantly.  And this resulted in very angry parents & teachers - nothing worse than leaving a portion of parents in the dark on a schedule change...  I didn't have to ditch the host, but we did work together to migrate to gmail.  The hosts can coordinate this so you can keep the old domain on your emails (it doesn't say gmail).   Never had a problem again. 

At the very least - you should gain access to that main email account for yourself.  And look at it every morning to review the surprises in your inbox... 

One final note...  I suspect AOL isn't just AOL...  They got bought out by Verizon and are in a group called Oath that includes Yahoo and AOL.  And "Yahoo" includes all kinds of old email providers that were scooped up by Yahoo.  (E.g., my old pacbell.net email is actually Yahoo/ATT...)  If your "AOL" emails are getting blocked, it is likely that many more accounts are getting blocked. 

[shawnb61]

A theory... 

Do a lot of your users have the "Instantly - but only for the first unread reply" setting set?  (A notify_regularity of 1?)

Your folks are asking for a board-level notification... 

I wonder if - at the board level - that means they are getting one and only one email notification each?  For the entire board.
Then it stops? 

You can double-check by looking at someone who is not receiving emails.  Check the notify_regularity field on the member record, and the 'sent' field on the log_notify record.  I believe once that 'sent' field is set, no more emails are sent...

If that's the case, they need to change it from "Instantly - but only for the first unread reply" to "Instantly" (or "Daily, or "Weekly"...).  You would ALSO likely need to uncheck that 'sent' flag...


EDIT:  First step is to see if that's the problem... 
Does this query produce the list of folks *NOT*RECEIVING*ANNOUNCEMENTS*:

Code Select Expand

select b.id_board, b.name, m.id_member, m.member_name, m.email_address, m.notify_announcements, m.notify_regularity, m.notify_types, m.is_activated, ln.id_topic, ln.sent
from smf_log_notify AS ln, smf_members as m, smf_boards as b
where ln.id_member = m.id_member
and ln.id_board = b.id_board
and ln.id_board > 0
and m.notify_regularity = 1
and ln.sent = 1
order by m.member_name


[jdgretz]

Shawn -

That query certainly returns folks who are not getting the notifications (about 600) - as I am not in that list, but my gut is telling me that it is not the complete list. There is a 900 user delta between the results of this query and the total population of 1700.

All notifications are supposed to be at the board level (note I said supposed to be).

The first query you sent produces similar if not identical results.  I'm going to delve into a couple of accounts I know of folks there and see what their settings are.

Is there something in the documentation that explains this, or does one need to read the comments in the code to figure this stuff out?

For example -

most of the records returned have notify_announcements = "1", but a good percentage are "0"
                                                  notify_regularity = "1" but some are "0" and some are "3"
                                                  notify_types ="2" but some are "1"
   All of the records returned have is_active and sent = "1"

I'll let you know what I find out.

And again, thanks.

jdg

[shawnb61]

No.  Frankly I find most of it by trial & error... 

Some of those fields map to settings on my previous screenshot.

Notify_announcements is "Receive Forum Newsletters, announcements & notifications by mail".
  0 = The forum is not allowed to send me email
  1 = The forum is allowed to send me email

Notify_regularity is "For topics and boards I've requested notification on, notify me:".
  0 = Instantly
  1 = Instantly, but only for the first unread reply
  2 = Daily
  3 = Weekly

Notify_types is "For topics and boards I've requested notification on, notify me of:".
  1 = Replies and moderation
  2 = Moderation only if I started the topic
  3 = Only replies
  4 = Nothing at all

is_activated = 1 = whether the user is active

sent = 1 = Notification sent

============================================

I think the problem is in two parts here:
(1) If notify_announcements is set to 0, that means their profile disallows email.  That first checkbox isn't checked.
(2) If notify_regularity is set to 1, and sent set to 1, I don't think are getting another email for that board/topic. 

Given that this forum is setup specifically for folks to ask for notifications, I would be tempted to:
- Identify folks who have asked for board notifications (id_board matches that board # on log_notify)
- For those identified, set notify_announcements to 1 (email OK)
- For those identified, change notify_regularity from 1 (instant, but...) to either 2 (daily) or 0 (instantly)

And put an announcement on the board explaining what you did & why; show them they can change preferences under Profile | Modify Profile | Notifications.

[jdgretz]

Well, life has been interesting, to say the least...

I did a test mailing from the newly installed system and it sent 29 emails

22 of those had notify_regularity = 0
7 had notify_regularity = 1 and all but one had sent = 1  the remaining user had sent = 0

One user with notify_regularity = 0 did not receive an email.

I guess I need to hit the books to figure out how to write an update query to change al those with notify_announcements = 1 to have notify_regularity = 0 and see what happens.

Should I also change sent =0?

Thanks again - I owe you more than a beer.

John