XF 1.5 The xf_ip table

[Wildcat Media]

I've noticed that our xf_ip table has grown to 7.0GB in size. Doing a few queries, it seems our first entries go back to January 6, 2002, which is when the original vBulletin forum was started up. The table has 97,610,227 rows in it.

Aside from registration IP addresses, is there any reason we should be keeping history back this far? One use for IP address history is watching for banned members signing up again, as well as watching for spam activity. Beyond that, I'd think that IP addresses probably aren't accurate beyond a certain point, like five or ten years. I was with my cable Internet provider since 2014 but changed to fiber late last year, but I'm sure I am an outlier in keeping Internet service that long.

Would it be safe to clear out older data from the table, but leaving the registration data? The action column would be my criteria, as action = "register" when a member registers. Other than that, is there any reason to keep the data for this long?

 

[Paul B]

It's largely pointless data, especially if many visitors use dynamic allocation, hotspots, public wi-fi, etc.

I would clear most of it out and set the option to auto delete after 365 days, or sooner.

 

[Wildcat Media]

I may do that after we upgrade to 2.2. I don't think the option is available in 1.5, unless I missed it.

Does registration IP data still remain for member profiles? I don't see a column for it. Not a big deal, just curious.

 

[Paul B]

It's been a long time since I used XF1 so you may be right.
You can just truncate the table or delete any rows older than x days.

I'm, not sure what you mean by "Does registration IP data still remain for member profiles".
IP addresses are logged on registration, log in, etc. but if you select the option to delete after x days, all records are deleted.

 

[Wildcat Media]

I was poking around to see if the registration IP address was saved somewhere other than the IP table, but apparently it's not. Not a big deal though--I'll clear that table of all the old stuff and move on. It was more a curiosity than anything else--seeing how some of our members first joined us from 20+ years ago.

 

[MySiteGuy]

I may do that after we upgrade to 2.2. I don't think the option is available in 1.5, unless I missed it.

Does registration IP data still remain for member profiles? I don't see a column for it. Not a big deal, just curious.

Admin Panel -> Home Tab -> Options -> Logging Options

 

[Chris D]

The relevance of IP records across a long period of time in 2023 is next to zero.

Sure, if you catch someone within a few days sharing an IP address it could be the same person, but even then it isn’t guaranteed. But after a year? All bets are off.

I believe @NixFifty and I were at one point using the same IP address within a few days of each other because of Apple’s Private Relay.

For the amount of use they are, you could probably get away with storing only the last month’s worth of IP data before pruning, but I expect the default will stay at a year.

 

[digitalpoint]

The IP records can be useful for longer periods of time as long as you don’t care about being too granular with the IP itself. I keep the IP records for long periods of time but only for geotargetting the country. IP blocks rarely switch countries even over long periods of time.

As an example say you have a user that for the last 10 years has always been in the US, then they start logging in from North Korea. You could have a mechanism that sends them to moderation queue for review.

A tab on member profiles (that only staff can see):



That being said, I’ve been tossing around the idea of storing the country at the time of the IP usage rather than geotargetting it after the fact.

 

[Wildcat Media]

For the amount of use they are, you could probably get away with storing only the last month’s worth of IP data before pruning, but I expect the default will stay at a year.

We actually have caught banned members returning under the same IP address after a handful of years. I can't think of anything past five years though, so I'll start with that as our cutoff for now.

It's not uncommon for us to find someone who has repeatedly tried to rejoin and have a two to three year old IP address, and we can often tell by the very similar email address or a throwaway account somewhere. This is often in tandem with a history of a banned member returning a couple of times and having posted without being caught, and all have had shared IP addresses. Banned members often try very soon after being kicked out, but they'll often lay back months or a year or two before attempting it again. It's just one additional bit of proof we use with others to determine if it's a banned member.

We do have a very busy forum, though--over 32 million posts now, and attracting a different crowd than we used to, so the staff is always busy cleaning up messes. And there are many more bannings than we used to have in, say, 2006-2008, where we'd maybe have one or two a year.

 

[Wildcat Media]

Admin Panel -> Home Tab -> Options -> Logging Options

And there it is. Thanks!

 

[Jake B.]

That being said, I’ve been tossing around the idea of storing the country at the time of the IP usage rather than geotargetting it after the fact.

Cloudflare passes along a CF-IPCountry header which will make this easier as well, without the need to use any sort of geo IP databases / services yourself

 

[digitalpoint]

Cloudflare passes along a CF-IPCountry header which will make this easier as well, without the need to use any sort of geo IP databases / services yourself

Yep, it's how I'm doing it for things that aren't in-house only. For example this addon shows the country where users are logging in from and using passkeys from: https://xenforo.com/community/resources/digitalpoint-security-passkeys.8738/

FWIW, you can also get more granular with Cloudflare (region, city longitude, latitude, as well if you want). You can do it on all requests or just some. For example I have a mechanism where a two-step authentication request will present a map and granular location of where the login request came from and the user can just click "Yes it's me" on their end for the two-step process.