Ngabe i-Intanethi yasekhaya iphila kanjani kanye nezibalo zeseva yegama lesizinda?

Irutha yasekhaya (kulokhu iFritzBox) ingarekhoda okuningi: ingakanani ithrafikhi ehamba nini, ubani oxhunywe ngasiphi isivinini, njll. Iseva yegama lesizinda (i-DNS) kunethiwekhi yendawo ingisize ukuthola ukuthi yini ebifihliwe ngemuva kwabamukeli abangaziwa.

Sekukonke, i-DNS ibe nomthelela omuhle kunethiwekhi yasekhaya: yengeze isivinini, ukuzinza, nokuphathwa.

Ngezansi kunomdwebo ophakamise imibuzo kanye nesidingo sokuqonda ukuthi kwakwenzekani. Imiphumela isivele ihlunga izicelo ezaziwayo nezisebenzayo kumaseva egama lesizinda.

Kungani izizinda ezingacacile ze-60 ziphendulwa nsuku zonke ngenkathi wonke umuntu elele?

Nsuku zonke, kuphonswa izizinda ezingaziwa ezingama-440 phakathi namahora okusebenza. Bangobani futhi benzani?

Isilinganiso senani lezicelo ngosuku ngehora

Umbuzo wombiko we-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'))

Ebusuku, ukufinyelela okungenantambo kukhutshaziwe futhi umsebenzi wedivayisi ulindelwe, i.e. akukho ukuvota kwezizinda ezingaziwa. Lokhu kusho ukuthi umsebenzi omkhulu kakhulu uvela kumadivayisi anezinhlelo zokusebenza ezifana ne-Android, iOS ne-Blackberry OS.

Ake sibhale izizinda ezivotelwe kakhulu. Ukuqina kuzonqunywa ngamapharamitha afana nenani lezicelo ngosuku, inani lezinsuku zomsebenzi nokuthi mangaki amahora osuku eziqashelwe ngawo.

Bonke abasolwa abebelindelwe bebesohlwini.

Izizinda eziphonswe ngokujulile

Umbuzo wombiko we-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

Sivimba i-isс.blackberry.com kanye ne-iceberg.blackberry.com, okuzovunyelwa umenzi ngezizathu zokuphepha. Umphumela: uma uzama ukuxhuma ku-WLAN, ikhombisa ikhasi lokungena futhi ayiphinde ixhume noma kuphi. Masiyivule.

detectportal.firefox.com iyindlela efanayo, isetshenziswa kuphela kusiphequluli seFirefox. Uma udinga ukungena kunethiwekhi ye-WLAN, izobonisa kuqala ikhasi lokungena. Akucaci ngokuphelele ukuthi kungani ikheli kufanele lifakwe i-ping kaningi, kodwa indlela ichazwe ngokucacile ngumenzi.

skype. Izenzo zalolu hlelo zifana ne-worm: ifihla futhi ayivumeli nje ukuthi ibulawe kubha yomsebenzi, ikhiqiza ithrafikhi eningi kunethiwekhi, i-pings izizinda ezi-10 njalo ngemizuzu eyi-4. Uma wenza ikholi yevidiyo, uxhumano lwe-inthanethi luhlala luphuka, lapho lungeke lube ngcono. Ngoba manje kudingekile, ngakho uhlala.

upload.fp.measure.office.com - ibhekisela ku-Office 365, angikwazanga ukuthola incazelo ehloniphekile.
browser.pipe.aria.microsoft.com - Angikwazanga ukuthola incazelo ehloniphekile.
Sivimba sobabili.

connect.facebook.net - Isicelo sokuxoxa se-Facebook. Kusele.

mediator.mail.ru Ukuhlaziywa kwazo zonke izicelo zesizinda se-mail.ru kubonise ukuba khona kwenani elikhulu lezinsiza zokukhangisa nabaqoqi bezibalo, okubangela ukungathembani. Isizinda se-mail.ru sithunyelwa ngokuphelele ohlwini lwabavinjelwe.

I-google-analytics.com - ayithinti ukusebenza kwamadivayisi, ngakho siyayivimba.
i-doubleclick.net - ibala ukuchofoza kokukhangisa. Siyavimba.

