IP Database handling

[QuackieMackie]

I have a few questions regarding how IP addresses are handled in the database.

First, is there an existing procedure or script that allows me to search for all occurrences of a specific IP address across the entire XenForo database? Even better, is there a way to replace a specified IP address with a dummy value?

Also, I came across this thread: IP Address Concerns where @duderuud mentions you can schedule IP deletion. Could someone please point me to where this option is available or how to set it up?

Finally, is there a list or something if the first one doesn't exist, that tells me which tables contain ips?


EDIT: I just realised I posted this in the wrong section. it should've been in https://xenforo.com/community/forums/xenforo-questions-and-support.25/ I believe.

 

[QuackieMackie]

In Setup -> Options -> Logging options you have the options to change how long logs are kept.

IPs are stored in these tables in the database:

• xf_session_activity
• xf_cookie_consent_log
• xf_ip
• xf_login_attempt
• xf_error_log
• xf_admin_log

Clean up options
This options clean up these tables:
Cookie consent log length - xf_cookie_consent_log
Delete IP usage data after: - xf_ip

As for these tables:
xf_login_attempt - This table is hard coded to automatically clean itself up. So these logs are only kept for 24 hours. This method runs when the hourly clean up cron job runs.
xf_error_log - This has no automatic clean up what so ever. It will keep these logs indefinitely. To clear these logs go into Logs -> Sever error log and click the clear button.
xf_admin_log - in your site config.php you need to add this option $config['adminLogLength'] = NumberOfDaysToKeepLogs; By default this is 60 days.
xf_session_activity - This is by default hard coded to keep the logs for an hour before it prunes them. But can be overiden using the config.php file but adding this option $config['sessionActivityExpiration'] = 3600; <- This value is how many seconds.

 

[Jeremy P]

The xf_ip table is the central place. The option you noted will prune the records after a period of time. The activity log option is for something else (it logs activity to power trending content) which does not contain IP records. The other records are removed by the logging option values, or periodically through cron.

 

[QuackieMackie]

The activity log option is for something else (it logs activity to power trending content) which does not contain IP records.

Sorry I'm a little bit confused, about what you meant by this. The xf_session_activity table does contain IP's.

 

[Jeremy P]

Right, but this option is for the xf_content_activity_log table, which does not store IPs:



The records in xf_session_activity are pruned via cron every hour.

 

[QuackieMackie]

Right, but this option is for the xf_content_activity_log table, which does not store IPs:

View attachment 324562

The records in xf_session_activity are pruned via cron every hour.

Ah okay thank's for explaining what you meant.

 

[Sim]

is there an existing procedure or script that allows me to search for all occurrences of a specific IP address across the entire XenForo database?

Just in case it's useful, here is the query I run to identify all IP addresses used by a specific user or group of users:

SELECT ip.user_id,
  user.username,
  ip.content_type,
  ip.content_id,
  ip.action,
  INET6_NTOA(ip.ip) AS ip_text,
  ip.log_date,
  CONVERT_TZ(FROM_UNIXTIME(log_date), 'UTC', 'Australia/Sydney') AS log_date_sydney

FROM xf_ip AS ip

LEFT JOIN xf_user AS user ON (ip.user_id = user.user_id)

WHERE ip.user_id IN (1234, 4567)

ORDER BY ip.log_date DESC

... obviously you'd want to change your timezone and change the list of user_ids to suit your needs.