# Need a IP duplication query
**URL:** https://meta.discourse.org/t/need-a-ip-duplication-query/123229
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [July 17, 2019, 7:09pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229 "2019-07-17T19:09:43Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@ThorY](https://meta.discourse.org/u/ThorY)
#### Post date: [July 17, 2019, 7:09pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229/1 "2019-07-17T19:09:43Z")
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?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [July 17, 2019, 7:14pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229/2 "2019-07-17T19:14:46Z")
```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
```
---
### Author:  [@ThorY](https://meta.discourse.org/u/ThorY)
#### Post date: [July 17, 2019, 7:22pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229/3 "2019-07-17T19:22:11Z")
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?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [July 17, 2019, 7:25pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229/4 "2019-07-17T19:25:06Z")
`ip_address` instead of `registration_ip_address`.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [August 16, 2019, 7:25pm UTC](https://meta.discourse.org/t/need-a-ip-duplication-query/123229/5 "2019-08-16T19:25:17Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.