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

Users sharing IP addresses

Discussion in 'XenForo Questions and Support' started by discoian, Sep 30, 2012.

  1. discoian

    discoian Active Member

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

    Jake Bunce XenForo Moderator Staff Member

    Admin CP -> Users -> Search for Users -> Search by IP Address
     
  3. ENF

    ENF Well-Known Member

    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.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
    Insy and ENF like this.
  5. discoian

    discoian Active Member

    Thanks for this, great work!
     
  6. Tom_

    Tom_ Member

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

    Jake Bunce XenForo Moderator Staff Member

    It should still work.
     
  8. BentDreams

    BentDreams Active Member

    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.
     
  9. Mike

    Mike XenForo Developer Staff Member

    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;
     
  10. BentDreams

    BentDreams Active Member

    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.
     
  11. Mike

    Mike XenForo Developer Staff Member

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

Share This Page