ããŒã ã«ãŒã¿ãŒ (ãã®å Žå㯠FritzBox) ã¯ããã€ã©ã®ãããã®ãã©ãã£ãã¯ãæµããŠãããã誰ãã©ã®é床ã§æ¥ç¶ããŠããããªã©ãå€ãã®ããšãèšé²ã§ããŸãã ããŒã«ã« ãããã¯ãŒã¯äžã®ãã¡ã€ã³ ããŒã ãµãŒã㌠(DNS) ã¯ãäžæãªåä¿¡è ã®èåŸã«äœãé ãããŠããããèŠã€ããã®ã«åœ¹ç«ã¡ãŸããã
å šäœãšããŠãDNS ã¯ããŒã ãããã¯ãŒã¯ã«ãã©ã¹ã®åœ±é¿ãäžããé床ãå®å®æ§ã管çæ§ãåäžããŸããã
以äžã¯ãçåãšäœãèµ·ãã£ãŠããã®ããç解ããå¿ èŠæ§ãåŒãèµ·ãããå³ã§ãã çµæã¯ããã¡ã€ã³ ããŒã ãµãŒããŒã«å¯Ÿããæ¢ç¥ã®åäœäžã®ãªã¯ãšã¹ãããã§ã«ãã£ã«ã¿ãŒã§é€å€ããŠããŸãã
誰ãããŸã ç ã£ãŠããéã«ã60 ã®ç¡åãã¡ã€ã³ãæ¯æ¥ããŒãªã³ã°ãããã®ã¯ãªãã§ãããã?
æ¯æ¥ãã¢ã¯ãã£ããªæé垯㫠440 ã®æªç¥ã®ãã¡ã€ã³ãããŒãªã³ã°ãããŸãã 圌ãã¯äœè
ã§ãäœãããŠããã®ã§ãããã?
XNUMX æ¥ãããã®æéããšã®å¹³åãªã¯ãšã¹ãæ°
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'))
å€éã¯ã¯ã€ã€ã¬ã¹ ã¢ã¯ã»ã¹ãç¡å¹ã«ãªããããã€ã¹ã®ã¢ã¯ãã£ããã£ãäºæ³ãããŸãã äžæãªãã¡ã€ã³ã«å¯ŸããããŒãªã³ã°ã¯è¡ãããŸããã ããã¯ãAndroidãiOSãBlackberry OS ãªã©ã®ãªãã¬ãŒãã£ã³ã° ã·ã¹ãã ãæèŒããããã€ã¹ããã®ã¢ã¯ãã£ããã£ãæãå€ãçºçããŠããããšãæå³ããŸãã
éäžçã«ããŒãªã³ã°ããããã¡ã€ã³ããªã¹ãããŠã¿ãŸãããã 匷床ã¯ãXNUMX æ¥ãããã®ãªã¯ãšã¹ãã®æ°ãã¢ã¯ãã£ããã£ã®æ¥æ°ããªã¯ãšã¹ãã XNUMX æ¥ã®ãã¡ã«äœæéãã£ãããªã©ã®ãã©ã¡ãŒã¿ã«ãã£ãŠæ±ºãŸããŸãã
äºæ³ããã容çè ã¯å šå¡ãªã¹ãã«èŒã£ãŠããã
éäžçã«ããŒãªã³ã°ããããã¡ã€ã³
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
ã¡ãŒã«ãŒã¯ã»ãã¥ãªãã£äžã®çç±ããããããæ£åœåãããããisÑ.blackberry.com ãš Iceberg.blackberry.com ããããã¯ããŸãã çµæ: WLAN ã«æ¥ç¶ããããšãããšããã°ã€ã³ ããŒãžã衚瀺ãããã©ãã«ãæ¥ç¶ã§ããªããªããŸãã ãããã¯ã解é€ããŸãããã
detectportal.firefox.com ãåãã¡ã«ããºã ã§ãFirefox ãã©ãŠã¶ãŒã«ã®ã¿å®è£ ãããŠããŸãã WLAN ãããã¯ãŒã¯ã«ãã°ã€ã³ããå¿ èŠãããå Žåã¯ãæåã«ãã°ã€ã³ ããŒãžã衚瀺ãããŸãã ãªãã¢ãã¬ã¹ã«é »ç¹ã« ping ãéä¿¡ããå¿ èŠãããã®ãââã¯å®å šã«ã¯æããã§ã¯ãããŸãããããã®ã¡ã«ããºã ã¯ã¡ãŒã«ãŒã«ãã£ãŠæ確ã«èª¬æãããŠããŸãã
ã¹ã«ã€ãã ãã®ããã°ã©ã ã®åäœã¯ã¯ãŒã ã«äŒŒãŠããŸããã¿ã¹ã¯ããŒã«é ããŠèªåèªèº«ã匷å¶çµäºãããããããã¯ãŒã¯äžã«å€§éã®ãã©ãã£ãã¯ãçæãã10 åããšã« 4 ã€ã®ãã¡ã€ã³ã« ping ãéä¿¡ããŸãã ãããªé話ãè¡ããšãã€ã³ã¿ãŒãããæ¥ç¶ãåžžã«åæãããŠããŸããŸãã ä»ã®ãšããå¿ èŠãªã®ã§ãã®ãŸãŸã«ããŠãããŸãã
Upload.fp.measure.office.com - Office 365 ãæããŸããé©åãªèª¬æãèŠã€ãããŸããã§ããã
browser.pipe.aria.microsoft.com - é©åãªèª¬æãèŠã€ãããŸããã§ããã
ç§ãã¡ã¯äž¡æ¹ããããã¯ããŸãã
connect.facebook.net - Facebook ãã£ãã ã¢ããªã±ãŒã·ã§ã³ã æ®ã£ãŠããŸãã
mediator.mail.ru mail.ru ãã¡ã€ã³ã«å¯Ÿãããã¹ãŠã®ãªã¯ãšã¹ããåæãããšãããèšå€§ãªæ°ã®åºåãªãœãŒã¹ãšçµ±èšåéè ã®ååšã瀺ãããäžä¿¡æãåŒãèµ·ãããŠããŸãã mail.ru ãã¡ã€ã³å šäœããã©ãã¯ãªã¹ãã«éä¿¡ãããŸãã
google-analytics.com - ããã€ã¹ã®æ©èœã«ã¯åœ±é¿ããªãããããããã¯ããŸãã
doubleclick.net - åºåã®ã¯ãªãã¯ãã«ãŠã³ãããŸãã ç§ãã¡ã¯ãããã¯ããŸãã
å€ãã®ãªã¯ãšã¹ã㯠googleapis.com ã«éä¿¡ãããŸãã ãã®ãããã¯ã«ãããã¿ãã¬ããäžã®çãã¡ãã»ãŒãžãå¬ããããšã«ã·ã£ããããŠã³ãããããã«ãªããŸããããç§ã«ã¯ãããæãã«æããŸãã ããããPlayã¹ãã¢ãåäœããªããªã£ãã®ã§ããããã¯ã解é€ããŸãããã
Cloudflare.com - 圌ãã¯ãªãŒãã³ãœãŒã¹ã倧奜ãã ãšæžããäžè¬ã«èªåèªèº«ã«ã€ããŠå€ãã®ããšãæžããŸãã ãã¡ã€ã³èª¿æ»ã®åŒ·åºŠã¯å®å šã«ã¯æããã§ã¯ãããŸããããã€ã³ã¿ãŒãããäžã®å®éã®ã¢ã¯ãã£ããã£ãããã¯ããã«é«ãããšããããããŸãã ä»ã¯ãã®ãŸãŸã«ããŠãããŸãããã
ãããã£ãŠããªã¯ãšã¹ãã®åŒ·åºŠã¯ãå€ãã®å Žåãããã€ã¹ã«å¿ èŠãªæ©èœã«é¢é£ããŠããŸãã ãããã掻åãããããã人ãã¡ãçºèŠãããŸããã
æåã«
ã¯ã€ã€ã¬ã¹ ã€ã³ã¿ãŒãããããªã³ã«ãªã£ãŠãããšãã¯ãå šå¡ããŸã ç ã£ãŠãããããã©ã®ãªã¯ãšã¹ããæåã«ãããã¯ãŒã¯ã«éä¿¡ããããã確èªã§ããŸãã ãããã£ãŠã6 æ 50 åã«ã€ã³ã¿ãŒãããããªã³ã«ãªããæåã® 60 åéã§æ¯æ¥ XNUMX ã®ãã¡ã€ã³ãããŒãªã³ã°ãããŸãã
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 ã¯ãWLAN æ¥ç¶ã«ãã°ã€ã³ ããŒãžãååšãããã©ããããã§ãã¯ããŸãã
Citrix ã¯ãã¢ããªã±ãŒã·ã§ã³ãã¢ã¯ãã£ãã«å®è¡ãããŠããªãå Žåã§ãããµãŒããŒã« ping ãéä¿¡ããŸãã
ã·ãã³ããã¯ã¯èšŒææžãæ€èšŒããŸãã
Mozilla ã¯ã¢ããããŒãããã§ãã¯ããŸãããèšå®ã§ãããè¡ããªãããæå®ããŸããã
mmo.de ã¯ã²ãŒã ãµãŒãã¹ã§ãã ããããããªã¯ãšã¹ã㯠Facebook ãã£ããã«ãã£ãŠéå§ãããŸãã ç§ãã¡ã¯ãããã¯ããŸãã
Apple ã¯ãã¹ãŠã®ãµãŒãã¹ãæå¹åããŸãã api-glb-fra.smoot.apple.com - 説æããå€æãããšããã¿ã³ã®ã¯ãªãã¯ã¯ãã¹ãŠãæ€çŽ¢ãšã³ãžã³æé©åã®ç®çã§ããã«éä¿¡ãããŸãã éåžžã«çãããã§ãããæ©èœã«é¢é£ããŠããŸãã ãã®ãŸãŸã«ããŠãããŸãã
以äžã¯ãmicrosoft.com ãžã®ãªã¯ãšã¹ãã®é·ããªã¹ãã§ãã XNUMX çªç®ã®ã¬ãã«ããå§ãŸããã¹ãŠã®ãã¡ã€ã³ããããã¯ããŸãã
äžçªæåã®ãµããã¡ã€ã³ã®æ°
ã€ãŸããã¯ã€ã€ã¬ã¹ã€ã³ã¿ãŒãããããªã³ã«ããŠããæåã®10åéã§ãã
æãå€ãã®ãµããã¡ã€ã³ã調æ»ããã®ã¯ iOS 㧠- 32 ã§ããã次㫠Android - 24ã次㫠Windows - 15ãæåŸã« Blackberry - 9 ã§ããã
Facebook ã¢ããªã±ãŒã·ã§ã³ã ã㧠10 ã®ãã¡ã€ã³ãããŒãªã³ã°ããSkype 㯠9 ã€ã®ãã¡ã€ã³ãããŒãªã³ã°ããŸãã
æ å ±æº
åæã®ãœãŒã¹ã¯ãbind9 ããŒã«ã« ãµãŒã㌠ãã° ãã¡ã€ã«ã§ã次ã®åœ¢åŒãå«ãŸããŠããŸãã
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)
ãã¡ã€ã«ã¯ sqlite ããŒã¿ããŒã¹ã«ã€ã³ããŒããããSQL ã¯ãšãªã䜿çšããŠåæãããŸããã
ãµãŒããŒã¯ãã£ãã·ã¥ãšããŠæ©èœããèŠæ±ã¯ã«ãŒã¿ãŒããéä¿¡ããããããèŠæ±ã¯ã©ã€ã¢ã³ãã¯åžžã« XNUMX ã€ã«ãªããŸãã åçŽåãããããŒãã«æ§é ã§ååã§ãã ã¬ããŒãã«ã¯ããªã¯ãšã¹ãã®æå»ããªã¯ãšã¹ãèªäœãããã³ã°ã«ãŒãåã®ããã®ç¬¬ XNUMX ã¬ãã«ã®ãã¡ã€ã³ãå¿
èŠã§ãã
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)
);
åºå
ãããã£ãŠããã¡ã€ã³ ããŒã ãµãŒããŒã®ãã°ãåæããçµæã50 件ãè¶ ããã¬ã³ãŒããæ€é²ãããããã㯠ãªã¹ãã«è¿œå ãããŸããã
äžéšã®ã¯ãšãªã®å¿
èŠæ§ã¯ãœãããŠã§ã¢ ã¡ãŒã«ãŒã«ãã£ãŠååã«èª¬æãããŠãããèªä¿¡ãäžããŸãã ãããã掻åã®å€ãã¯æ ¹æ ããªããçããããã®ã§ãã
åºæïŒ habr.com