Sidee buu u nool yahay intarneetka gurigu iyo magaca xayndaabka server-ka?

Router-ka guriga (kiiskan FritzBox) wax badan ayuu qori karaa: inta taraafiggu socdo marka, yaa ku xidhan xawaarkee, iwm. Adeegga magac domain (DNS) ee shabakada deegaanka ayaa iga caawiyay inaan ogaado waxa ku qarsoon kuwa aan la garanayn.

Guud ahaan, DNS waxay saameyn togan ku yeelatay shabakadda guriga: waxay ku dartay xawaaraha, xasilloonida, iyo maaraynta.

Hoos waxaa ku yaal jaantus ka dhashay su'aalo iyo baahida loo qabo in la fahmo waxa dhacaya. Natiijooyinku waxay horeba u shaandheeyaan codsiyada la yaqaan iyo kuwa shaqeeya ee adeegayaasha magac domain.

Maxay tahay sababta 60 goobood oo dahsoon loo codeeyo maalin kasta iyadoo qof kastaa weli hurdo?

Maalin kasta, 440 goobood oo aan la garanayn ayaa la codeeyaa saacadaha firfircoonida. Waa ayo maxayse qabtaan?

Celceliska tirada codsiyada maalin kasta saacadba

Su'aasha warbixinta 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'))

Habeenkii, gelitaanka wirelessku waa naafo waxaana la filayaa hawsha qalabka, i.e. ma jirto wax codayn ah oo loogu talagalay domains aan la garanayn. Tani waxay ka dhigan tahay in dhaqdhaqaaqa ugu weyn uu ka yimaado aaladaha leh nidaamyada hawlgalka sida Android, iOS iyo Blackberry OS.

Aynu taxno domainsyada sida degdega ah loo codeeyay. Xoojinta waxaa lagu go'aamin doonaa cabbirro sida tirada codsiyada maalintii, tirada maalmaha dhaqdhaqaaqa iyo inta saacadood ee maalinta la ogaaday.

Dhammaan tuhmanayaasha la filayo ayaa ku jiray liiska.

Goobaha codbixinta degdega ah

Su'aasha warbixinta 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

Waxaan xannibnaa isс.blackberry.com iyo iceberg.blackberry.com, taas oo soo-saareyaashu ay ku caddayn doonaan sababo ammaan dartood. Natiijadu: markaad isku dayayso inaad ku xidho WLAN, waxay tusinaysaa bogga galitaanka oo waligeed meelna kuma xidhin mar dambe. Aan ka furno xannibaadda

detectportal.firefox.com waa isla farsamadii, oo kaliya laga hirgaliyay biraawsarkaaga Firefox. Haddii aad u baahan tahay inaad gasho shabakadda WLAN, waxay marka hore tusi doontaa bogga gelitaanka. Si buuxda uma cadda sababta ciwaanka loogu dhejin karo marar badan, laakiin habka waxaa si cad u qeexay soo saaraha.

skype Ficilada barnaamijkani waxay la mid yihiin gooryaanka: way qarisaa oo si fudud uma ogola in lagu dilo goobta shaqada, waxay abuurtaa taraafikada badan ee shabakada, pings 10 domains 4kii daqiiqoba mar. Markaad samaynayso wicitaan fiidyow ah, xidhiidhka internetku wuxuu si joogto ah u go'aa, marka aanu ka fiicnaan karin. Hadda waa lagama maarmaan, sidaas darteed way sii jirtaa.

upload.fp.measure.office.com - waxaa loola jeedaa Office 365, maan helin sharaxaad wanaagsan.
browser.pipe.aria.microsoft.com - Ma helin sharraxaad hufan.
Labadaba waan xannibnaa.

connect.facebook.net - Codsiga ku sheekeysiga Facebook. Hadhay

mediator.mail.ru Falanqaynta dhammaan codsiyada domainka mail.ru waxay muujisay joogitaanka tiro aad u badan oo agab xayaysiis ah iyo uruuriyayaal tirokoob, taas oo keenta kalsooni darro. Domain-ka mail.ru waxa gabi ahaanba loo diraa liiska madow

google-analytics.com - ma saameynayso shaqeynta qalabka, sidaas darteed waanu xannibnaa.
doubleclick.net - waxay tirisaa gujisyada xayeysiiska. Waan xannibnaa.

