Чем живёт домашний интернет и статистика сервера доменных имён

в 11:25, , рубрики: DNS, sql, sqlite, веб-аналитика

Домашний роутер (в данном случае FritzBox) умеет многое регистрировать: сколько трафика когда ходит, кто с какой скоростью подключён и т.п. Узнать, что скрывается под непонятными адресатами, мне помог сервер доменных имён (DNS) в локальной сети.

В целом, DNS оказал положительное влияние на домашнюю сеть: добавил скорость, устойчивость и управляемость.

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

По какой причине каждый день опрашиваются 60 непонятных доменов во время, когда все ещё спят?

Каждый день опрашиваются 440 неизвестных доменов в активное время. Кто это такие и что они делают?

Среднее количество запросов в день по часам

image

SQL запрос отчёта

WITH CLS AS ( /* prepare unique requests */
SELECT
DISTINCT DATE_NK,
STRFTIME( '%s', SUBSTR(DATE_NK,8,4) || '-' ||
	CASE SUBSTR(DATE_NK,4,3)
	WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
	WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11'
	ELSE '12' END || '-' || SUBSTR(DATE_NK,1,2) || ' ' || SUBSTR(TIME_NK,1,8) ) AS EVENT_DT,
REQUEST_NK, DOMAIN
FROM STG_BIND9_LOG )
SELECT
  1 as 'Line: DNS Requests per Day for Hours',
  strftime('%H:00', datetime(EVENT_DT, 'unixepoch')) AS 'Day',
  ROUND(1.0*SUM(1)/COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))), 1) AS 'Requests per Day'
FROM CLS
WHERE DOMAIN NOT IN ('in-addr.arpa', 'IN-ADDR.ARPA', 'local', 'dyndns', 'nas', 'ntp.org')
  AND datetime(EVENT_DT, 'unixepoch') > date('now', '-20 days')
GROUP BY /* hour aggregate */
  strftime('%H:00', datetime(EVENT_DT, 'unixepoch'))
ORDER BY strftime('%H:00', datetime(EVENT_DT, 'unixepoch'))

В ночное время беспроводный доступ отключается и активность устройст ожидаема, т.е. нет опроса непонятных доменов. Значит, наибольшая активность исходит от устройств с такими операционными системами, как Android, iOS и Blackberry OS.

Выведем домены, которые интенсивно опрашиваются. Интенсивность будут определять такие параметры, как количество запросов в день, количество дней активности и в скольких часах дня были замечены.

В списке оказались все ожидаемые подозреваемые.

Интенсивно опрашиваемые домены

image

SQL запрос отчёта

WITH CLS AS ( /* prepare unique requests */
SELECT
DISTINCT DATE_NK,
STRFTIME( '%s', SUBSTR(DATE_NK,8,4) || '-' ||
	CASE SUBSTR(DATE_NK,4,3)
	WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
	WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11'
	ELSE '12' END || '-' || SUBSTR(DATE_NK,1,2) || ' ' || SUBSTR(TIME_NK,1,8) ) AS EVENT_DT,
REQUEST_NK, DOMAIN
FROM STG_BIND9_LOG )
SELECT 
  1 as 'Table: Havy DNS Requests',
  REQUEST_NK AS 'Request',
  DOMAIN AS 'Domain',
  REQ AS 'Requests per Day',
  DH AS 'Hours per Day',
  DAYS AS 'Active Days'
FROM (
SELECT
  REQUEST_NK, MAX(DOMAIN) AS DOMAIN,
  COUNT(DISTINCT REQUEST_NK) AS SUBD,
  COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))) AS DAYS,
  ROUND(1.0*SUM(1)/COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))), 1) AS REQ,
  ROUND(1.0*COUNT(DISTINCT strftime('%d.%m %H', datetime(EVENT_DT, 'unixepoch')))/COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))), 1) AS DH
FROM CLS
WHERE DOMAIN NOT IN ('in-addr.arpa', 'IN-ADDR.ARPA', 'local', 'dyndns', 'nas', 'ntp.org')
  AND datetime(EVENT_DT, 'unixepoch') > date('now', '-20 days')
GROUP BY REQUEST_NK )
WHERE DAYS > 9 -- long period
ORDER BY 4 DESC, 5 DESC
LIMIT 20

Блокируем iсс.blackberry.com и iceberg.blackberry.com, которые производитель обоснует соображениями безопасности. Результат: при попытке подключения к WLAN, показывает страницу логина и больше никогда никуда не подключается. Разблокируем.

detectportal.firefox.com — это такой же механизм, только реализован в браузере Firefox. В случае необходимости логиниться в сеть WLAN, покажет сначала страницу логина. Не совсем понятно, зачем так часто пинговать адрес, но механизм доступно описан у производителя.

skype. Действия этой программы похожи на червя: скрывается и не даёт просто себя убить в панеле задач, в сети генерирует много трафика, каждые 10 минут пингует 4 домена. При видео-звонке постоянно мобит интернет-соединение, когда оно уже лучше не может быть. Пока является необходимым, поэтому остаётся.

