Как найти дубликаты IP адресов?

Question

[Сергей]

Здравствуйте, подскажите, есть база одного из действующих ресурсов, требуется найти дубликаты учеток, зарегистрированных с одного IP.
Составил такой запрос:

SELECT member_id, name as one_from_logins,ip_address, count(ip_address) as logins_by_ip from members where GROUP by ip_address having logins_by_ip > 1 ORDER BY member_id asc

Получил один из логинов (кстати, как вывести все логины для сгруппированных IP?), собственно сам IP и количество логинов, зарегистрированных с одного IP. Но есть один минус - дубликаты выводятся только для полностью совпадающих IP и учетки, зарегистрированные с IP отличающихся на 1-2 октета (3 и 4 октет) не видны.
Собственно вопрос, как можно сделать (если можно - с примером) поиск дубликатов для полностью совпадающих IP, а также отличающихся на 3 и 4 октет?

Заранее спасибо

Answer 1

[AlexeyVD]

Для поиска и вывода дубликатов можете попробовать следующее:

SELECT *
  FROM members m
  JOIN (SELECT *
          FROM members
          WHERE logins_by_ip > 1
          GROUP BY ip_address
          HAVING COUNT(1) > 1) AS sel ON m.member_id = sel.member_id
  ORDER BY m.ip_address

Тут мы в подзапросе ищем ip, которые дублируются в нескольких учетках, а затем выводим эти учетки, отсортировав по ip.
Для поиска по отличающимся октетам группируйте и сортируйте по:
SUBSTRING_INDEX(ip_address, '.', 2)
или
SUBSTRING_INDEX(ip_address, '.', 3)

Comments

[Сергей]

С полностью одинаковыми я сделал так:

SELECT GROUP_CONCAT(DISTINCT member_id) as memid,GROUP_CONCAT(DISTINCT name) as one_from_logins,ip_address,count(ip_address) as logins_for_ip from comp_members where GROUP by ip_address having logins_for_ip > 1 ORDER BY logins_for_ip DESC

Вроде работает, данных не особо много получилось, поэтому не должен в лимит упереться.
С SUBSTRING_INDEX интересно, но честно говоря не понял куда именно его...

[AlexeyVD]

Вместо GROUP BY ip_address поставьте GROUP BY SUBSTRING_INDEX(ip_address, '.', 3). Тогда в итоге данные сгруппируются не по конкретному ip, а по первым 3 октетам. Например, сгруппирует все ip, начинающиеся с 192.168.1

[Сергей]

AlexeyVD: о, круто, спасибо.

Answer 2

[Dimonchik]

оператор LIKE

ну и freelansim.ru

Comments

[Сергей]

Like мне бы помог, если бы сравнивал с каким-либо конкретным IP, а тут можно считать количество не ограниченным.

Answer 3

[Иван]

Я бы вообще выводил во что-то экселеобразное и там разбирал. Не та задача. Или вы хотите эти данные каждому юзеру на сайте выводить?

Comments

[Сергей]

Есть некий проект, где дубликаты учеток дают преимущество перед другими пользователями.. Способ поиска дубликатов есть, но оно обходитя. Вот хочу попытаться побороться с теми, кто все же сделал дубликат.

Answer 4

[Ruslan]

Для полностью одинаковых ip

SELECT ip_address, GROUP_CONCAT(name SEPARATOR ', ') AS name from members group by ip_address

Comments

[AlexeyVD]

Не забывайте, что максимальная длина вывода для group_concat по-умолчанию ограничена 1024 символами, так что есть шанс потерять данные.