🥇 Kedu ihe ịntanetị na-eme yana ọnụ ọgụgụ sava ngalaba aha

Onye rawụta ụlọ (na nke a FritzBox) nwere ike ịdekọ ọtụtụ: ole okporo ụzọ na-aga mgbe, onye ejikọtara na ụdị ọsọ, wdg. Ihe nkesa aha ngalaba (DNS) na netwọk mpaghara nyeere m aka ịchọpụta ihe zoro ezo n'azụ ndị nnata amaghị.

N'ozuzu, DNS enweela mmetụta dị mma na netwọk ụlọ: ọ gbakwunyere ọsọ ọsọ, nkwụsi ike, na njikwa.

N'okpuru ebe a bụ eserese nke welitere ajụjụ na mkpa ọ dị ịghọta ihe na-eme. Nsonaazụ enyochalarị arịrịọ amaara ama na arụ ọrụ na sava ngalaba aha.

Kedu ihe kpatara na ngalaba 60 na-adịghị ahụkebe na-enyocha kwa ụbọchị mgbe onye ọ bụla ka na-ehi ụra?

Kwa ụbọchị, a na-enyocha ngalaba 440 amabeghị n'oge awa ọrụ. Ole ndị ka ha bụ na gịnị ka ha na-eme?

Nkezi ọnụọgụ arịrịọ kwa ụbọchị site na elekere

Ajụjụ mkpesa 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'))

N'abalị, a na-enweghi ike ịnweta ikuku ikuku yana a na-atụ anya ọrụ ngwaọrụ, ya bụ. enweghị ntuli aka maka ngalaba amaghị. Nke a pụtara na ọrụ kacha ukwuu sitere na ngwaọrụ nwere sistemụ arụmọrụ dị ka Android, iOS na Blackberry OS.

Ka anyị depụta ngalaba ndị a gbara ajụjụ ọnụ nke ukwuu. A ga-ekpebi ike ahụ site na paramita dị ka ọnụọgụ arịrịọ kwa ụbọchị, ọnụọgụ ụbọchị ọrụ yana n'ime awa ole n'ụbọchị ahụrụ ha.

Ndị niile a na-enyo enyo nọ na ndepụta ahụ.

Ngalaba a kpasuru iwe

Ajụjụ mkpesa 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

Anyị na-egbochi isс.blackberry.com na iceberg.blackberry.com, nke onye nrụpụta ga-akwado maka ihe nchekwa. Nsonaazụ: mgbe ị na-agbalị ijikọ na WLAN, ọ na-egosi ibe nbanye na ọ dịghị mgbe ọ bụla jikọọ ọzọ. Ka anyị kpọghee ya.

detectportal.firefox.com bụ otu usoro a na-eme ya naanị na ihe nchọgharị Firefox. Ọ bụrụ na ịchọrọ ịbanye na netwọk WLAN, ọ ga-ebu ụzọ gosi ibe nbanye. O dochaghị anya ihe kpatara eji etinye adreesị ahụ ugboro ugboro, mana onye nrụpụta kọwara usoro ahụ nke ọma.

skype. Omume nke mmemme a yiri ikpuru: ọ na-ezo ma ghara ikwe ka e gbuo ya na taskbar, na-emepụta ọtụtụ okporo ụzọ na netwọk, pings 10 ngalaba kwa nkeji 4. Mgbe ị na-akpọ oku vidiyo, njikọ ịntanetị na-akụda mgbe niile, mgbe ọ nweghị ike ịka mma. Maka ugbu a ọ dị mkpa, ya mere ọ na-anọgide.

upload.fp.measure.office.com - na-ezo aka na Office 365, enweghị m ike ịhụ nkọwa dị mma.
browser.pipe.aria.microsoft.com - Enweghị m ike ịhụ nkọwa dị mma.
Anyị na-egbochi ha abụọ.

connect.facebook.net - ngwa nkata Facebook. foduru.

mediator.mail.ru Nyochaa arịrịọ niile maka ngalaba mail.ru gosipụtara ọnụnọ nke nnukwu ọnụọgụ mgbasa ozi na ndị na-anakọta ọnụ ọgụgụ, nke na-akpata ntụkwasị obi. A na-eziga ngalaba mail.ru kpamkpam na listi ojii.

google-analytics.com - anaghị emetụta ọrụ nke ngwaọrụ, yabụ anyị na-egbochi ya.
doubleclick.net - na-agụ ọpịpị mgbasa ozi. Anyị na-egbochi.

