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.
 
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)
 
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
 
Back
Top Bottom