ααααααααααα (αααα»αααααΈααα 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 ααααΌαααΆααΉααααα αΆααααααα αΌαα ααΆαα·αα αααΆααααααΆα ααα»α’αααΈααΆαααΆα’αΆααααααΆααα½αααααααΌαααΆα ping ααΉαααΆααααΌα αααα ααα»ααααααααααΆααααααααΌαααΆααα·αααααΆαααΆαα αααΆααααααααα»αα αα»αααα·αα
skype α αααααααΆααααααααααα·ααΈαααααΊαααααααα ααΉααααααΌααα½ααααα ααΆααΆαααααα½α αα·ααα·αα’αα»ααααΆαα±αααααα½αααΆααααΌαααΆααααααΆαααα αααα»ααααΆαααΆααα·α αα αααααΎαα ααΆα αααα αααΎααα ααΎαααααΆα ααΆα 10 ααααααααΆαα 4 ααΆααΈααααα αα αααααααΎααΆαα α ααΆααΈααα’αΌ ααΆααααααΆααα’ααΈααΊαα·ααααααααΌα αα ααααααααΆαα·αα’αΆα αααααΎαααΆααα»αα αααααΆααααααααααΆα αΆαααΆα αααΌα ααααααΆαα ααααΆαα
upload.fp.measure.office.com - αααα
αα
Office 365 αααα»ααα·αα’αΆα
αααααααααΆααα·αααααΆααααααααΆαααα
browser.pipe.aria.microsoft.com - αααα»ααα·αα’αΆα
αααααααααΆααα·αααααΆααααααααΆαααα
ααΎαααΆααΆααααΆααααΈαα
connect.facebook.net - αααααα·ααΈααααααΆαα ααααααα»αα αα αααα
mediator.mail.ru ααΆααα·ααΆαααααααΎααΆααα’αααααααΆααααα mail.ru ααΆααααα αΆαααααααΆαααααααααΆαααααΆαααΆαα·ααααααα αα·αα’ααααααααΌααααα·αα·ααΆα αααΎα ααααααααΆαα±ααααΆαααΆααα·ααα»αα α·αααα ααα mail.ru ααααΌαααΆαααααΎααΆαααααα»ααα αααα»ααααααΈαααα α
google-analytics.com - αα·ααααααΆααααααα»αααΆαααααα§ααααααα ααΌα
ααααααΎαααΆααΆααααΆα
doubleclick.net - ααΆααααΆαα
α»α
ααααΆαααΆαα·αααααααα ααΎαααΆααΆααα
ααααΎααΆα αααΎαα αΌααα ααΆαα googleapis.com α ααΆααααααααΆααααΆαααΆααα αααααΆααα·αααΆαααααΈααα ααΎαα»αααααΌααααααααααααΈαααΆα αααα αΆααααΌα ααΆαααα½ααααααΆαααααα»αα ααα»ααααβα αΆαβαααβααΆαβαααβααααΎαααΆα ααΌα ααααβααΎαβαααβαα·αβααΆβαα·αα
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 αααα»α pinging server αααααααα½α αααααΈααΆαααααα·ααΈαα·αααααΎαααΆααααΆαααααααααααα
Symantec αααααααααΆαααα·ααααΆααααααα
Mozilla αα·αα·αααααΎαααΆαα’αΆααααα αααααΈααΆαα
αααα»αααΆαααααααααα»αααΆααα»ααα·αααααΎαααααααααααα
mmo.de ααΊααΆααααΆαααααααα ααααα ααΆαα αααΎαααααααΆααααΎααααΌαααΆααααα½α ααααΎααααααΆαααααααΆαα ααααααα»αα ααΎαααΆααΆααα
Apple ααΉαααΎαααααΎαααΆαααααΆααααααΆααα’αααααααααα½αα api-glb-fra.smoot.apple.com - αα·αα·α αααααααααΆααα·αααααΆ ααΆααααΆαα α»α αααΌαα»αααααΌαααΆαααααΎαα ααΈααααααααΆααααααααααααααΎαααααα·αααααΆααααΆαααΈααααααααα ααΆαααΆαααααααααααα ααα»ααααααΆααααααΉααα»αααΆαα ααΎααα»αααΆα ααα
ααΆαααααααααααΊααΆαααααΈααααΎααααααα½ααα ααΆαα microsoft.com α ααΎαααΆααΆαααααααΆααα’αααααα αΆααααααΎαααΈααααα·αααΈααΈα
α
ααα½ααααααααααααΌααααα»αα
ααΌα
αααα 10 ααΆααΈααααΌαααααΆαααΎαα’ααΈαααΊαα·αα₯αααααα
ααααααααααααα·ααααα·ααΆα iOS ααααΎααΆααααααααα·ααΎαααααα
αααΎααααα»α - 32. ααΆαααΈαααααααα Android - 24 αααααΆαααα Windows - 15 αα·αα
α»αααααα Blackberry - 9 α
αααααα·ααΈ facebook ααα―αααααΎααΆααααααααα· 10 domains, skype polls 9 domainsα
αααααααααααααΆα
ααααααααααΆααααΆααα·ααΆαααΊααΆα―αααΆααααααα ααα»αααΆαααΈαααααΌαααααΆα 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 α
αααΆαααΈαααααΎααα½ααΆααααΆαααααααΆαα ααααΎααΆαααααΈααααααα ααΌα
αααααααααααΆαα’αα·αα·ααααααΎαα½αα αα
ααΆααααααααααΆααΆαααΆααααααΊαααααααααΆαα, i.e. αααΆαααΆαααααΆαααΆααααααααΆααααααΎ ααααΎααααααα½αα―α αα·ααααααααα·αααΈααΈααααααΆααααΆαααΆααααΆαααα»αα
ααΆααΆα 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