Kodi intaneti yakunyumba imakhala bwanji komanso ziwerengero za seva ya domain name?

Router yapanyumba (panthawiyi, FritzBox) imatha kulemba zambiri: kuchuluka kwa magalimoto omwe akugwiritsidwa ntchito, liti, omwe amalumikizidwa pa liwiro lotani, ndi zina zotero. Seva ya dzina lachidziwitso (DNS) pamanetiweki amderali idandithandiza kudziwa chomwe chinali kuseri kwa ma adilesi osadziwika.

Ponseponse, DNS yakhala ndi zotsatira zabwino pamaneti apanyumba, ndikuwonjezera liwiro, kukhazikika, komanso kuwongolera.

Pansipa pali chithunzi chomwe chinayambitsa mafunso ndi kufunika komvetsetsa zomwe zikuchitika. Zotsatira zake zasankhidwa kale kuti zigwirizane ndi zopempha zodziwika komanso zogwira ntchito ku ma seva. mayina a domain.

Chifukwa chiyani madera 60 osadziwika akufunsidwa tsiku lililonse aliyense akadali mtulo?

Tsiku lililonse, madera osadziwika a 440 amafunsidwa nthawi yogwira ntchito. Kodi iwo ndi ndani ndipo amachita chiyani?

Avereji ya zopempha patsiku ndi ola

Lipoti la funso la 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'))

Usiku, kugwiritsa ntchito mawayilesi opanda zingwe kumazimitsidwa ndipo ntchito ya chipangizocho imayembekezeredwa, mwachitsanzo, palibe kufufuza madera osadziwika. Izi zikutanthauza kuti ntchito yayikulu kwambiri imachokera ku zipangizo zomwe zimagwiritsa ntchito makina ogwiritsira ntchito monga Android, iOS ndi Blackberry OS.

Tizindikira madomeni omwe amafufuzidwa kwambiri. Kukula kumeneku kudzatsimikiziridwa ndi magawo monga kuchuluka kwa zopempha patsiku, kuchuluka kwa masiku ochita, ndi nthawi zatsiku zomwe adawonedwa.

M’ndandandawu munali anthu onse amene akuwakayikira.

Madomeni omwe amafufuzidwa kwambiri

Lipoti la funso la 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

Timaletsa iss.blackberry.com ndi iceberg.blackberry.com, zomwe wopanga amavomereza ndi nkhawa zachitetezo. Zotsatira: poyesa kulumikizana ndi Wi-Fi, imawonetsa tsamba lolowera ndipo silimalumikizana ndi china chilichonse. Tiyeni titsegule.

detectportal.firefox.com ndi njira yofananira, koma imayendetsedwa mu msakatuli wa Firefox. Mukalowa mu netiweki ya Wi-Fi, imayamba kuwonetsa tsamba lolowera. Sizidziwika bwino chifukwa chake imayenera kuyimba adilesi pafupipafupi, koma makinawo amafotokozedwa momveka bwino ndi wopanga.

Skype. Pulogalamuyi imagwira ntchito ngati nyongolotsi: imabisala ndipo siyilola kuti iphedwe mosavuta mu taskbar, imapanga ma network ambiri, ndikuyika madambwe anayi mphindi 10 zilizonse. Mukayimba pavidiyo, imasokoneza intaneti nthawi zonse, ngakhale sizingakhale bwino. Imakhalabe m'malo mpaka pano, bola ngati ndiyofunikira.

upload.fp.measure.office.com - amatanthauza Office 365, sindinapeze malongosoledwe abwino.
browser.pipe.aria.microsoft.com - Sindinapeze malongosoledwe abwino.
Timaletsa onse awiri.

connect.facebook.net ndi pulogalamu yochezera ya Facebook. Ikadali pano.

Kuwunika kwa zopempha zonse za mail.ru kudawulula kupezeka kwa zinthu zambiri zotsatsa ndi osonkhanitsa ziwerengero, zomwe zimadzutsa kukayikira. Dera lonse la mail.ru lidatsekedwa.

