Hello, i searched around the forum, github and google but i was unable to find a #plugin:data-explorer query for searching for duplicated IP adresses. Anyone can share one with me?
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
Thanks, it works perfectly! What i need to change if i want to get the last used IPs instead of the ones used at registration?
ip_address instead of
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.