ProHoster > Blog > Pulega > E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

Ole telefoni ile fale (i lenei tulaga FritzBox) e mafai ona faʻamaumau le tele: o le a le tele o fefaʻatauaiga o loʻo alu, o ai e fesoʻotaʻi i le saoasaoa, ma isi. O se domain name server (DNS) i luga o fesoʻotaʻiga faʻapitonuʻu na fesoasoani ia te aʻu e suʻe le mea o loʻo natia i tua o tagata e le o iloa.

I le aotelega, o le DNS na i ai se aafiaga lelei i luga o le upega tafaʻilagi: ua faʻaopoopoina le saoasaoa, mautu, ma le pulea.

O lo'o i lalo se ata na lāgā ai ni fesili ma le mana'oga ia malamalama i le mea na tupu. O fa'ai'uga ua mae'a fa'amama fa'amama fa'atonuga ma fa'atino galuega ile fa'aumau igoa ole igoa.

Aisea e su'esu'e ai vaega e 60 i aso uma a'o momoe pea tagata uma?

O aso uma lava, e 440 vaega e le o iloa e su'esu'eina i taimi o galuega. O ai i latou ma o a mea latou te faia?

Ole aofa'i ole talosaga ile aso ile itula

E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

Su'ega lipoti a 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 le po, e le mafai ona faʻaogaina le uaealesi ma faʻamoemoeina le gaioiga o masini, i.e. e leai se palota mo vaega e le iloa. O lona uiga o le gaioiga sili e sau mai masini ma faiga faʻaogaina e pei ole Android, iOS ma le Blackberry OS.

Se'i o tatou lisi vaega o lo'o su'esu'eina malosi. Ole malosi ole a fa'amoemoeina ile fa'asologa e pei ole numera ole talosaga ile aso, ole numera o aso ole gaioiga ma le tele o itula ole aso na latou matauina.

O tagata masalomia uma sa i ai i le lisi.

Fa'atosina su'esu'eina vaega

E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

Su'ega lipoti a 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

Matou te poloka isс.blackberry.com ma iceberg.blackberry.com, lea o le a faʻamaonia e le gaosiga mo mafuaaga saogalemu. I'uga: pe a taumafai e fa'afeso'ota'i i le WLAN, e fa'aalia ai le itulau saini ma e le toe feso'ota'i i so'o se mea. Tatou tatala le poloka.

detectportal.firefox.com o le masini lava lea e tasi, naʻo le faʻatinoina i le Firefox browser. Afai e te manaʻomia le ulufale i totonu o le WLAN network, o le a faʻaalia muamua le itulau saini. E le o manino atoatoa pe aisea e tatau ai ona pinged le tuatusi i taimi uma, ae o le masini o loʻo faʻamatalaina manino e le gaosiga.

skype. O gaioiga a lenei polokalame e tutusa ma se anufe: e natia ma e le na o le faʻatagaina o ia lava e fasiotia i le taskbar, faʻatupuina le tele o feoaiga i luga o le upega tafailagi, pings 10 domains i 4 minute. Pe a faia se telefoni vitio, o le Initaneti e malepe i taimi uma, pe a le mafai ona sili atu. Mo le taimi nei e tatau, o lea e tumau ai.

upload.fp.measure.office.com - e faasino ile Office 365, e le mafai ona ou mauaina se faʻamatalaga lelei.
browser.pipe.aria.microsoft.com - E le mafai ona ou mauaina se faʻamatalaga lelei.
Matou te poloka uma.

connect.facebook.net - Facebook chat application. Tu'umau.

mediator.mail.ru O se auʻiliʻiliga o talosaga uma mo le mail.ru domain na faʻaalia ai le i ai o se numera tele o punaoa faʻasalalauga ma faʻamaumauga fuainumera, lea e mafua ai le le talitonuina. O le mail.ru domain e lafo atoa i le blacklist.

google-analytics.com - e le afaina ai le gaioiga o masini, o lea matou te poloka ai.
doubleclick.net - faitau fa'asalalauga kiliki. Matou te poloka.

