🥇He aha ka hana a ka pūnaewele home a me nā helu helu kikowaena inoa

Hiki i kahi mea hoʻokele home (ma kēia hihia ʻo FritzBox) ke hoʻopaʻa i ka nui: pehea ka nui o ke kaʻa e hele ai i ka wā, ʻo wai ka mea i pili i ka wikiwiki, etc. ʻO kahi kikowaena inoa inoa (DNS) ma ka pūnaewele kūloko i kōkua iaʻu e ʻike i ka mea i hūnā ʻia ma hope o nā mea ʻike ʻole.

Ma keʻano holoʻokoʻa, ua loaʻa ka hopena maikaʻi o DNS ma ka pūnaewele home: ua hoʻohui i ka wikiwiki, kūpaʻa, a me ka hoʻokele.

Aia ma lalo kahi kiʻi i hāpai i nā nīnau a me ka pono e hoʻomaopopo i ka mea e hana nei. Ua kānana nā hopena i nā noi i ʻike ʻia a hana i nā kikowaena inoa inoa.

No ke aha i koho ʻia ai nā kāʻei kapu 60 i kēlā me kēia lā i ka wā e hiamoe ana nā mea a pau?

I kēlā me kēia lā, 440 mau kāʻei kua ʻike ʻole ʻia e koho ʻia i nā hola hana. ʻO wai lākou a he aha kā lākou hana?

Ka helu awelika o nā noi i kēlā me kēia lā ma ka hola

Nīnau hōʻike 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'))

I ka pō, hoʻopau ʻia ke kelepona ʻole a manaʻo ʻia ka hana ʻana o ka mea hana, ʻo ia hoʻi. ʻaʻohe koho balota no nā kāʻei kapu ʻike ʻole. ʻO ia ke kumu o ka hana nui loa mai nā polokalamu me nā ʻōnaehana hana e like me Android, iOS a me Blackberry OS.

E papa inoa i nā kāʻei kapu i koho nui ʻia. E hoʻoholo ʻia ka ikaika e nā ʻāpana e like me ka helu o nā noi i kēlā me kēia lā, ka helu o nā lā o ka hana a me ka nui o nā hola o ka lā i ʻike ʻia ai.

Aia nā mea i manaʻo ʻia ma ka papa inoa.

Nā kāʻei kapu i koho ʻia

Nīnau hōʻike 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

Hoʻopaʻa mākou i isс.blackberry.com a me iceberg.blackberry.com, a ka mea hana e hoʻāpono no nā kumu palekana. Ka hopena: i ka wā e hoʻāʻo ai e hoʻopili i ka WLAN, hōʻike ia i ka ʻaoʻao hoʻopaʻa inoa a ʻaʻole e hoʻopili hou ma kahi ʻē. E wehe kāua.

ʻO detectportal.firefox.com ka hana like, hoʻokō wale ʻia ma ka polokalamu kele Firefox. Inā pono ʻoe e komo i ka pūnaewele WLAN, e hōʻike mua ia i ka ʻaoʻao komo. ʻAʻole maopopo loa ke kumu e hoʻopili pinepine ʻia ai ka helu wahi, akā ua wehewehe ʻia ka mīkini e ka mea hana.

skype. Ua like nā hana o kēia papahana me kahi ilo: hūnā ʻo ia a ʻaʻole ʻae wale ia e pepehi iā ia iho i ka pahu hana, hoʻohua i ka nui o ke kaʻa ma ka pūnaewele, pings 10 domains i kēlā me kēia 4 mau minuke. I ka hana ʻana i ke kelepona wikiō, haki mau ka pilina pūnaewele, ke hiki ʻole ke ʻoi aku ka maikaʻi. I kēia manawa he mea pono, no laila ke mau nei.

upload.fp.measure.office.com - pili iā Office 365, ʻaʻole hiki iaʻu ke ʻike i kahi wehewehe kūpono.
browser.pipe.aria.microsoft.com - ʻAʻole i loaʻa iaʻu kahi wehewehe kūpono.
Kāohi mākou i nā mea ʻelua.

connect.facebook.net - ka palapala noi kamaʻilio Facebook. Koe.

Mediator.mail.ru Ua hōʻike ʻia kahi loiloi o nā noi āpau no ka domain mail.ru i ka nui o nā kumu hoʻolaha hoʻolaha a me nā mea hōʻiliʻili helu, kahi e hilinaʻi ai. Hoʻouna piha ʻia ka domain mail.ru i ka papa inoa ʻeleʻele.

google-analytics.com - ʻaʻole pili i ka hana o nā mea hana, no laila ke kāohi nei mākou iā ia.
doubleclick.net - helu i nā kaomi hoʻolaha. Hoʻopaʻa mākou.