Izicelo eziningi ziya ku-googleapis.com. Ukuvinjwa kuholele ekuvalweni kwenjabulo kwemilayezo emifushane kuthebhulethi, ebonakala iyisiphukuphuku kimi. Kodwa i-playstore iyekile ukusebenza, ngakho-ke asiyivule.

cloudflare.com - babhala ukuthi bathanda umthombo ovulekile futhi, ngokuvamile, babhala okuningi ngabo. Ukuqina kwenhlolovo yesizinda akucaci ngokuphelele, okuvame ukuba phezulu kakhulu kunomsebenzi wangempela oku-inthanethi. Asiyiyeke okwamanje.

Ngakho-ke, ukushuba kwezicelo kuvame ukuhlobene nokusebenza okudingekayo kwamadivayisi. Kodwa labo abeqile ngomsebenzi baphinde batholakala.

Eyokuqala impela

Uma i-inthanethi engenantambo ivuliwe, wonke umuntu usalele futhi kuyenzeka ukubona ukuthi yiziphi izicelo ezithunyelwa kunethiwekhi kuqala. Ngakho-ke, ngo-6:50 i-inthanethi iyavula futhi esikhathini esiyimizuzu eyishumi yokuqala yesikhathi izizinda ezingama-60 ziphendulwa nsuku zonke:

Umbuzo wombiko we-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

IFirefox ihlola uxhumo lwe-WLAN ukuze ibone ukuthi kukhona ikhasi lokungena ngemvume.
I-Citrix iphinga iseva yayo yize uhlelo lokusebenza lungasebenzi.
I-Symantec iqinisekisa izitifiketi.
I-Mozilla ihlola izibuyekezo, nakuba kuzilungiselelo ngicele ukungakwenzi lokhu.

mmo.de iyisevisi yokudlala. Kungenzeka ukuthi isicelo siqalwa yingxoxo ye-facebook. Siyavimba.

I-Apple izovula zonke izinsiza zayo. api-glb-fra.smoot.apple.com - ngokubheka incazelo, konke ukuchofoza inkinobho kuthunyelwa lapha ngezinjongo zokuthuthukisa injini yokusesha. Kusolisa kakhulu, kodwa kuhlobene nokusebenza. Siyayishiya.

Okulandelayo uhlu olude lwezicelo ku-microsoft.com. Sivimba zonke izizinda kusukela ezingeni lesithathu.

Inombolo yezizinda ezingaphansi zokuqala


Ngakho, imizuzu yokuqala eyi-10 yokuvula i-inthanethi engenantambo.
Ukuvota kwe-iOS izizinda ezingaphansi kakhulu - 32. Kulandelwa i-Android - 24, bese kuba yi-Windows - 15 futhi okokugcina i-Blackberry - 9.
Uhlelo lokusebenza lwe-facebook lulodwa luvotela izizinda eziyi-10, izizinda ze-skype ziyi-9.

Umthombo wolwazi

Umthombo wokuhlaziywa bekuyifayela lokungena leseva yendawo ye-bind9, eliqukethe ifomethi elandelayo:

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)

Ifayela lingeniswe kusizindalwazi se-sqlite futhi lahlaziywa kusetshenziswa imibuzo ye-SQL.
Iseva isebenza njengenqolobane; izicelo zivela kumzila, ngakho-ke kuhlala kuneklayenti elilodwa lesicelo. Isakhiwo setafula esenziwe lula sanele, i.e. Umbiko udinga isikhathi sesicelo, isicelo ngokwaso, kanye nesizinda sezinga lesibili sokuqoqwa.

Amathebula e-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)
);

isiphetho

Ngakho, ngenxa yokuhlaziywa kwelogi yegama lesizindalwazi, amarekhodi angaphezu kuka-50 ahlolwe futhi afakwa ohlwini lokuvimba.

Isidingo seminye imibuzo sichazwa kahle abakhiqizi bezinhlelo zokusebenza futhi sikhuthaza ukuzethemba. Nokho, umsebenzi omningi awunasisekelo futhi uyangabazeka.

Source: www.habr.com