I know in the user admin we can see if there is another account on a IP address when you click the registered IP or last login IP, can we also see all users that have been matched with double IP’s instead of checking all my users one by one?

Here’s a data explorer query. It’d be better if it did a group by so that it could sort by most-recent-create-date or something like that, but maybe this’ll help. WITH users_per_ip AS ( SELECT count(1) AS user_count, u.registration_ip_address AS ip, max(u.created_at) last_create, min(u.created_at) first_create, (max(u.created_at) - min(u.created_at)) diff, case when (max(u.suspended_at) is not null or max(u.silenced_till) is not null ) then 1 else 0 end bad FROM users u GROUP BY ip ) SELECT u.id AS user_id, date_trunc('day',u.created_at)::date created, date_trunc('day',upi.diff) days, bad, upi.ip AS ip_address FROM users_per_ip upi JOIN users u ON u.registration_ip_address = upi.ip WHERE upi.user_count > 1 ORDER BY upi.last_create DESC

Scan IP matched accounts

Product Support

MarcP (MarcP) November 20, 2019, 7:27pm 3

This seems to be working fine for me, didn’t know about Data Explorer Plugin at all.. Thanks!

Topic		Replies	Views
Two users from same IP address Product Support	2	681	September 12, 2019
Need a IP duplication query Data & reporting sql-query	3	751	July 17, 2019
Add report: Duplicate IP address Feature	7	2112	August 13, 2020
Query regarding IP Lookup feature Product Support	3	826	October 20, 2021
Duplicate account icon for suspended users Feature	9	1665	August 27, 2017

Scan IP matched accounts

Related topics