ProHoster > Ibhulogi > Ulawulo > Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

I-router yasekhaya (kule meko iFritzBox) inokurekhoda kakhulu: ingakanani i-traffic ehambayo xa, ngubani odibeneyo kwiphi isantya, njl. Iseva yegama lesizinda (DNS) kwinethiwekhi yendawo yandinceda ukuba ndifumanise ukuba yintoni efihliweyo emva kwabamkeli abangaziwayo.

Ngokubanzi, i-DNS ibe nefuthe elihle kwinethiwekhi yasekhaya: yongeze isantya, ukuzinza, kunye nokulawula.

Apha ngezantsi kukho umzobo owaphakamisa imibuzo kunye nesidingo sokuqonda okwenzekayo. Iziphumo sele zihluza izicelo ezaziwayo nezisebenzayo kwiiseva zamagama esizinda.

Kutheni le nto i-60 domains engabonakaliyo iphononongwa yonke imihla ngelixa wonke umntu elele?

Yonke imihla, ii-domain ezingama-440 ezingaziwayo zivotelwa ngexesha leeyure ezisebenzayo. Ngoobani kwaye benza ntoni?

I-avareji yenani lezicelo ngosuku ngeyure

Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

Umbuzo wengxelo yeSQL

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

Ebusuku, ukufikelela kwi-wireless kuvaliwe kwaye umsebenzi wesixhobo ulindeleke, okt. akukho luvoto lwemimandla engaziwayo. Oku kuthetha ukuba owona msebenzi mkhulu uvela kwizixhobo ezineenkqubo zokusebenza ezifana ne-Android, iOS kunye ne-Blackberry OS.

Masidwelise imimandla ephononongwe kakhulu. Ubukhulu buya kumiselwa ngeeparamitha ezifana nenani lezicelo ngosuku, inani leentsuku zomsebenzi kunye nokuba zingaphi iiyure zosuku eziye zaqatshelwa.

Bonke abarhanelwa abalindelekileyo bebekolu luhlu.

Imimandla ephononongwe ngokunzulu

Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

Umbuzo wengxelo yeSQL

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

Sivimba isс.blackberry.com kunye ne-iceberg.blackberry.com, apho umenzi uya kuthethelela ngenxa yezizathu zokhuseleko. Isiphumo: xa uzama ukuqhagamshela kwi-WLAN, ibonisa iphepha lokungena kwaye ayiphinde idibanise naphi na. Masiyivule.

detectportal.firefox.com ikwayindlela enye, ephunyezwa kuphela kwiFirefoxbrowser. Ukuba ufuna ukungena kwinethiwekhi yeWLAN, iya kubonisa kuqala iphepha lokungena. Akucaci ngokupheleleyo ukuba kutheni idilesi kufuneka ifakwe rhoqo, kodwa indlela ichazwe ngokucacileyo ngumenzi.

skype. Izenzo zale nkqubo zifana nembungu: ifihla kwaye ayivumeli nje ukuba ibulawe kwibar yomsebenzi, ivelise i-traffic eninzi kwinethiwekhi, i-pings 10 domains yonke imizuzu eyi-4. Xa ufowuna ngevidiyo, uqhagamshelo lwe-Intanethi luhlala luwohloka, xa lungenakuba ngcono. Kuba ngoku kuyimfuneko, ngoko ihlala.

upload.fp.measure.office.com - ibhekisa kwiOfisi 365, andifumananga nkcazelo endilisekileyo.
browser.pipe.aria.microsoft.com-Andifumananga nkcazelo ifanelekileyo.
Sivala zombini.

connect.facebook.net-isicelo sencoko kaFacebook. Ushiyekile.

mediator.mail.ru Uhlalutyo lwazo zonke izicelo ze-mail.ru domain lubonise ubukho benani elikhulu lezibonelelo zentengiso kunye nabaqokeleli bezibalo, ezibangela ukungathembani. I-domain ye-mail.ru ithunyelwe ngokupheleleyo kuluhlu lwabamnyama.

google-analytics.com - ayichaphazeli ukusebenza kwezixhobo, ngoko siyayivimba.
i-doubleclick.net - ibala unqakrazo lwentengiso. Siyabhloka.

