ProHoster > Blog > Tsamaiso > Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Router ea lapeng (tabeng ena FritzBox) e ka rekota haholo: ho na le sephethephethe se kae, ke mang ea hokahaneng ka lebelo lefe, joalo-joalo. Sebaka sa marang-rang sa marang-rang (DNS) marang-rang a lehae se nthusitse ho fumana se neng se patiloe ka morao ho baamoheli ba sa tsejoeng.

Ka kakaretso, DNS e bile le phello e ntle marang-rang a lapeng: e ekelitse lebelo, botsitso le taolo.

Ka tlaase mona ke setšoantšo se ileng sa hlahisa lipotso le tlhokahalo ea ho utloisisa se neng se etsahala. Liphetho li se li ntse li sefa likopo tse tsebahalang le tse sebetsang ho li-server tsa mabitso a domain.

Ke hobane'ng ha libaka tse 60 tse sa tsejoeng li hlahlojoa letsatsi le leng le le leng ha bohle ba ntse ba robetse?

Letsatsi le leng le le leng, libaka tse 440 tse sa tsejoeng li hlahlojoa nakong ea lihora tse sebetsang. Ke bo-mang ’me ba etsa’ng?

Karolelano ea palo ea likopo ka letsatsi le hora

Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Potso ea tlaleho ea 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'))

Bosiu, phihlello ea waelese e holofetse mme ts'ebetso ea sesebelisoa e lebelletsoe, i.e. ha ho na likhetho bakeng sa libaka tse sa tsejoeng. Sena se bolela hore mosebetsi o moholo ka ho fetisisa o tsoa ho lisebelisoa tse nang le lisebelisoa tse kang Android, iOS le Blackberry OS.

Ha re thathamiseng libaka tse hlahlobiloeng ka matla. Matla a tla khethoa ke mekhahlelo e kang palo ea likopo ka letsatsi, palo ea matsatsi a ts'ebetso le hore na li hlokomeloe lihora tse kae tsa letsatsi.

Babelaelloa bohle ba lebelletsoeng ba ne ba le lethathamong.

Libaka tse hlahlobiloeng ka matla

Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Potso ea tlaleho ea 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

Re thibela isс.blackberry.com le iceberg.blackberry.com, eo moetsi a tla e lokafatsa ka mabaka a tšireletso. Sephetho: ha u leka ho hokela ho WLAN, e bonts'a leqephe la ho kena 'me ha e sa hlola e hokela kae kapa kae. Ha re e thibolle.

detectportal.firefox.com ke mokhoa o ts'oanang, o sebelisoang feela ho sebatli sa Firefox. Haeba o hloka ho kena marang-rang a WLAN, e tla qala ho bonts'a leqephe la ho kena. Ha ho hlake ka ho feletseng hore na ke hobane'ng ha aterese e lokela ho pinged hangata, empa mochine o hlalosoa ka ho hlaka ke moetsi.

skype. Liketso tsa lenaneo lena li tšoana le sebōkō: e ipata 'me ha e lumelle feela hore e bolaoe ka har'a sebaka sa mosebetsi, e hlahisa sephethephethe se sengata marang-rang, pings 10 domains metsotso e meng le e meng e 4. Ha u etsa call ea video, khokahanyo ea Marang-rang e senyeha khafetsa, ha ho se be betere. Bakeng sa hona joale ho hlokahala, kahoo e sala.

upload.fp.measure.office.com - e bua ka Office 365, ha kea fumana tlhaloso e ntle.
browser.pipe.aria.microsoft.com - Ha ke fumane tlhaloso e nepahetseng.
Re thibela bobeli.

connect.facebook.net - Sesebelisoa sa puisano sa Facebook. Ho sala.

mediator.mail.ru Tlhahlobo ea likopo tsohle tsa sebaka sa mail.ru e bontšitse boteng ba palo e kholo ea lisebelisoa tsa papatso le babokelli ba lipalo-palo, tse bakang ho se tšepane. Sebaka sa mail.ru se romelloa ka ho feletseng ho lenane la batho ba batšo.

google-analytics.com - ha e ame ts'ebetso ea lisebelisoa, kahoo rea e thibela.
doubleclick.net - e bala lipapatso tsa lipapatso. Re thibela.

