扫描匹配IP的账户

支持
1

我知道在用户管理界面中，点击注册 IP 或最后登录 IP 时，可以查看该 IP 地址下是否有其他账户。我们能否直接查看所有匹配到重复 IP 的用户，而不是逐个检查所有用户？

2

这是一个数据探索器查询。如果能按分组进行排序，比如按最新创建日期或类似条件排序会更好，但也许这会有所帮助。

WITH users_per_ip AS (
SELECT
COUNT(1) AS user_count,
u.registration_ip_address AS ip,
MAX(u.created_at) AS last_create,
MIN(u.created_at) AS first_create,
(MAX(u.created_at) - MIN(u.created_at)) AS diff,
CASE WHEN (MAX(u.suspended_at) IS NOT NULL 
      OR MAX(u.silenced_till) IS NOT NULL)
      THEN 1 ELSE 0 END AS bad
FROM users u
GROUP BY ip
)

SELECT
u.id AS user_id,
DATE_TRUNC('day', u.created_at)::DATE AS created,
DATE_TRUNC('day', upi.diff) AS 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
3

这对我来说似乎运行正常，我之前完全不知道 Data Explorer Plugin 这个插件……谢谢！