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.
 

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