upload.fp.measure.office.com — относится к Office 365, приличного описания не нашёл.
browser.pipe.aria.microsoft.com — приличного описания не нашёл.
Оба блокируем.

connect.facebook.net — чат-приложение facebook. Остаётся.

mediator.mail.ru Анализ всех запросов домена mail.ru показал наличие огромного количества рекламных ресурсов и сборщиков статистики, что вызывает недоверие. Домен mail.ru отправляется целиком в чёрный список.

google-analytics.com — на функциональность устройств не влияет, поэтому блокируем.
doubleclick.net — считает рекламные нажатия. Блокируем.

Много запросов идёт к googleapis.com. Блокирование привело к радостному отключению коротких сообщений в планшете, которые мне кажутся дурацкими. Но перестал работать playstore, поэтому разблокируем.

cloudflare.com — пишут, что любят открытый код и, вообще, много о себе пишут. Не совсем понятна интенсивность опроса домена, которая часто сильно выше самой активности в интернете. Пока оставим.

Таким образом, интенсивность запросов часто связана с необходимой функциональностью устройств. Но были обнаружены и те, кто перестарался с активностью.

Самые первые

В момент включения беспроводного соединения все ещё спят и есть возможность увидеть, какие запросы первыми отправляются в сеть. Итак, в период с 6:50 до 6:59 ежедневно опрашиваются 60 доменов:

image

SQL запрос отчёта

WITH CLS AS ( /* prepare unique requests */
SELECT
DISTINCT DATE_NK,
STRFTIME( '%s', SUBSTR(DATE_NK,8,4) || '-' ||
	CASE SUBSTR(DATE_NK,4,3)
	WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
	WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11'
	ELSE '12' END || '-' || SUBSTR(DATE_NK,1,2) || ' ' || SUBSTR(TIME_NK,1,8) ) AS EVENT_DT,
REQUEST_NK, DOMAIN
FROM STG_BIND9_LOG )
SELECT
  1 as 'Table: First DNS Requests at 06:00',
  REQUEST_NK AS 'Request',
  DOMAIN AS 'Domain',
  REQ AS 'Requests',
  DAYS AS 'Active Days',
  strftime('%H:%M', datetime(MIN_DT, 'unixepoch')) AS 'First Ping',
  strftime('%H:%M', datetime(MAX_DT, 'unixepoch')) AS 'Last Ping'
FROM (
SELECT
  REQUEST_NK, MAX(DOMAIN) AS DOMAIN,
  MIN(EVENT_DT) AS MIN_DT,
  MAX(EVENT_DT) AS MAX_DT,
  COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))) AS DAYS,
  ROUND(1.0*SUM(1)/COUNT(DISTINCT strftime('%d.%m', datetime(EVENT_DT, 'unixepoch'))), 1) AS REQ
FROM CLS
WHERE DOMAIN NOT IN ('in-addr.arpa', 'IN-ADDR.ARPA', 'local', 'dyndns', 'nas', 'ntp.org')
  AND datetime(EVENT_DT, 'unixepoch') > date('now', '-20 days')
  AND strftime('%H', datetime(EVENT_DT, 'unixepoch')) = strftime('%H', '2019-08-01 06:50:00')
GROUP BY REQUEST_NK
 )
WHERE DAYS > 3 -- at least 4 days activity
ORDER BY 5 DESC, 4 DESC

Firefox проверяет WLAN соединение на наличие страницы с логином.
Citrix пингует свой сервер, хотя приложение активно не запущено.
Symantec проверяет сертификаты.
Mozilla проверяет обновления, хотя в настройках просил не делать этого.

mmo.de — игровой сервис. Скорее всего запрос инициирует чат facebook. Блокируем.

Apple активирует все свои сервисы. api-glb-fra.smoot.apple.com — судя по описанию, сюда отправляется каждое нажатие кнопки в целях поисковой оптимизации. Сильно подозрительно, но связано с функциональностью. Оставляем.

Далее идёт длинный список обращений к microsoft.com. Все домены, начиная с третьего уровня, блокируем.

Источник информации

Источником для анализа послужил лог файл локального сервера bind9, который содержит следующий формат:

01-Aug-2019 20:03:30.996 client 192.168.0.2#40693 (api.aps.skype.com): query: api.aps.skype.com IN A + (192.168.0.102)

Файл был импортирован в базу данных sqlite и проанализирован с помощью SQL запросов.

Вывод

Таким образом, в результате анализа лога сервера доменных имён более 50 записей были подвергнуты цензуре и помещены в список для блокировки.

Необходимость некоторых запросов хорошо описана производителями программного обеспечения и внушает доверие. Однако, большая часть активности необоснована и вызывает сомнение.

Автор: Андрей Марченко

Источник


* - обязательные к заполнению поля