• 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

Jake Bunce

XenForo moderator
Staff member
#6
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)
 
#7
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