XF 1.5 shared IPs

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.
 

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.
 

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

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.
 

Jake Bunce

XenForo moderator
Staff member
Try this query:

Code:
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

Member
Try this query:

Code:
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. :D
 
Top