Izicelo ezininzi ziya kugoogleapis.com. Ukubhloka kukhokelele ekuvalweni ngovuyo kwemiyalezo emifutshane kwithebhulethi, ebonakala ibubudenge kum. Kodwa i-playstore iyekile ukusebenza, ngoko ke masiyivule.

cloudflare.com - babhala ukuba bathanda umthombo ovulekileyo kwaye, ngokubanzi, babhala okuninzi malunga nabo. Ubunzulu besaveyi yesizinda ayicacanga ngokupheleleyo, ehlala iphezulu kakhulu kunomsebenzi wangempela kwi-Intanethi. Masiyiyeke okwangoku.

Ke ngoko, ubukhulu bezicelo buhlala buhambelana nokusebenza okufunekayo kwezixhobo. Kodwa abo bagqithisa ngokusebenza nabo bafunyanwa.

Eyokuqala kakhulu

Xa i-Intanethi engenazingcingo ivuliwe, wonke umntu usalele kwaye kunokwenzeka ukubona ukuba zeziphi izicelo ezithunyelwa kwinethiwekhi kuqala. Ke, ngo-6:50 i-Intanethi iyavula kwaye kwimizuzu elishumi yokuqala yexesha iindawo ezingama-60 ziphendulwa mihla le:

Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

Umbuzo wengxelo yeSQL

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

IFirefox ijonga udibaniso lweWLAN malunga nobukho bephepha lokungena.
I-Citrix i-pinging iseva yayo nangona isicelo singasebenzi.
I-Symantec iqinisekisa iziqinisekiso.
IMozilla ijonga uhlaziyo, nangona kuseto ndicelile ukuba ndingakwenzi oku.

mmo.de yinkonzo yokudlala. Okunokwenzeka ukuba isicelo siqaliswe ngengxoxo ye-facebook. Siyabhloka.

I-Apple iya kuvula zonke iinkonzo zayo. api-glb-fra.smoot.apple.com - ukugweba ngenkcazo, lonke iqhosha lokucofa lithunyelwa apha ngeenjongo zokuphucula injini yokukhangela. Ikrokrela kakhulu, kodwa inxulumene nokusebenza. Siyayishiya.

Oku kulandelayo luluhlu olude lwezicelo kwi-microsoft.com. Sivimba zonke iindawo ukusuka kwinqanaba lesithathu.

Inani lesubdomains zokuqala kakhulu
Ihlala njani i-Intanethi yasekhaya kunye nedatha yeseva yegama lesizinda?

Ke, imizuzu yokuqala ye-10 yokuvula i-Intanethi engenazingcingo.
I-iOS yokuvota i-subdomains ezininzi - 32. Ilandelwa yi-Android - 24, emva koko Windows - 15 kwaye ekugqibeleni iBlackberry - 9.
Isicelo se-facebook sisodwa sinokhetho lwe-10 domains, i-skype polls i-9 domains.

Umthombo wolwazi

Umthombo wohlalutyo yayiyi-bind9 yefayile yelogu yeseva yasekuhlaleni, equlethe le fomati ilandelayo:

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)

Ifayile ithathwe kumazwe angaphandle kwisiseko sedata sesqlite kwaye yahlalutywa kusetyenziswa imibuzo yeSQL.
Umncedisi usebenza njenge-cache izicelo zivela kwi-router, ngoko kukho rhoqo umxhasi wesicelo omnye. Isakhiwo setafile esilula sanele, okt. Ingxelo idinga ixesha lesicelo, isicelo ngokwaso, kunye ne-domain yenqanaba lesibini lokuhlanganisa.

Iitafile ze-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)
);

isiphelo

Ngaloo ndlela, ngenxa yohlalutyo lwe-domain yegama le-server log, ngaphezu kweerekhodi ze-50 zihlolwe kwaye zafakwa kuluhlu lwebhloko.

Imfuneko yeminye imibuzo ichazwa kakuhle ngabavelisi besoftware kwaye ikhuthaza ukuzithemba. Nangona kunjalo, umsebenzi omninzi awunasiseko kwaye uyathandabuza.

umthombo: www.habr.com

Yongeza izimvo