My webhost (Dreamhost) has recently been blocking our mass mailing as spam and they say in order to comply with their rules, and remain unblocked, we need to provide proof that we use double opt-in and share the logs that show subscriptions with a timestamp and IP address.

I figured out how to enable double opt-in and got that working. But when researching the subscription logs I found that the civicrm_subscription_history table has a tracking field with the description "IP address or other tracking info," but instead of an IP address it contains the foreign key ID to either the civicrm_mailing_event_subscribe or civicrm_mailing_event_unsubscribe tables. Neither of these tables contains an IP address log.

I've searched for the IP address in other tables with no luck.

  • Does CiviCRM log the IP address from the subscription confirmation?
    • If so, where?
    • If not, is there some way to enable it?
  • I'm afraid you have to implement this yourself. Have a look at docs.civicrm.org/dev/en/latest/hooks/hook_civicrm_post
    – nielo
    Commented Oct 26, 2022 at 9:16
  • That's what I ended up doing, although not using hooks. I added a Wordpress plugin that captures the IP addresses of all visitors and logs the URL. Then I wrote MySQL views to filter on the confirmation URL, parse it, and join it to the civicrm_mailing_event_subscribe table. Then a nightly cron job runs a MySQL procedure to insert new records into an archive table (since I'm only keeping 30 days of IP traffic but want to keep the confirmation logs indefinitely). Although... it would be nice if CiviCRM could do this natively for those users who don't know PHP or MySQL.
    – BillC
    Commented Oct 26, 2022 at 16:45
  • Nice that you have reached your goals. And your're right. In this times absolutely necessary for mass mailers. I suggest extending core civimail and pass the vore team a pull request.
    – nielo
    Commented Oct 26, 2022 at 20:49


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.