Bawo ni Intanẹẹti ile ṣe n gbe ati awọn iṣiro olupin orukọ ìkápá?

Olutọpa ile kan (ninu ọran yii FritzBox) le ṣe igbasilẹ pupọ: melo ni ijabọ n lọ nigbati, tani o sopọ ni iyara wo, ati bẹbẹ lọ. Olupin orukọ ìkápá kan (DNS) lori nẹtiwọọki agbegbe ṣe iranlọwọ fun mi lati wa ohun ti o farapamọ lẹhin awọn olugba aimọ.

Ni apapọ, DNS ti ni ipa rere lori nẹtiwọọki ile: o ti ṣafikun iyara, iduroṣinṣin, ati iṣakoso.

Ni isalẹ ni aworan atọka ti o gbe awọn ibeere dide ati iwulo lati loye ohun ti n ṣẹlẹ. Awọn abajade tẹlẹ ṣe àlẹmọ ti a mọ ati awọn ibeere iṣẹ si awọn olupin orukọ ìkápá.

Kini idi ti awọn ibugbe 60 ti ko ni aabo lojoojumọ lakoko ti gbogbo eniyan tun sùn?

Lojoojumọ, awọn ibugbe aimọ 440 ti wa ni ibo lakoko awọn wakati ti nṣiṣe lọwọ. Tani wọn ati kini wọn ṣe?

Nọmba apapọ awọn ibeere fun ọjọ kan nipasẹ wakati

Ibeere ijabọ 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'))

Ni alẹ, iraye si alailowaya jẹ alaabo ati iṣẹ ẹrọ ni a nireti, ie. ko si idibo fun awọn ibugbe aimọ. Eyi tumọ si pe iṣẹ ṣiṣe ti o tobi julọ wa lati awọn ẹrọ pẹlu awọn ọna ṣiṣe bii Android, iOS ati Blackberry OS.

Jẹ ki a ṣe atokọ awọn agbegbe ti o ti didi lekoko. Kikan naa yoo jẹ ipinnu nipasẹ awọn ayeraye gẹgẹbi nọmba awọn ibeere fun ọjọ kan, nọmba awọn ọjọ iṣẹ ṣiṣe ati ni awọn wakati melo ti ọjọ ti wọn ṣe akiyesi.

Gbogbo awọn ifura ti o nireti wa lori atokọ naa.

Awọn ibugbe didi lekoko

Ibeere ijabọ 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

A ṣe idiwọ isс.blackberry.com ati iceberg.blackberry.com, eyiti olupese yoo ṣe idalare fun awọn idi aabo. Esi: nigba ti o ba n gbiyanju lati sopọ si WLAN, o fihan oju-iwe iwọle ko si tun sopọ mọ nibikibi mọ. Jẹ ki a sina rẹ.

detectportal.firefox.com jẹ ilana kanna, ti a ṣe nikan ni ẹrọ aṣawakiri Firefox. Ti o ba nilo lati wọle si nẹtiwọki WLAN, yoo kọkọ fi oju-iwe wiwọle han. Ko ṣe kedere idi ti adirẹsi yẹ ki o wa ni pinged nigbagbogbo, ṣugbọn ẹrọ naa jẹ apejuwe ni kedere nipasẹ olupese.

skype. Awọn iṣe ti eto yii jẹ iru si alajerun: o tọju ati pe ko gba laaye laaye lati pa ararẹ ni ile-iṣẹ iṣẹ-ṣiṣe, n ṣe ọpọlọpọ awọn ijabọ lori nẹtiwọọki, awọn ibugbe pings 10 ni gbogbo iṣẹju mẹwa 4. Nigbati o ba n pe ipe fidio kan, asopọ Intanẹẹti yoo bajẹ nigbagbogbo, nigbati ko le dara julọ. Fun bayi o jẹ dandan, nitorinaa o wa.

upload.fp.measure.office.com - tọka si Office 365, Emi ko le rii apejuwe to bojumu.
browser.pipe.aria.microsoft.com - Nko ri apejuwe to dara.
A dènà mejeeji.

connect.facebook.net - Facebook iwiregbe ohun elo. O ku.

mediator.mail.ru Iṣiro ti gbogbo awọn ibeere fun agbegbe mail.ru fihan wiwa ti nọmba nla ti awọn orisun ipolowo ati awọn agbowọ iṣiro, eyiti o fa igbẹkẹle. Ibugbe mail.ru ti firanṣẹ patapata si akojọ dudu.

google-analytics.com - ko ni ipa lori iṣẹ ṣiṣe ti awọn ẹrọ, nitorinaa a dènà rẹ.
doubleclick.net - ka awọn jinna ipolowo. A dènà.

