Body: <p>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.</p>
  <p>I figured out how to enable double opt-in and got that working. But when researching the subscription logs I found that the <strong><a href="https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_subscription_history.html" rel="nofollow noreferrer">civicrm_subscription_history</a></strong> table has a tracking field with the description &quot;IP address or other tracking info,&quot; but instead of an IP address it contains the foreign key ID to either the <strong><a href="https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_mailing_event_subscribe.html" rel="nofollow noreferrer">civicrm_mailing_event_subscribe</a></strong> or <strong><a href="https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_mailing_event_unsubscribe.html" rel="nofollow noreferrer">civicrm_mailing_event_unsubscribe</a></strong> tables. Neither of these tables contains an IP address log.</p>
  <p>I've searched for the IP address in other tables with no luck.</p>
  <ul>
  <li>Does CiviCRM log the IP address from the subscription confirmation?
  <ul>
  <li>If so, where?</li>
  <li>If not, is there some way to enable it?</li>
  </ul>
  </li>
  </ul>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-10-26T09:16:02.430Z
Comments.Text: I'm afraid you have to implement this yourself. Have a look at
  https://docs.civicrm.org/dev/en/latest/hooks/hook_civicrm_post/
Comments.UserDisplayName: nielo
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-10-26T16:45:35.433Z
Comments.Text: 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.
Comments.UserDisplayName: BillC
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-10-26T20:49:02.027Z
Comments.Text: 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.
Comments.UserDisplayName: nielo
Tags: <civimail>
Title: Email subscription IP address tracking
ThreadText: # Email subscription IP address tracking
  
  (Asked by: username_0 on 2022-10-23)
  
  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](https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_subscription_history.html)** 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](https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_mailing_event_subscribe.html)** or **[civicrm\_mailing\_event\_unsubscribe](https://doc.symbiotic.coop/dev/civicrm/latest/schema/tables/civicrm_mailing_event_unsubscribe.html)** 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?
  
  **Tags:** civimail
  
  > **Comments:**
  > **username_1**: I'm afraid you have to implement this yourself. Have a look at https://docs.civicrm.org/dev/en/latest/hooks/hook_civicrm_post/  
  > **username_0**: 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.  
  > **username_1**: 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.  
  
  ---
  
  ## 0 answers
  
  