• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Users sharing IP addresses

ENF

Well-known member
#3
Admin CP -> Users -> Search for Users -> Search by IP Address
That doesn't automatically show IP addresses with more than one user.

If you know the IP address to check, then that works. Having an automated query or detective control for users with the same IP would be fairly beneficial. I know we rely on a vB mod for detecting users with duplicate accounts and multiple accounts from one IP source.
 

Jake Bunce

XenForo moderator
Staff member
#4
A custom script is required for more complete reporting, but this query may be sufficient:

Code:
SELECT INET_NTOA(i.ip) AS ipAddress, COUNT(*) AS numUsers, u.username AS oneUsername
FROM xf_user AS u
LEFT JOIN xf_ip AS i ON (i.user_id = u.user_id AND i.action = 'register')
GROUP BY ipAddress;
You get data like this:

Screen shot 2012-10-01 at 12.21.56 AM.png

edit... actually this may be more useful:

Code:
SELECT INET_NTOA(i.ip) AS ipAddress, COUNT(*) AS numUsers, u.username AS oneUsername
FROM xf_user AS u
LEFT JOIN xf_ip AS i ON (i.user_id = u.user_id AND i.action = 'register')
GROUP BY i.ip
ORDER BY numUsers
DESC;
That will list the highest number of matching IPs first.
 

BentDreams

Active member
#8
It should still work.
I ran the most recent version of this query on 1.4.7 and it reports an ip of 0.0.0.0 for all users.

I spot checked a few users and none of them report an IP of 0.0.0.0 in their user data in the ACP.

If you could provide an update that would be appreciated.
 

Mike

XenForo developer
Staff member
#9
You can try:
Code:
SELECT INET_NTOA(CONV(HEX(i.ip), 16, 10)) AS ipAddress, COUNT(*) AS numUsers, u.username AS oneUsername
FROM xf_user AS u
LEFT JOIN xf_ip AS i ON (i.user_id = u.user_id AND i.action = 'register')
GROUP BY i.ip
ORDER BY numUsers DESC;
 

BentDreams

Active member
#10
You can try:
Code:
SELECT INET_NTOA(CONV(HEX(i.ip), 16, 10)) AS ipAddress, COUNT(*) AS numUsers, u.username AS oneUsername
FROM xf_user AS u
LEFT JOIN xf_ip AS i ON (i.user_id = u.user_id AND i.action = 'register')
GROUP BY i.ip
ORDER BY numUsers DESC;
That is closer. It now shows full IP addresses but only returns about 55% of my users, none of which are my 4 accounts which should all have the same IP.
 

Mike

XenForo developer
Staff member
#11
You may need to modify the query to fit your needs then. I just adapted it to handle the new IP storage format.