Ta yaya Intanet ɗin gida ke rayuwa da kididdigar sunan sabar?

Mai na'ura mai ba da hanya tsakanin hanyoyin sadarwa (a cikin wannan yanayin FritzBox) na iya yin rikodin da yawa: yawan zirga-zirgar zirga-zirgar da ke faruwa lokacin, wanda aka haɗa a wane saurin, da sauransu. Sabar sunan yanki (DNS) akan hanyar sadarwar gida ta taimaka min gano abin da ke ɓoye a bayan waɗanda ba a san su ba.

Gabaɗaya, DNS ya sami tasiri mai kyau akan hanyar sadarwar gida: ya ƙara saurin gudu, kwanciyar hankali, da sarrafawa.

A ƙasa akwai zane wanda ya tada tambayoyi da buƙatar fahimtar abin da ke faruwa. Sakamakon ya riga ya tace sanannun da buƙatun aiki zuwa sabar sunan yankin.

Me yasa ake kada kuri'a 60 a duk rana yayin da kowa ke barci?

Kowace rana, 440 da ba a san wuraren da ba a san su ba ana yin zabe a cikin sa'o'i masu aiki. Wanene su kuma menene suke yi?

Matsakaicin adadin buƙatun kowace rana zuwa sa'a

Tambayar rahoton 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'))

Da dare, ana kashe damar shiga mara waya kuma ana sa ran aikin na'urar, watau. babu wani zabe na yankunan da ba a san su ba. Wannan yana nufin cewa mafi girman aiki ya zo daga na'urori masu tsarin aiki kamar Android, iOS da Blackberry OS.

Bari mu jera wuraren da aka yi zabe sosai. Za a ƙayyade ƙarfin ta sigogi kamar adadin buƙatun kowace rana, adadin kwanakin aiki da kuma a cikin sa'o'i nawa na ranar da aka lura da su.

Dukkan wadanda ake zargin suna cikin jerin sunayen.

Yankunan da aka kada kuri'a

Tambayar rahoton 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

Muna toshe isс.blackberry.com da iceberg.blackberry.com, wanda masana'anta zai ba da hujja saboda dalilai na tsaro. Sakamako: lokacin ƙoƙarin haɗawa da WLAN, yana nuna shafin shiga kuma baya sake haɗawa a ko'ina. Bari mu buɗe shi.

detectportal.firefox.com tsari iri ɗaya ne, kawai ana aiwatar dashi a cikin burauzar Firefox. Idan kana buƙatar shiga cikin hanyar sadarwar WLAN, za ta fara nuna shafin shiga. Ba a bayyana cikakken dalilin da ya sa ya kamata a yi amfani da adireshin sau da yawa ba, amma masana'anta sun bayyana tsarin a fili.

skype. Ayyukan wannan shirin suna kama da tsutsa: yana ɓoyewa kuma baya ƙyale kansa kawai a kashe shi a cikin taskbar, yana haifar da yawan zirga-zirga akan hanyar sadarwa, pings 10 domains kowane minti 4. Lokacin yin kiran bidiyo, haɗin Intanet koyaushe yana lalacewa, lokacin da ba zai iya zama mafi kyau ba. Don yanzu ya zama dole, don haka ya kasance.

upload.fp.measure.office.com - yana nufin Office 365, ban sami kwatance mai kyau ba.
browser.pipe.aria.microsoft.com - Ba zan iya samun kwatance mai kyau ba.
Mun toshe duka biyu.

connect.facebook.net - Facebook chat aikace-aikace. Ya rage

mediator.mail.ru Binciken duk buƙatun don yankin mail.ru ya nuna kasancewar adadin albarkatun talla da masu tattara ƙididdiga, wanda ke haifar da rashin yarda. An aika yankin mail.ru gaba ɗaya zuwa jerin baƙaƙe.

google-analytics.com - baya shafar ayyukan na'urori, don haka muna toshe shi.
doubleclick.net - yana ƙidaya danna talla. Muna toshe.

Yawancin buƙatun suna zuwa googleapis.com. Toshewar ya haifar da rufewar gajerun saƙon cikin farin ciki a kan kwamfutar hannu, waɗanda ke da alama wauta a gare ni. Amma playstore ya daina aiki, don haka bari mu buɗe shi.

cloudflare.com - sun rubuta cewa suna son bude tushen kuma, a gaba ɗaya, rubuta da yawa game da kansu. Ƙarfin binciken yanki ba a bayyana gaba ɗaya ba, wanda sau da yawa yana da yawa fiye da ainihin aiki akan Intanet. Mu bar shi a yanzu.

Don haka, tsananin buƙatun galibi yana da alaƙa da ayyukan da ake buƙata na na'urorin. Amma an kuma gano wadanda suka wuce gona da iri.

Na farko

Lokacin da aka kunna Intanet mara waya, kowa har yanzu yana barci kuma yana yiwuwa a ga irin buƙatun da aka aika zuwa cibiyar sadarwa da farko. Don haka, da ƙarfe 6:50 Intanit yana kunna kuma a cikin minti goma na farko na lokaci 60 ana yin zabe a kowace rana:

Tambayar rahoton 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 tana bincika haɗin WLAN don kasancewar shafin shiga.
Citrix yana pinging uwar garken sa duk da cewa aikace-aikacen ba ya gudana.
Symantec yana tabbatar da takaddun shaida.
Mozilla tana bincika sabuntawa, kodayake a cikin saitunan na nemi kar in yi hakan.

mmo.de sabis ne na caca. Mai yuwuwa an ƙaddamar da buƙatar ta hanyar facebook chat. Muna toshe.

Apple zai kunna duk ayyukansa. api-glb-fra.smoot.apple.com - yin la'akari da bayanin, ana aika kowane danna maballin nan don dalilai na inganta injin bincike. Mai tsananin tuhuma, amma mai alaƙa da aiki. Mu bar shi.

Mai zuwa shine jerin jerin buƙatun zuwa microsoft.com. Muna toshe duk wuraren da suka fara daga mataki na uku.

Adadin ƙananan yankuna na farko


Don haka, mintuna 10 na farko na kunna Intanet mara waya.
IOS zabe mafi subdomains - 32. Bi Android - 24, sai Windows - 15 da kuma karshe Blackberry - 9.
Aikace-aikacen facebook kadai yana yin zabe 10 domains, skype polls 9 domains.

Tushen bayani

Tushen binciken shine fayil ɗin log ɗin uwar garken gida na bind9, wanda ya ƙunshi tsari mai zuwa:

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)

An shigo da fayil ɗin cikin ma'ajin bayanai na sqlite kuma an bincika ta amfani da tambayoyin SQL.
Sabar tana aiki azaman cache; buƙatun sun fito daga na'ura mai ba da hanya tsakanin hanyoyin sadarwa, don haka koyaushe akwai abokin ciniki buƙata ɗaya. Tsarin tebur mai sauƙi ya isa, watau. Rahoton yana buƙatar lokacin buƙatar, buƙatar da kanta, da yanki na mataki na biyu don haɗawa.

Farashin 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)
);

ƙarshe

Don haka, sakamakon bincike na log ɗin uwar garken sunan yankin, an tantance fiye da 50 bayanai kuma an sanya su a cikin jerin toshewar.

Wajabcin wasu tambayoyin masana'antun software sun bayyana da kyau kuma suna ƙarfafa kwarin gwiwa. Koyaya, yawancin ayyukan ba su da tushe kuma abin tambaya.

source: www.habr.com