Ọtụtụ arịrịọ na-aga googleapis.com. Mgbochi ahụ emewo ka a na-emechi ọṅụ nke obere ozi na mbadamba nkume, nke yiri ihe nzuzu n'anya m. Mana playstore kwụsịrị ịrụ ọrụ, yabụ ka anyị kpọghee ya.

cloudflare.com - ha na-ede na ha hụrụ ebe mepere emepe n'anya na, n'ozuzu, dee ọtụtụ ihe banyere onwe ha. Ịdị ike nke nyocha ngalaba abụghị nke doro anya, nke na-adịkarị elu karịa ọrụ n'ezie na ịntanetị. Ka anyị hapụ ya ugbu a.

Ya mere, ike nke arịrịọ na-emetụtakarị ọrụ achọrọ nke ngwaọrụ. Ma a chọpụtakwara ndị mejọrọ ya.

Nke mbụ

Mgbe ikuku ịntanetị na-agbanye, onye ọ bụla ka na-ehi ụra ma nwee ike ịhụ arịrịọ ndị a na-ezigara na netwọk mbụ. Yabụ, na 6:50 ịntanetị na-agbanye na n'ime nkeji iri mbụ, a na-enyocha ngalaba 60 kwa ụbọchị:

Ajụjụ mkpesa 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 na-enyocha njikọ WLAN maka ọnụnọ nke ibe nbanye.
Citrix na-etinye ihe nkesa ya n'agbanyeghị na ngwa ahụ anaghị arụ ọrụ nke ọma.
Symantec na-enyocha asambodo.
Mozilla na-enyocha maka mmelite, ọ bụ ezie na na ntọala m jụrụ ka m ghara ime nke a.

mmo.de bụ ọrụ egwuregwu. O yikarịrị ka ọ bụ nkata facebook malitere arịrịọ a. Anyị na-egbochi.

Apple ga-arụ ọrụ ya niile. api-glb-fra.smoot.apple.com - ikpe site na nkọwa, a na-eziga bọtịnụ ọ bụla pịa ebe a maka ebumnuche njikarịcha search engine. Na-enyo enyo nke ukwuu, mana metụtara ọrụ. Anyị na-ahapụ ya.

Ihe a bụ ndepụta ogologo nke arịrịọ maka microsoft.com. Anyị na-egbochi ngalaba niile malite na ọkwa nke atọ.

Ọnụọgụ nke subdomains mbụ

Yabụ, nkeji iri mbụ nke ịgbanwuo ịntanetị ikuku.
iOS ntuli aka kacha subdomains - 32. Na-esote gam akporo - 24, wee Windows - 15 na ikpeazụ Blackberry - 9.
Ngwa Facebook naanị na-eme ntuli aka ngalaba 10, ntuli aka skype ngalaba 9.

Isi mmalite nke ozi

Isi mmalite maka nyocha a bụ faịlụ ndekọ ihe nkesa mpaghara bind9, nke nwere usoro a:

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)

Ebubatara faịlụ a na nchekwa data sqlite wee nyochaa site na iji ajụjụ SQL.
Ihe nkesa ahụ na-arụ ọrụ dị ka cache; arịrịọ sitere na rawụta, yabụ enwere onye ahịa arịrịọ mgbe niile. Nhazi tebụl dị mfe ezuola, ya bụ. Akụkọ a chọrọ oge nke arịrịọ, arịrịọ ahụ n'onwe ya, yana ngalaba ọkwa nke abụọ maka ịchịkọta.

Ọnụ ego nke 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)
);

nkwubi

Ya mere, n'ihi nyocha nke ndekọ aha nkesa ngalaba, ihe karịrị 50 ndekọ ka enyochara ma tinye ya na ndepụta ngọngọ.

Ndị nrụpụta ngwanrọ kọwara mkpa ụfọdụ ajụjụ dị mkpa ma kpalie ntụkwasị obi. Otú ọ dị, ọtụtụ n'ime ọrụ ahụ enweghị ihe ndabere na nke a na-enyo enyo.

isi: www.habr.com

Kedu ka ọnụ ọgụgụ ihe nkesa ịntanetị na-ebi na ngalaba aha?

Nkezi ọnụọgụ arịrịọ kwa ụbọchị site na elekere

Ngalaba a kpasuru iwe

Nke mbụ

Isi mmalite nke ozi

nkwubi

Tinye a comment Отменить ответ