แกแแฎแแแก แ แแฃแขแแ แก (แแ แจแแแแฎแแแแแจแ FritzBox) แจแแฃแซแแแ แแแแ แ แฉแแฌแแ แแก: แ แแแแแแ แขแ แแคแแแ แ แแแแก แแแแแก, แแแ แ แ แกแแฉแฅแแ แแ แแ แแก แแแแแแจแแ แแแฃแแ แแ แ.แจ. แแแแแแแก แกแแฎแแแแแแก แกแแ แแแ แ (DNS) แแแแแแฃแ แฅแกแแแจแ แแแแแฎแแแ แ แแแแแแ แ แ แแแแแแแแแ แฃแชแแแแ แแแแฆแแแแแแก แฃแแแ.
แกแแแ แแ แฏแแแจแ, DNS-แแ แแแแแแแแ แแแแแแแ แแแแฎแแแแ แกแแฎแแแก แฅแกแแแแ: แแแแแแขแ แกแแฉแฅแแ แ, แกแขแแแแแฃแ แแแ แแ แแแ แแแแแ.
แฅแแแแแ แแแชแแแฃแแแ แแแแแ แแแ, แ แแแแแแช แแฉแแแก แแแแฎแแแแก แแ แกแแญแแ แแแแก แแแแก แแแแแแแก, แแฃ แ แ แฎแแแแแแ. แจแแแแแแแ แฃแแแ แคแแแขแ แแแก แชแแแแแ แแ แกแแแฃแจแแ แแแแฎแแแแแแก แแแแแแแก แกแแฎแแแแแแก แกแแ แแแ แแแแ.
แ แแขแแ แแแแแฎแแแ 60 แแฃแแแแแแแ แแแแแแ แงแแแแแแฆแ, แ แแชแ แงแแแแแก แฏแแ แแแแแ แกแซแแแแแก?
แงแแแแแแฆแแฃแ แแ แแฅแขแแฃแ แกแแแแแแจแ 440 แฃแชแแแแ แแแแแแแก แแแแแแแแฎแแ แฎแแแแ. แแแ แแ แแแ แแกแแแ แแ แ แแก แแแแแแแแ?
แแแแฎแแแแแแแก แกแแจแฃแแแ แ แแแแแแแแ แแฆแแจแ แกแแแแจแ
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.
แฉแแแแแแแแแแ แแแขแแแกแแฃแ แแ แแแแแแแแฎแฃแแ แแแแแแแแ. แแแขแแแกแแแแแ แแแแแกแแแฆแแ แแแ แแกแแแ แแแ แแแแขแ แแแแ, แ แแแแ แแชแแ แแแแฎแแแแแก แ แแแแแแแแ แแฆแแจแ, แแฅแขแแแแแแก แแฆแแแแแก แ แแแแแแแแ แแ แแฆแแก แ แแแแแ แกแแแแจแ แจแแแแจแแแก แแกแแแ.
แงแแแแ แแแกแแแแแแแแ แแญแแแแขแแแแแ แแงแ แกแแแจแ.
แแแขแแแกแแฃแ แแ แแแแแแแแฎแฃแแ แแแแแแแแ
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 แฅแกแแแจแ แจแแกแแแ, แแก แฏแแ แแฉแแแแแแก แจแแกแแแแก แแแแ แแก. แแแแแแแ แแแกแแแแแ แแ แแ แแก, แ แแขแแ แฃแแแ แแแฎแแแก แแแกแแแแ แแแก แแแแแ แแกแ แฎแจแแ แแ, แแแแ แแ แแแฅแแแแแแ แแแแแแ แแ แแก แแฆแฌแแ แแแ แแฌแแ แแแแแแแก แแแแ .
แกแแแแแ. แแ แแ แแแ แแแแก แแแฅแแแแแแแแ แญแแแก แแกแแแแกแแ: แแก แแแแแแก แแ แแ แแซแแแแก แแแแก แฃแแ แแแแ แแแแแแแก แฃแคแแแแแก แแแแแแแแแก แแแแแแจแ, แฅแแแแก แฃแแแ แแ แขแ แแคแแแก แฅแกแแแจแ, แแแแแแแแก 10 แแแแแแก แงแแแแ 4 แฌแฃแแจแ. แแแแแแแแ แแก แแแแฎแแ แชแแแแแแแกแแก แแแขแแ แแแข แแแแจแแ แ แแฃแแแแแแ แแจแแแแ, แ แแชแ แฃแแแแแกแ แแ แแ แแก. แฏแแ แฏแแ แแแแ แแฃแชแแแแแแแแ, แแกแ แ แฉแแแ.
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-แแ. แแแแแแแแแ แแแแแแฌแแแ แขแแแแแขแแ แแแแแ แจแแขแงแแแแแแแแแแก แแฎแแแ แฃแแ แแแแแ แแแ, แ แแช แกแฃแแแแฃแ แแ แแแฉแแแแแแ. แแแแ แแ playstore-แแ แจแแฌแงแแแขแ แแฃแจแแแแ, แแแแแ แแแแแแแแแแ แแแ.
cloudflare.com - แฌแแ แแ, แ แแ แฃแงแแแ แ แฆแแ แแแแ แแ แแแแแแแ, แแแแ แก แฌแแ แแ แกแแแฃแแแ แแแแแ. แแแแแแแก แแแแแแแก แแแขแแแกแแแแแ แแแแแแแ แแ แแ แแก แแแแแแ, แ แแช แฎแจแแ แแ แแแแ แแ แแฆแแแแขแแแ แ แแแแฃแ แแฅแขแแแแแแก แแแขแแ แแแขแจแ. แแแแแ แแแแขแแแแ แแก แแฎแแ.
แแแ แแแแ, แแแแฎแแแแแแแก แแแขแแแกแแแแแ แฎแจแแ แแ แแแแแแจแแ แแแฃแแแ แแแฌแงแแแแแแแแแแก แกแแญแแ แ แคแฃแแฅแชแแแแแแแแ. แแแแ แแ แแกแแแแช แแฆแแแแฉแแแแก, แแแแช แแฅแขแแฃแ แแแแ แแแแแแญแแ แแ.
แแแ แแแแแแ
แ แแแแกแแช แฃแแแแแแ แแแขแแ แแแขแ แฉแแ แแฃแแแ, แงแแแแแก แฏแแ แแแแแ แกแซแแแแแก แแ แจแแกแแซแแแแแแแ แแแฎแแ, แ แแแแแ แแแแฎแแแแแแ แแแแแแแแแ แแแ แแแ แ แแแจแ แฅแกแแแจแ. แแกแ แ แแ, 6:50 แกแแแแแ แแ แแแแแ แแแขแแ แแแขแ แแ แแแ แแแ แแ แฌแฃแแจแ แงแแแแแแฆแแฃแ แแ 60 แแแแแแแก แแแแแแแแฎแแ แฎแแแแ:
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 แแงแแแแแก แแแแแก แกแแ แแแ แก, แแแฃแฎแแแแแแ แแแแกแ, แ แแ แแแแแแแชแแ แแฅแขแแฃแ แแ แแ แแฃแจแแแแก.
Symantec แแแแฌแแแแก แกแแ แแแคแแแแขแแแก.
Mozilla แแแแฌแแแแก แแแแแฎแแแแแแก, แแฃแแชแ แแแ แแแแขแ แแแจแ แแแฎแแแ แแ แแแแแแแแแแแแ แแก.
mmo.de แแ แแก แกแแแแแแจแ แกแแ แแแกแ. แกแแแแ แแฃแแแ แแแแฎแแแแ แแแแชแแ แแแฃแแแ แคแแแกแแฃแฅ แฉแแขแแ. แแแแแแแแ.
Apple แแแแแฅแขแแฃแ แแแก แแแแแก แงแแแแ แกแแ แแแกแก. api-glb-fra.smoot.apple.com - แแฆแฌแแ แแแแแแ แแแแกแฏแแแแแ, แฆแแแแแแ แงแแแแแ แแแฌแแแแฃแแแแ แแแแแแแแแ แแฅ แกแแซแแแแ แกแแกแขแแแแก แแแขแแแแแแชแแแก แแแแแแแแกแแแแก. แซแแแแแ แกแแแญแแแ, แแแแ แแ แแแแแแจแแ แแแฃแแแ แคแฃแแฅแชแแแแแแแแ. แฉแแแ แแแก แแขแแแแแ.
แฅแแแแแ แแแชแแแฃแแแ แแแแฎแแแแแแแก แแ แซแแแ แกแแ microsoft.com-แแกแแแแก. แฉแแแ แแแแแแแแ แงแแแแ แแแแแแก แแแกแแแ แแแแแแแ แแแฌแงแแแฃแแ.
แแแ แแแแแแ แฅแแแแแแแแแแแก แ แแแแแแแแ
แแกแ แ แแ, แฃแแแแแแ แแแขแแ แแแขแแก แฉแแ แแแแก แแแ แแแแ 10 แฌแฃแแ.
iOS แแแแแแแแฎแแแจแ แงแแแแแแ แแแขแ แฅแแแแแแแแแ - 32. แแแฐแงแแแแ Android - 24, แจแแแแแ Windows - 15 แแ แแแแแก Blackberry - 9.
แแแ แขแ แคแแแกแแฃแฅแแก แแแแแแแชแแ แแแแแแแแฎแแแก 10 แแแแแแก, แกแแแแแ 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 แแแแฎแแแแแแแก แแแแแงแแแแแแ.
แกแแ แแแ แ แแแฅแแแแแแก แ แแแแ แช แฅแแจแ; แแแแฎแแแแแแ แแแแแก แ แแฃแขแแ แแแแ, แแแแขแแ แงแแแแแแแแก แแ แแก แแ แแ แแแแฎแแแแแก แแแแแแขแ. แกแแแแแ แแกแแ แชแฎแ แแแแก แแแแแ แขแแแแแฃแแ แกแขแ แฃแฅแขแฃแ แ, แ.แ. แแแแแ แแจแ แแแแแฎแแแก แแแแฎแแแแแก แแ แแก, แแแแแ แแแแฎแแแแแก แแ แแแแ แ แแแแแก แแแแแแก แแแฏแแฃแคแแแแกแแแแก.
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-แแ แแแขแ แฉแแแแฌแแ แ แชแแแแฃแ แแก แแ แแแแ แกแแแจแ แแแแแแกแแ.
แแแแแแ แแ แจแแแแแฎแแแก แแฃแชแแแแแแแแ แแแ แแแ แแ แแก แแฆแฌแแ แแแ แแ แแแ แแแฃแแ แฃแแ แฃแแแแแงแแคแแก แแฌแแ แแแแแแแแแก แแแแ แแ แจแแแแแแแแแก แแแแแแก. แแฃแแชแ, แกแแฅแแแแแแแแก แแแแ แแแฌแแแ แฃแกแแคแฃแซแแแ แแ แกแแแญแแแ.
แฌแงแแ แ: www.habr.com