XF 1.5 shared IPs

[Hooligan]

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.

 

[Paul B]

 

[Hooligan]

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.

 

[Paul B]

No, that's not possible via the UI.

You would have to do it with a query directly in the DB.

 

[Hooligan]

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.

 

[Jake Bunce]

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]

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)

Worked like a charm. Thank you.