# Gescannte IP-abgestimmte Konten

**URL:** <https://meta.discourse.org/t/scan-ip-matched-accounts/133930>\
**Category:** Product Support\
**Created:** [20. November 2019 um 16:14 UTC](https://meta.discourse.org/t/scan-ip-matched-accounts/133930 "2019-11-20T16:14:03Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\
**Post date:** [20. November 2019 um 18:36 UTC](https://meta.discourse.org/t/scan-ip-matched-accounts/133930/2 "2019-11-20T18:36:54Z")

</div>

Hier ist eine Abfrage für den Datenexplorer. Es wäre besser, wenn sie eine GROUP BY-Operation durchführen würde, um nach dem neuesten Erstellungsdatum oder ähnlichem zu sortieren, aber vielleicht hilft das schon.

```sql
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

```

---

_[View the full topic](https://meta.discourse.org/t/scan-ip-matched-accounts/133930)._
