Users sharing IP addresses

[discoian]

Is there a way, or maybe even a mod that will show me users who share IP addresses?

 

[Jake Bunce]

Admin CP -> Users -> Search for Users -> Search by IP Address

 

[ENF]

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]

A custom script is required for more complete reporting, but this query may be sufficient:

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:



edit... actually this may be more useful:

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.

 

[discoian]

Thanks for this, great work!

 

[Tom_]

@Jake Bunce SQL query is outdated? Can you provide new one?

 

[Jake Bunce]

@Jake Bunce SQL query is outdated? Can you provide new one?

It should still work.

 

[BentDreams]

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]

You can try:

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]

You can try:

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]

You may need to modify the query to fit your needs then. I just adapted it to handle the new IP storage format.