Router yapanyumba (panthawiyi FritzBox) imatha kulemba zambiri: kuchuluka kwa magalimoto akuyenda liti, ndani amalumikizidwa pa liwiro lotani, ndi zina zambiri. Seva ya dzina lachidziwitso (DNS) pamaneti akomweko idandithandiza kudziwa zomwe zidabisika kumbuyo kwa olandila osadziwika.
Ponseponse, DNS yakhudza kwambiri maukonde apanyumba: yawonjezera liwiro, kukhazikika, komanso kuwongolera.
Pansipa pali chithunzi chomwe chinadzutsa mafunso komanso kufunika komvetsetsa zomwe zikuchitika. Zotsatira zimasefa kale zopempha zodziwika komanso zogwirira ntchito kumaseva a mayina a domain.
Chifukwa chiyani madera osadziwika a 60 amafunsidwa tsiku lililonse aliyense akadali mtulo?
Tsiku lililonse, madera 440 osadziwika 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, kupeza opanda zingwe kumaletsedwa ndipo ntchito ya chipangizo ikuyembekezeka, i.e. palibe kuvotera kwa madambwe osadziwika. Izi zikutanthauza kuti ntchito yaikulu imachokera ku zipangizo zomwe zili ndi machitidwe monga Android, iOS ndi Blackberry OS.
Tiyeni titchule madera omwe amafunsidwa mozama. Kuchulukaku kudzatsimikiziridwa ndi magawo monga kuchuluka kwa zopempha patsiku, kuchuluka kwa masiku ochita ntchito komanso maola angati atsiku omwe adawonedwa.
Onse omwe amayembekezeredwa kukayikira anali pamndandanda.
Madomeni osankhidwa mozama
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 20Timaletsa isΡ.blackberry.com ndi iceberg.blackberry.com, zomwe wopanga adzazilungamitsa pazifukwa zachitetezo. Zotsatira: poyesa kulumikiza ku WLAN, ikuwonetsa tsamba lolowera ndipo silimalumikizananso paliponse. Tiyeni titsegule.
detectportal.firefox.com ndi njira yomweyi, yongokhazikitsidwa mu msakatuli wa Firefox. Ngati mukufuna kulowa mu netiweki ya WLAN, iwonetsa tsamba lolowera. Sizodziwikiratu chifukwa chake adilesi iyenera kukhala pinged nthawi zambiri, koma makinawo amafotokozedwa momveka bwino ndi wopanga.
skype. Zochita za pulogalamuyi ndizofanana ndi nyongolotsi: zimabisala ndipo sizimangodzilola kuti ziphedwe mu taskbar, zimapanga magalimoto ambiri pamaneti, pings 10 domains mphindi 4 zilizonse. Mukayimba foni pavidiyo, intaneti imawonongeka nthawi zonse, pomwe sizingakhale bwino. Kwa tsopano ndikofunikira, kotero kumakhalabe.
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 - Ntchito yochezera pa Facebook. Zatsala.
mediator.mail.ru Kuwunika kwa zopempha zonse za mail.ru kudawonetsa kupezeka kwa zinthu zambiri zotsatsa ndi osonkhanitsa ziwerengero, zomwe zimayambitsa kusakhulupirirana. Tsamba la mail.ru limatumizidwa kwathunthu ku blacklist.
google-analytics.com - sizimakhudza magwiridwe antchito a zida, chifukwa chake timaletsa.
doubleclick.net - imawerengera kudina kotsatsa. Timatsekereza.
Zopempha zambiri zimapita ku googleapis.com. Kutsekereza kwadzetsa kutseka kosangalatsa kwa mauthenga achidule pa piritsi, zomwe zimawoneka zopusa kwa ine. Koma playstore inasiya kugwira ntchito, ndiye tiyeni titsegule.
cloudflare.com - amalemba kuti amakonda gwero lotseguka ndipo, makamaka, amalemba zambiri za iwo okha. Kuchuluka kwa kafukufuku wam'derali sikudziwika bwino, komwe nthawi zambiri kumakhala kokwera kwambiri kuposa zomwe zimachitika pa intaneti. Tisiyeni pano.
Chifukwa chake, kuchuluka kwa zopempha nthawi zambiri kumakhudzana ndi magwiridwe antchito ofunikira a zida. Koma omwe adachita mopambanitsa adapezekanso.
Choyamba kwambiri
Pamene intaneti yopanda zingwe yatsegulidwa, aliyense akadali akugona ndipo ndizotheka kuwona zopempha zomwe zimatumizidwa ku netiweki poyamba. Chifukwa chake, nthawi ya 6:50 intaneti imayatsidwa ndipo munthawi ya mphindi khumi zoyambirira madera 60 amafunsidwa tsiku lililonse:
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 DESCFirefox imayang'ana kulumikizidwa kwa WLAN kuti mupeze tsamba lolowera.
Citrix ikuyitanitsa seva yake ngakhale pulogalamuyo siyikuyenda.
Symantec imatsimikizira ziphaso.
Mozilla imayang'ana zosintha, ngakhale pazokonda ndidapempha kuti ndisachite izi.
mmo.de ndi ntchito yamasewera. Nthawi zambiri pempholi limayambitsidwa ndi macheza a facebook. Timatsekereza.
Apple idzayambitsa ntchito zake zonse. api-glb-fra.smoot.apple.com - kutengera malongosoledwe, batani lililonse limatumizidwa pano kuti likwaniritse zolinga zakusaka. Zokayikitsa kwambiri, koma zokhudzana ndi magwiridwe antchito. Timazisiya.
Zotsatirazi ndi mndandanda wautali wa zopempha ku microsoft.com. Timaletsa madambwe onse kuyambira gawo lachitatu.
Chiwerengero cha ma subdomain oyamba kwambiri
Chifukwa chake, mphindi 10 zoyambirira zoyatsa intaneti yopanda zingwe.
iOS zisankho madera ambiri - 32. Kutsatiridwa ndi Android - 24, ndiye Windows - 15 ndipo potsiriza Blackberry - 9.
Pulogalamu ya facebook yokha imasankha madera 10, ma skype polls madera 9.
Gwero lachidziwitso
Gwero la kusanthulako linali fayilo ya chipika cha seva ya bind9, yomwe ili ndi mawonekedwe awa:
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 ndikuwunikidwa pogwiritsa ntchito mafunso a SQL.
Seva imakhala ngati cache; zopempha zimachokera ku rauta, kotero nthawi zonse pamakhala kasitomala wopempha. Mapangidwe a tebulo osavuta ndi okwanira, i.e. Lipotilo limafuna nthawi ya pempho, pempho lokha, ndi dera lachiwiri lamagulu.
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 mafunso ena kumafotokozedwa bwino ndi opanga mapulogalamu ndipo kumalimbikitsa chidaliro. Komabe, zambiri mwazochitazo zilibe maziko komanso zokayikitsa.
Source: www.habr.com