Codsiyo badan ayaa taga googleapis.com. Xannibaadda ayaa keentay in si farxad leh loo xidho farriimaha gaagaaban ee tablet-ka, kuwaas oo aniga iila muuqda doqonnimo. Laakin playstore-ka ayaa shaqadii joojiyay, markaa aan furno xannibaadda.

Cloudflare.com - waxay qoraan inay jecel yihiin il furan, guud ahaan, wax badan ayay ka qoraan naftooda. Xoojinta sahanka domain gabi ahaanba ma cadda, kaas oo inta badan aad uga sarreeya dhaqdhaqaaqa dhabta ah ee internetka. Aan iska dhaafno hadda.

Sidaa darteed, xoojinta codsiyada badanaa waxay la xiriirtaa shaqada loo baahan yahay ee qalabka. Laakin kuwa ku xad-gudbay dhaqdhaqaaqa ayaa iyagana la ogaaday.

Midka ugu horreeya

Marka intarneedka bilaa-waayirka ah la shido, qof kastaa weli wuu hurdaa, waxaana suurtogal ah in la arko codsiyada loo soo diro shabakadda marka hore. Marka, 6:50 Internetku wuu shidaa oo tobanka daqiiqo ee ugu horreeya 60 goobood ayaa la codeeyaa maalin kasta:

Su'aasha warbixinta 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 waxay hubisaa xidhiidhka WLAN joogitaanka bogga gelitaanka.
Citrix waxay ku dhejinaysaa server-keeda in kasta oo codsigu aanu si firfircoon u shaqaynayn.
Symantec waxay xaqiijisaa shahaadooyinka.
Mozilla waxay hubisaa wixii cusbooneed, in kasta oo goobaha aan ka codsaday inaanan tan samayn.

mmo.de waa adeeg ciyaaraha Inta badan codsiga waxaa bilaabay facebook chat. Waan xannibnaa.

Apple waxay hawlgelin doontaa dhammaan adeegyadeeda. api-glb-fra.smoot.apple.com - adoo eegaya sharraxaadda, badhan kasta oo gujis ah ayaa halkan loo soo diraa ujeedooyinka hagaajinta mashiinka raadinta. Aad looga shakisan yahay, laakiin la xidhiidha shaqeynta. Waanu ka tagaynaa.

Kuwa soo socda waa liis dheer oo codsiyada microsoft.com. Waxaan xannibnaa dhammaan domains laga bilaabo heerka saddexaad.

Tirada subdomains-ka ugu horreeya


Markaa, 10-ka daqiiqo ee ugu horreeya ee daaritaanka Internetka wireless-ka.
IOS codadka ugu badan subdomains - 32. Waxaa ku xiga Android - 24, ka dibna Windows - 15 iyo ugu dambeyntii Blackberry - 9.
Codsiga facebook kaligiis wuxuu codeeyaa 10 domains, skype polls 9 domains.

Isha xogta

Isha falanqayntu waxay ahayd bind9 faylka log server-ka maxalliga ah, kaas oo ka kooban qaabkan soo socda:

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)

Faylka waxa la soo galiyay kaydka xogta ee sqlite waxaana la falanqeeyay iyada oo la adeegsanayo su'aalaha SQL.
Seerfarku wuxuu u shaqeeyaa sidii kayd; codsiyadu waxay ka yimaaddaan router-ka, markaa had iyo jeer waxaa jira hal codsi oo macmiil ah. Qaab dhismeedka miiska la fududeeyay ayaa ku filan, i.e. Warbixintu waxay u baahan tahay wakhtiga codsiga, codsiga laftiisa, iyo qaybta heerka labaad ee kooxaynta.

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

gunaanad

Sidaa darteed, natiijada falanqaynta log domain name server log, in ka badan 50 diiwaan ayaa la faafreebiyay oo la geliyay liiska xannibaadda.

Baahida loo qabo qaar ka mid ah weydiimaha waxaa si fiican u sharaxay soosaarayaasha software waxayna dhiirigelisaa kalsoonida. Si kastaba ha ahaatee, hawsha inteeda badan waa mid aan sal iyo raad toona lahayn oo la is waydiin karo.

Source: www.habr.com