E tele talosaga e alu ile googleapis.com. O le polokaina na mafua ai le tapunia fiafia o savali pupuu i luga o le laulau, lea e foliga mai ia te aʻu e valea. Ae ua le toe galue le playstore, o lea tatou tatala le poloka.

cloudflare.com - latou te tusia latou te fiafia i punaoa tatala ma, i se tulaga lautele, tusi tele e uiga ia i latou lava. Ole malosi ole su'esu'ega ole domain e le'o manino atoatoa, lea e masani ona sili atu le maualuga nai lo le gaioiga moni ile Initaneti. Se'i o tatou tu'u pea mo le taimi nei.

O le mea lea, o le malosi o talosaga e masani ona fesoʻotaʻi ma le manaʻomia o galuega a masini. Ae na maua foi i latou na soona fai i gaoioiga.

Le muamua lava

Pe a ki le Initaneti uaealesi, o loʻo momoe pea tagata uma ma e mafai ona vaʻai poʻo fea talosaga e lafo muamua i le fesoʻotaʻiga. O lea la, i le 6:50 e ki ai le Initaneti ma i le sefulu minute muamua o le taimi e suʻesuʻeina ai 60 domains i aso uma:

E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

Su'ega lipoti a 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

E siaki e Firefox le feso'ota'iga WLAN mo le iai o se itulau saini.
O loʻo faʻapipiʻi e Citrix lana 'auʻaunaga e ui lava e le o taʻavale le talosaga.
Symantec fa'amaonia tusi pasi.
Mozilla siaki mo faʻafouga, e ui lava i tulaga na ou fai atu e aua le faia lenei mea.

mmo.de o se tautua ta'aloga. E foliga mai o le talosaga e amataina e le facebook chat. Matou te poloka.

O le a faʻagaoioia e Apple ana auaunaga uma. api-glb-fra.smoot.apple.com - fa'amasino i le fa'amatalaga, o fa'amau uma kiliki e lafo iinei mo le su'esu'eina o masini su'esu'e. E sili ona masalosalo, ae e fesoʻotaʻi ma faʻatinoga. Matou te tuua.

Ole lisi umi lea ole talosaga ile microsoft.com. Matou te poloka vaega uma e amata mai i le tulaga tolu.

Numera o subdomains muamua
E fa'afefea ona ola le Initaneti ile fale ma fa'amaumauga ole igoa ole igoa ole igoa?

O lea la, o le 10 minute muamua o le faʻaogaina o le Initaneti uaealesi.
Ua palota iOS le tele o subdomains - 32. Sosoo ai ma le Android - 24, sosoo ai ma le Windows - 15 ma mulimuli ane Blackberry - 9.
O le talosaga facebook na'o le palota e 10 domains, skype polls 9 domains.

Le puna o faamatalaga

O le punavai mo le auiliiliga o le bind9 local server log file, lea e aofia ai le faatulagaga lenei:

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)

O le faila na faaulufale mai i totonu o se sqlite database ma suʻesuʻeina e faʻaaoga ai fesili SQL.
O le 'au'aunaga e galue e pei o se fa'aoga; o talosaga e sau mai le router, o lea e tasi lava le talosaga a le tagata fa'atau. O se fausaga laulau faigofie e lava, i.e. E mana'omia e le lipoti le taimi o le talosaga, le talosaga lava ia, ma le vaega lona lua mo le fa'avasegaina.

DDL laulau

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

iʻuga

O le mea lea, o se taunuuga o le auʻiliʻiliga o le log name server log, e sili atu i le 50 faʻamaumauga na faʻamaonia ma tuʻuina i luga o le lisi poloka.

O le mana'omia o nisi o fesili o lo'o fa'amatalaina lelei e le au fai polokalame ma fa'aosofia ai le mautinoa. Ae ui i lea, o le tele o gaioiga e le faʻavae ma fesiligia.

puna: www.habr.com

Faaopoopo i ai se faamatalaga