google-analytics.com - sizimakhudza magwiridwe antchito a chipangizocho, chifukwa chake timaletsa.
doubleclick.net - amawerengera kudina kwa malonda. Timawaletsa.

Zopempha zambiri zikupita ku googleapis.com. Kuyiletsa kwayimitsa mosangalala mauthenga achidule pa piritsi langa, zomwe ndimaona kuti ndizopusa. Koma Play Store yasiya kugwira ntchito, ndiye tiyimitsa.

cloudflare.com - amati amakonda gwero lotseguka ndipo nthawi zambiri amalemba zambiri za iwo eni. Kuchulukira kwa magalimoto amtunduwu sikudziwika bwino, chifukwa nthawi zambiri kumakhala kokwera kwambiri kuposa zomwe zimachitika pa intaneti. Tizisiya pamenepo.

Chifukwa chake, kuchuluka kwa zopempha nthawi zambiri kumakhudzana ndi magwiridwe antchito ofunikira a zida. Komabe, ena anapezekanso kuti anali okangalika kwambiri.

Zoyamba kwambiri

Pamene intaneti yopanda zingwe imatsegulidwa, aliyense akadali akugona, kotero ndizotheka kuwona zopempha zomwe zimatumizidwa poyamba. Chifukwa chake, nthawi ya 6:50 AM, intaneti imatsegulidwa, ndipo mphindi khumi zoyambirira zatsiku, madambwe 60 amafunsidwa:

Lipoti la funso la 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 imayang'ana kulumikizana kwa WLAN pa tsamba lolowera.
Citrix imayimba seva yake ngakhale pulogalamuyo siyikuyenda.
Symantec imatsimikizira ziphaso.
Mozilla imayang'ana zosintha, ngakhale ndidapempha kuti isachite izi pazokonda.

mmo.de ndi ntchito yamasewera. Pempholi mwina lidayambitsidwa ndi macheza a Facebook. Tiziletsa.

Apple imayendetsa ntchito zake zonse. api-glb-fra.smoot.apple.com - kutengera kufotokozera, makiyi aliwonse amatumizidwa pano kuti akwaniritse zolinga zakusaka. Zokayikitsa kwambiri, koma zokhudzana ndi magwiridwe antchito. Tizisunga.

Kenako pamabwera mndandanda wautali wazofunsira Microsoft.com. Timaletsa madambwe onse, kuyambira gawo lachitatu.

Chiwerengero cha ma subdomain apamwamba


Chifukwa chake, mphindi 10 zoyambirira zoyatsa intaneti yopanda zingwe.
Mafunso a iOS ndi ma subdomain ambiri - 32. Amatsatiridwa ndi Android - 24, kenako Windows — 15 ndipo Blackberry yomaliza — 9.
Pulogalamu ya Facebook yokha imafunsa madambwe 10, Skype imafunsa madambwe 9.

Gwero la chidziwitso

Gwero la kusanthulaku linali fayilo ya log yakomweko Seva bind9, yomwe ili ndi mawonekedwe otsatirawa:

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)

Fayiloyo idatumizidwa ku database ya sqlite ndikuyankhidwa pogwiritsa ntchito mafunso a SQL.
Seva imakhala ngati cache, ndipo zopempha zimachokera ku rauta, kotero nthawi zonse pamakhala kasitomala mmodzi yekha. Mapangidwe a tebulo osavuta ndi okwanira, kutanthauza kuti lipotilo limangofuna nthawi yopempha, pempho lokha, ndi dera lachiwiri la magulu.

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

Pomaliza

Chifukwa chake, chifukwa cha kusanthula kwa chipika cha seva ya domain name, zolemba zopitilira 50 zidafufuzidwa ndikuyikidwa pamndandanda wa block.

Kufunika kwa zopempha zina kumalembedwa bwino ndi ogulitsa mapulogalamu ndipo kumalimbikitsa chidaliro. Komabe, zambiri mwazochitazo zilibe maziko ndipo zimadzutsa kukayikira.

Source: www.habr.com