Likopo tse ngata li ea ho googleapis.com. Ho thibela ho lebisitse ho koaloa ka thabo ha melaetsa e khutšoanyane letlapeng, e bonahalang e le booatla ho 'na. Empa playstore e emisitse ho sebetsa, ka hona, ha re e thibolle.

cloudflare.com - ba ngola hore ba rata mohloli o bulehileng mme, ka kakaretso, ba ngola haholo ka bona. Matla a phuputso ea sebaka sa marang-rang ha a hlake ka ho feletseng, ao hangata a leng holimo haholo ho feta mosebetsi oa sebele oa Inthanete. Ha re e tloheleng hajoale.

Kahoo, matla a likopo hangata a amana le ts'ebetso e hlokahalang ea lisebelisoa. Empa ba ileng ba e etsa ho tlōla ka mosebetsi le bona ba ile ba fumanoa.

Ea pele haholo

Ha Inthanete e se nang mohala e buletsoe, bohle ba ntse ba robetse 'me hoa khoneha ho bona hore na ke likopo life tse rometsoeng marang-rang pele. Kahoo, ka 6:50 Marang-rang aa buleha 'me nakong ea metsotso e leshome ea pele libaka tse 60 li hlahlojoa letsatsi le letsatsi:

Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Potso ea tlaleho ea 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 e lekola khokahano ea WLAN bakeng sa boteng ba leqephe la ho kena.
Citrix e penya seva ea eona leha ts'ebeliso e sa sebetse ka mafolofolo.
Symantec e netefatsa litifikeiti.
Mozilla e hlahloba lintlafatso, leha ho litlhophiso ke kopile ho se etse sena.

mmo.de ke tšebeletso ea papali. Mohlomong kopo e qalisoa ke moqoqo oa facebook. Re thibela.

Apple e tla kenya lits'ebeletso tsohle tsa eona. api-glb-fra.smoot.apple.com - ho latela tlhaloso, konopo e 'ngoe le e 'ngoe ea konopo e romelloa mona molemong oa ho ntlafatsa enjine ea patlo. E belaella haholo, empa e amana le ts'ebetso. Rea e tlohela.

E latelang ke lethathamo le lelelele la likopo ho microsoft.com. Re thibela libaka tsohle ho tloha boemong ba boraro.

Palo ea li-subdomain tsa pele haholo
Marang-rang a marang-rang a phela joang le lipalo-palo tsa seva sa domain name?

Kahoo, metsotso ea pele ea 10 ea ho bulela Inthanete e se nang mohala.
Likhetho tsa iOS li-subdomain tse ngata ka ho fetisisa - 32. E lateloa ke Android - 24, ebe Windows - 15 'me qetellong Blackberry - 9.
Sesebelisoa sa facebook se le seng se khetha libaka tse 10, likhetho tsa skype tse 9.

Mohloli oa boitsebiso

Mohloli oa tlhahlobo e ne e le faele ea marang-rang ea bind9 ea lehae, e nang le sebopeho se latelang:

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)

Faele e ne e kentsoe ka har'a database ea sqlite mme ea hlahlojoa ho sebelisoa lipotso tsa SQL.
Seva e sebetsa joalo ka cache; likopo li tsoa ho router, kahoo kamehla ho na le moreki a le mong oa kopo. Sebopeho sa tafole se nolofalitsoeng se lekane, i.e. Tlaleho e hloka nako ea kopo, kopo ka boeona, le sebaka sa boemo ba bobeli bakeng sa ho hlophisa.

Litafole tsa DL

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

fihlela qeto e

Ka hona, ka lebaka la tlhahlobo ea sebaka sa marang-rang sa marang-rang, lirekoto tse fetang 50 li ile tsa hlahlojoa 'me tsa behoa lethathamong la li-block.

Tlhokahalo ea lipotso tse ling e hlalositsoe hantle ke baetsi ba software mme e etsa hore motho a itšepe. Leha ho le joalo, boholo ba tšebetso ha bo na motheo ebile boa belaetsa.

Source: www.habr.com

Eketsa ka tlhaloso