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

XF 1.5 shared IPs

Discussion in 'XenForo Questions and Support' started by Hooligan, Sep 21, 2016.

  1. Hooligan

    Hooligan Member

    Is there a way to mass search for users with shared IPs? Like to bring up a list of all users with shared IPs and whatnot.
  2. Brogan

    Brogan XenForo Moderator Staff Member

  3. Hooligan

    Hooligan Member

    Yeah I don't want to search just one IP though.

    I want to mass search all of them and see all users with any matched IPs.
  4. Brogan

    Brogan XenForo Moderator Staff Member

    No, that's not possible via the UI.

    You would have to do it with a query directly in the DB.
    Hooligan likes this.
  5. Hooligan

    Hooligan Member

    Ah, okay, that's what I wanted to know. Thank you. :)

    ... except I'm a noob and I have no idea how to do that.
  6. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Try this query:

    SELECT u.user_id, u.username, INET_NTOA(CONV(HEX(ushared.ip), 16, 10)) AS 'ip', ushared.users
    FROM xf_user AS u
    INNER JOIN ( -- user ids that have shared ips
        SELECT DISTINCT i.user_id, shared.ip, shared.users
        FROM xf_ip AS i
        INNER JOIN ( -- ips shared among multiple users
            SELECT ip, COUNT(DISTINCT user_id) AS 'users'
            FROM xf_ip
            GROUP BY ip
        ) shared ON (shared.ip = i.ip AND shared.users > 1)
    ) ushared ON (ushared.user_id = u.user_id)
    Hooligan and eva2000 like this.
  7. Hooligan

    Hooligan Member

    Worked like a charm. Thank you. :D

Share This Page