Ọpọlọpọ awọn ibeere lọ si googleapis.com. Idinamọ naa ti yori si titiipa ayọ ti awọn ifiranṣẹ kukuru lori tabulẹti, eyiti o dabi aimọgbọnwa si mi. Ṣugbọn Playstore duro ṣiṣẹ, nitorinaa jẹ ki a ṣii.

cloudflare.com - wọn kọ pe wọn nifẹ orisun ṣiṣi ati, ni gbogbogbo, kọ pupọ nipa ara wọn. Awọn kikankikan ti awọn ìkápá iwadi ni ko šee igbọkanle, eyi ti o jẹ igba Elo ti o ga ju awọn gangan aṣayan iṣẹ-ṣiṣe lori ayelujara. Jẹ ki a fi silẹ fun bayi.

Nitorinaa, kikankikan ti awọn ibeere nigbagbogbo ni ibatan si iṣẹ ṣiṣe ti awọn ẹrọ naa. Ṣugbọn awọn ti o bori rẹ pẹlu iṣẹ ṣiṣe ni a tun ṣe awari.

Awọn gan akọkọ

Nigbati Intanẹẹti alailowaya ba wa ni titan, gbogbo eniyan tun sùn ati pe o ṣee ṣe lati rii iru awọn ibeere ti a firanṣẹ si nẹtiwọọki akọkọ. Nitorinaa, ni 6:50 Intanẹẹti wa ni titan ati ni akoko iṣẹju mẹwa akọkọ ti akoko awọn ibugbe 60 ti wa ni ibo lojoojumọ:

Ibeere ijabọ 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 ṣayẹwo asopọ WLAN fun wiwa oju-iwe wiwọle kan.
Citrix n pingi olupin rẹ botilẹjẹpe ohun elo naa ko nṣiṣẹ lọwọ.
Symantec jẹri awọn iwe-ẹri.
Mozilla sọwedowo fun awọn imudojuiwọn, botilẹjẹpe ninu awọn eto Mo beere lati ma ṣe eyi.

mmo.de jẹ iṣẹ ere kan. O ṣeese julọ pe ibeere naa jẹ ipilẹṣẹ nipasẹ iwiregbe facebook. A dènà.

Apple yoo mu gbogbo awọn iṣẹ rẹ ṣiṣẹ. api-glb-fra.smoot.apple.com - idajọ nipasẹ apejuwe, gbogbo bọtini tẹ ni a firanṣẹ nibi fun awọn idi imudara ẹrọ wiwa. Ifura pupọ, ṣugbọn ti o ni ibatan si iṣẹ ṣiṣe. A fi silẹ.

Atẹle jẹ atokọ gigun ti awọn ibeere si microsoft.com. A dènà gbogbo awọn ibugbe ti o bẹrẹ lati ipele kẹta.

Nọmba awọn subdomains akọkọ pupọ


Nitorinaa, awọn iṣẹju 10 akọkọ ti titan Intanẹẹti alailowaya.
Awọn idibo iOS julọ subdomains - 32. Atẹle nipasẹ Android - 24, lẹhinna Windows - 15 ati nikẹhin Blackberry - 9.
Awọn ohun elo facebook nikan ni idibo awọn ibugbe 10, awọn idibo skype 9 awọn ibugbe.

Orisun alaye

Orisun fun itupalẹ ni bind9 faili log olupin agbegbe, eyiti o ni ọna kika atẹle wọnyi:

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)

Faili naa ti gbe wọle sinu aaye data sqlite ati ṣe atupale nipa lilo awọn ibeere SQL.
Olupin naa n ṣiṣẹ bi kaṣe kan; awọn ibeere wa lati ọdọ olulana, nitorinaa alabara ibeere kan nigbagbogbo wa. Eto tabili ti o rọrun ti to, i.e. Ijabọ naa nilo akoko ti ibeere naa, ibeere naa funrararẹ, ati agbegbe ipele-keji fun ṣiṣe akojọpọ.

Awọn tabili 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)
);

ipari

Nitorinaa, bi abajade ti itupalẹ ti akọọlẹ olupin orukọ ìkápá, diẹ sii ju awọn igbasilẹ 50 ni a ṣe akiyesi ati gbe sori atokọ bulọọki.

Awọn iwulo ti diẹ ninu awọn ibeere jẹ apejuwe daradara nipasẹ awọn aṣelọpọ sọfitiwia ati ṣe iwuri igbẹkẹle. Bibẹẹkọ, pupọ ninu iṣẹ naa ko ni ipilẹ ati ibeere.

orisun: www.habr.com