Nui nā noi e hele i googleapis.com. ʻO ka pāpā ʻana ua alakaʻi i ka pani ʻana i ka hauʻoli o nā leka pōkole ma ka papa, he mea naʻaupō iaʻu. Akā, ua pau ka hana ʻana o ka hale pāʻani, no laila e wehe kākou.

cloudflare.com - kākau lākou makemake lākou i ka open source a, ma ka laulā, kākau nui e pili ana iā lākou iho. ʻAʻole maopopo loa ka ikaika o ka ʻimi noiʻi domain, ʻoi aku ka kiʻekiʻe ma mua o ka hana maoli ma ka Pūnaewele. E waiho kāua i kēia manawa.

No laila, pili pinepine ka ikaika o nā noi i ka hana pono o nā mea hana. Akā, ua ʻike ʻia ka poʻe i hoʻonui i ka hana.

ʻO ka mua loa

Ke hoʻā ʻia ka Pūnaewele uila, ua hiamoe nā mea a pau a hiki ke ʻike i nā noi i hoʻouna mua ʻia i ka pūnaewele. No laila, ma ka hola 6:50 e ho'ā ana ka Pūnaewele a ma ka manawa he ʻumi minuke mua e koho ʻia ai nā kikowaena 60 i kēlā me kēia lā:

Nīnau hōʻike 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

Nānā ʻo Firefox i ka pilina WLAN no ka loaʻa ʻana o kahi ʻaoʻao komo.
Ke hoʻomau nei ʻo Citrix i kāna kikowaena ʻoiai ʻaʻole holo ikaika ka noi.
Hōʻoia ʻo Symantec i nā palapala hōʻoia.
Nānā ʻo Mozilla i nā mea hou, ʻoiai ma nā hoʻonohonoho ua noi au ʻaʻole e hana i kēia.

ʻO mmo.de kahi lawelawe pāʻani. Hoʻomaka ʻia ka noi e ke kamaʻilio facebook. Hoʻopaʻa mākou.

E ho'ā ʻo Apple i kāna mau lawelawe āpau. api-glb-fra.smoot.apple.com - ma ka wehewehe ʻana, e hoʻouna ʻia kēlā me kēia pihi pihi ma ʻaneʻi no ka huli ʻana i ka ʻenekini. Manaʻo loa, akā pili i ka hana. Haʻalele mākou.

He papa inoa lōʻihi kēia o nā noi iā microsoft.com. Kāohi mākou i nā kāʻei kapu a pau e hoʻomaka ana mai ka pae ʻekolu.

Ka helu o nā subdomain mua loa

No laila, ʻo ka 10 mau minuke mua o ka hoʻohuli ʻana i ka Pūnaewele uila.
Ua koho ʻo IOS i nā subdomains nui loa - 32. Ua ukali ʻia e Android - 24, a laila Windows - 15 a hope loa ʻo Blackberry - 9.
ʻO ka noi facebook wale nō e koho i 10 mau kikowaena, skype polls 9 mau kikowaena.

ʻO kahi kumu ʻike

ʻO ke kumu no ka nānā ʻana ʻo ka bind9 local server log file, aia i loko o kēia ʻano:

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)

Ua lawe ʻia ka faila i kahi waihona sqlite a nānā ʻia me ka hoʻohana ʻana i nā nīnau SQL.
Hana ʻia ke kikowaena ma ke ʻano he huna; hele mai nā noi mai ke alalai, no laila hoʻokahi mau mea noi noi. Ua lawa ka papaʻaina maʻalahi, i.e. Pono ka hōʻike i ka manawa o ke noi, ke noi ponoʻī, a me ka pae ʻāpana ʻelua no ka hui ʻana.

Nā papa 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)
);

hopena

No laila, ma muli o ka nānā ʻana i ka log server name domain, ʻoi aku ma mua o 50 mau moʻolelo i censored a kau ʻia ma ka papa inoa poloka.

ʻO ka pono o kekahi mau nīnau i wehewehe maikaʻi ʻia e nā mea hana polokalamu a hoʻoulu i ka hilinaʻi. Eia nō naʻe, ʻaʻohe kumu o ka nui o ka hana.

Source: www.habr.com

Pehea ka noho ʻana o ka Internet home a me ka helu kikowaena inoa kikowaena?

Ka helu awelika o nā noi i kēlā me kēia lā ma ka hola

Nā kāʻei kapu i koho ʻia

ʻO ka mua loa

ʻO kahi kumu ʻike

hopena

Pākuʻi i ka manaʻo hoʻopuka ana pane