Kepiye Internet ngarep lan statistik server jeneng domain?

Router ngarep (ing kasus iki FritzBox) bisa ngrekam akeh: pinten lalu lintas arep nalika, sing disambungake ing kacepetan apa, etc. Server jeneng domain (DNS) ing jaringan lokal mbantu aku ngerteni apa sing didhelikake ing mburi panampa sing ora dingerteni.

Sakabèhé, DNS wis nduwe pengaruh positif ing jaringan asal: wis nambah kacepetan, stabilitas, lan ngatur.

Ing ngisor iki ana diagram sing nuwuhake pitakonan lan kudu ngerti apa sing kedadeyan. Asil wis nyaring panjalukan sing dikenal lan bisa digunakake kanggo server jeneng domain.

Napa 60 domain sing ora jelas dijajaki saben dina nalika kabeh wong isih turu?

Saben dina, 440 domain sing ora dingerteni dijajaki sajrone jam aktif. Sapa lan apa sing ditindakake?

Jumlah panjalukan rata-rata saben dina saben jam

pitakon laporan 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'))

Ing wayah wengi, akses nirkabel dipateni lan aktivitas piranti wis samesthine, i.e. ora ana polling kanggo domain sing ora dingerteni. Iki tegese kegiatan paling gedhe teka saka piranti karo sistem operasi kayata Android, iOS lan Blackberry OS.

Ayo dhaptar domain sing dipolling kanthi intensif. Intensitas bakal ditemtokake dening paramèter kayata jumlah panjaluk saben dina, jumlah dina kegiatan lan pirang-pirang jam dina sing diweruhi.

Kabeh tersangka sing dikarepake ana ing dhaptar.

Domain sing dijajaki kanthi intensif

pitakon laporan 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

We mblokir isс.blackberry.com lan iceberg.blackberry.com, kang Produsèn bakal mbecikake kanggo alasan keamanan. Asil: nalika nyoba nyambung menyang WLAN, nuduhake kaca mlebu lan ora nyambung menyang ngendi wae maneh. Ayo mbukak blokir.

detectportal.firefox.com minangka mekanisme sing padha, mung ditindakake ing browser Firefox. Yen sampeyan kudu mlebu menyang jaringan WLAN, mula bakal nuduhake kaca mlebu. Ora jelas kenapa alamat kasebut kudu asring dipindhah, nanging mekanisme kasebut diterangake kanthi jelas dening pabrikan.

skype. Tumindak program iki padha karo cacing: ndhelikake lan ora mung ngidini mateni ing taskbar, ngasilake akeh lalu lintas ing jaringan, ping 10 domain saben 4 menit. Nalika nggawe panggilan video, sambungan Internet terus-terusan rusak, nalika ora bisa luwih apik. Kanggo saiki iku perlu, supaya tetep.

upload.fp.measure.office.com - nuduhake Office 365, aku ora bisa nemokake katrangan sing prayoga.
browser.pipe.aria.microsoft.com - Aku ora bisa nemokake gambaran prayoga.
We mblokir loro.

connect.facebook.net - Aplikasi chatting Facebook. Sisa.

mediator.mail.ru Analisis kabeh panjalukan kanggo domain mail.ru nuduhake anane akeh sumber daya iklan lan kolektor statistik, sing nyebabake rasa ora percaya. Domain mail.ru dikirim menyang dhaptar ireng.

google-analytics.com - ora mengaruhi fungsi piranti, mula kita mblokir.
doubleclick.net - ngitung klik iklan. We mblokir.

Akeh panjaluk menyang googleapis.com. Pamblokiran wis mimpin kanggo mati bungah pesen singkat ing tablet, kang ketoke bodho kanggo kula. Nanging playstore mandheg, mula diblokir.

cloudflare.com - dheweke nulis yen dheweke seneng mbukak sumber lan, umume, nulis akeh babagan awake dhewe. Intensitas survey domain ora sakabehe jelas, sing asring luwih dhuwur tinimbang aktivitas nyata ing Internet. Ayo ditinggal saiki.

Mangkono, intensitas panjalukan asring ana gandhengane karo fungsi piranti sing dibutuhake. Nanging wong-wong sing overded karo kegiatan uga ditemokake.

Pisanan

Nalika Internet nirkabel diuripake, kabeh wong isih turu lan sampeyan bisa ndeleng panjalukan sing dikirim menyang jaringan luwih dhisik. Dadi, ing 6:50 Internet urip lan ing wektu sepuluh menit pisanan 60 domain polling saben dina:

pitakon laporan 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 mriksa sambungan WLAN manawa ana kaca mlebu.
Citrix ping servere sanajan aplikasi kasebut ora aktif mlaku.
Symantec verifikasi sertifikat.
Mozilla mriksa nganyari, sanajan ing setelan aku njaluk supaya ora nindakake iki.

mmo.de minangka layanan game. Paling kamungkinan, panjalukan kasebut diwiwiti kanthi obrolan facebook. We mblokir.

Apple bakal ngaktifake kabeh layanan. api-glb-fra.smoot.apple.com - miturut katrangan, saben klik tombol dikirim ing kene kanggo tujuan optimasi mesin telusur. Curiga banget, nanging ana hubungane karo fungsi. We ninggalake.

Ing ngisor iki minangka dhaptar panjaluk sing dawa menyang microsoft.com. Kita mblokir kabeh domain wiwit saka tingkat katelu.

Jumlah subdomain pisanan


Dadi, 10 menit pisanan nguripake Internet nirkabel.
Jajak pendapat iOS paling akeh subdomain - 32. Diterusake dening Android - 24, banjur Windows - 15 lan pungkasane Blackberry - 9.
Aplikasi facebook mung polling 10 domain, skype polling 9 domain.

Sumber informasi

Sumber kanggo analisis yaiku file log server lokal bind9, sing ngemot format ing ngisor iki:

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)

File kasebut diimpor menyang database sqlite lan dianalisis nggunakake pitakon SQL.
Server tumindak minangka cache; panjalukan teka saka router, dadi tansah ana siji panjalukan klien. Struktur tabel sing disederhanakake cukup, yaiku. Laporan kasebut mbutuhake wektu panyuwunan, panjaluk kasebut dhewe, lan domain tingkat kapindho kanggo ngelompokake.

Tabel DDL

CREATE TABLE STG_BIND9_LOG (
  LINE_NK       INTEGER NOT NULL DEFAULT 1,
  DATE_NK       TEXT NOT NULL DEFAULT 'n.a.',
  TIME_NK       TEXT NOT NULL DEFAULT 'n.a.',
  CLI           TEXT, -- client
  IP            TEXT,
  REQUEST_NK    TEXT NOT NULL DEFAULT 'n.a.', -- requested domain
  DOMAIN        TEXT NOT NULL DEFAULT 'n.a.', -- domain second level
  QUERY         TEXT,
  UNIQUE (LINE_NK, DATE_NK, TIME_NK, REQUEST_NK)
);

kesimpulan

Mangkono, minangka asil analisis log server jeneng domain, luwih saka 50 cathetan disensor lan diselehake ing dhaptar blokir.

Keperluan sawetara pitakon uga diterangake dening produsen piranti lunak lan menehi inspirasi. Nanging, akeh kegiatan kasebut ora ana dhasar lan bisa dipertanyakan.

Source: www.habr.com