ื•ื•ื™ ื˜ื•ื˜ ื“ื™ ื”ื™ื™ื ืื™ื ื˜ืขืจื ืขื˜ ืœืขื‘ืŸ ืื•ืŸ ืคืขืœื“ ื ืึธืžืขืŸ ืกืขืจื•ื•ืขืจ ืกื˜ืึทื˜ื™ืกื˜ื™ืง?

ื ื”ื™ื™ื ืจืึทื•ื˜ืขืจ (ืื™ืŸ ื“ืขื ืคืึทืœ FritzBox) ืงืขื ืขืŸ ืจืขืงืึธืจื“ื™ืจืŸ ืึท ืคึผืœืึทืฅ: ื•ื•ื™ ืคื™ืœ ืคืึทืจืงืขืจ ื’ื™ื™ื˜ ื•ื•ืขืŸ, ื•ื•ืขืจ ืื™ื– ืงืึธื ื ืขืงื˜ืขื“ ืžื™ื˜ ื•ื•ืึธืก ื’ื™ื›ืงื™ื™ึทื˜, ืืื–"ื• ื•. ื ืคืขืœื“ ื ืึธืžืขืŸ ืกืขืจื•ื•ืขืจ (ื“ื ืก) ืื•ื™ืฃ ื“ื™ ื”ื™ื’ืข ื ืขืฅ ื’ืขื”ืึธืœืคึฟืŸ ืžื™ืจ ื’ืขืคึฟื™ื ืขืŸ ื•ื•ืึธืก ืื™ื– ืคืึทืจื‘ืึธืจื’ืŸ ื”ื™ื ื˜ืขืจ ื“ื™ ืื•ืžื‘ืึทืงืึทื ื˜ ืจื™ืกื™ืคึผื™ืึทื ืฅ.

ืงื•ื™ืœืขืœื“ื™ืง, DNS ื”ืื˜ ืึท positive ืคึผืจืึทืœ ืื•ื™ืฃ ื“ื™ ื”ื™ื™ื ื ืขืฅ: ืขืก ื”ืื˜ ืฆื•ื’ืขืœื™ื™ื’ื˜ ื’ื™ื›ืงื™ื™ึทื˜, ืคืขืกื˜ืงื™ื™ึทื˜ ืื•ืŸ ืžืึทื ื™ื“ื–ืฉืึทื‘ื™ืœื™ื˜ื™.

ื•ื ื˜ืขืจ ืื™ื– ืึท ื“ื™ืึทื’ืจืึทืžืข ื•ื•ืึธืก ื”ืึธื˜ ืื•ื™ืคื’ืขื•ื•ืขืงื˜ ืคึฟืจืื’ืŸ ืื•ืŸ ื“ื™ ื ื•ื™ื˜ ืฆื• ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื•ื•ืึธืก ืื™ื– ื’ืขืฉืขืขื ื™ืฉ. ื“ื™ ืจืขื–ื•ืœื˜ืึทื˜ืŸ ืฉื•ื™ืŸ ืคื™ืœื˜ืขืจ ืื•ื™ืก ื‘ืึทื•ื•ื•ืกื˜ ืื•ืŸ ืืจื‘ืขื˜ืŸ ืจื™ืงื•ื•ืขืก ืฆื• ืคืขืœื“ ื ืึธืžืขืŸ ืกืขืจื•ื•ืขืจืก.

ืคืืจื•ื•ืืก ื–ืขื ืขืŸ 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'))

ื‘ื™ื™ ื ืึทื›ื˜, ื•ื•ื™ื™ืจืœื™ืก ืึทืงืกืขืก ืื™ื– ืคืึทืจืงืจื™ืคึผืœื˜ ืื•ืŸ ืžื™ื˜ืœ ื˜ืขื˜ื™ืงื™ื™ื˜ ืื™ื– ื“ืขืจื•ื•ืึทืจื˜, ื“.ื”. ืขืก ืื™ื– ืงื™ื™ืŸ ืคึผืึธืœืœื™ื ื’ ืคึฟืึทืจ ืื•ืžื‘ืึทืงืึทื ื˜ ื“ืึธื•ืžื™ื™ื ื–. ื“ืขื ืžื™ื˜ืœ ืึทื– ื“ื™ ื’ืจืขืกื˜ืข ื˜ืขื˜ื™ืงื™ื™ื˜ ืงื•ืžื˜ ืคื•ืŸ ื“ืขื•ื•ื™ืกืขืก ืžื™ื˜ ืึธืคึผืขืจื™ื™ื˜ื™ื ื’ ืกื™ืกื˜ืขืžืขืŸ ืึทื–ืึท ื•ื•ื™ ืึทื ื“ืจื•ื™ื“, ื™ืึธืก ืื•ืŸ ืึธื–ืฉืขื ื™ืฆืข ืึทืก.

ื–ืืœ ืก ืจืฉื™ืžื” ื“ื™ ื“ืึธื•ืžื™ื™ื ื– ื•ื•ืึธืก ื–ืขื ืขืŸ ืคึผืึธื•ืœื“ ื™ื ื˜ืขื ืกื™ื•ื•ืœื™. ื“ื™ ื™ื ื˜ืขื ืกื™ื˜ื™ ื•ื•ืขื˜ ื–ื™ื™ืŸ ื‘ืืฉืœืืกืŸ ื“ื•ืจืš ืคึผืึทืจืึทืžืขื˜ืขืจืก ืึทื–ืึท ื•ื•ื™ ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ืจื™ืงื•ื•ืขืก ืคึผืขืจ ื˜ืึธื’, ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ื˜ืขื’ ืคื•ืŸ ื˜ืขื˜ื™ืงื™ื™ื˜ ืื•ืŸ ืื™ืŸ ื•ื•ื™ ืคื™ืœืข ืฉืขื” ืคื•ืŸ ื“ืขื ื˜ืึธื’ ื–ื™ื™ ื–ืขื ืขืŸ ื‘ืืžืขืจืงื˜.

ืึทืœืข ื“ื™ ื“ืขืจื•ื•ืึทืจื˜ ืกืึทืกืคึผืขืงืฅ ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ ืื•ื™ืฃ ื“ืขืจ ืจืฉื™ืžื”.

ื™ื ื˜ืขื ืกื™ื•ื•ืœื™ ืคึผืึธื•ืœื“ ื“ืึธื•ืžื™ื™ื ื–

ื•ื•ื™ ื˜ื•ื˜ ื“ื™ ื”ื™ื™ื ืื™ื ื˜ืขืจื ืขื˜ ืœืขื‘ืŸ ืื•ืŸ ืคืขืœื“ ื ืึธืžืขืŸ ืกืขืจื•ื•ืขืจ ืกื˜ืึทื˜ื™ืกื˜ื™ืง?

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 - ืจืขืคืขืจืก ืฆื• ืึธืคืคื™ืกืข 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

ืคื™ืจืขืคืึธืงืก ื˜ืฉืขืงืก ื“ื™ WLAN ืคึฟืึทืจื‘ื™ื ื“ื•ื ื’ ืคึฟืึทืจ ื“ืขื ื‘ื™ื™ึทื–ื™ื™ึทืŸ ืคื•ืŸ ืึท ืœืึธื’ื™ืŸ ื‘ืœืึทื˜.
Citrix ืคึผื™ื ื’ ื–ื™ื™ืŸ ืกืขืจื•ื•ืขืจ ืืคื™ืœื• ื›ืึธื˜ืฉ ื“ื™ ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ ืื™ื– ื ื™ืฉื˜ ืึทืงื˜ื™ื•ื•ืœื™ ืคืœื™ืกื ื“ื™ืง.
Symantec ื•ื•ืขืจืึทืคื™ื™ื– ืกืขืจื˜ื™ืคื™ืงืึทืฅ.
ืžืึธื–ื™ืœืœืึท ื˜ืฉืขืงืก ืคึฟืึทืจ ื“ืขืจื”ื™ื™ึทื ื˜ื™ืงื•ื ื’ืขืŸ, ื›ืึธื˜ืฉ ืื™ืŸ ื“ื™ ืกืขื˜ื˜ื™ื ื’ืก ืื™ืš ื’ืขื‘ืขื˜ืŸ ื ื™ืฉื˜ ืฆื• ื˜ืึธืŸ ื“ืึธืก.

mmo.de ืื™ื– ืึท ื’ื™ื™ืžื™ื ื’ ื“ื™ื ืกื˜. ืจื•ื‘ึฟ ืžืกืชึผืžื ื“ื™ ื‘ืงืฉื” ืื™ื– ื™ื ื™ืฉื™ื™ื™ื˜ื™ื“ ื“ื•ืจืš ืคืึทืกืขื‘ืึธืึธืง ืฉืžื•ืขืกืŸ. ืžื™ืจ ื‘ืœืึธืง.

ืขืคึผืœ ื•ื•ืขื˜ ืึทืงื˜ืึทื•ื•ื™ื™ื˜ ืึทืœืข ื–ื™ื™ึทืŸ ื‘ืึทื“ื™ื ื•ื ื’ืก. api-glb-fra.smoot.apple.com - ืื•ื™ื‘ ืžืฉืคื˜ืŸ ืœื•ื™ื˜ ื“ื™ ื‘ืึทืฉืจื™ื™ึทื‘ื•ื ื’, ื™ืขื“ืขืจ ืงืœื™ืงื™ื ื’ ืงื ืขืคึผืœ ืื™ื– ื’ืขืฉื™ืงื˜ ื“ืึธ ืคึฟืึทืจ ื–ื•ื›ืŸ ืžืึธื˜ืึธืจ ืึทืคึผื˜ืึทืžืึทื–ื™ื™ืฉืึทืŸ ืฆื•ื•ืขืงืŸ. ื”ืขื›ืกื˜ ืกืึทืกืคึผื™ืฉืึทืก, ืึธื‘ืขืจ ืฉื™ื™ืš ืฆื• ืคืึทื ื’ืงืฉืึทื ืึทืœื™ื˜ื™. ืžื™ืจ ืœืึธื–ืŸ ืขืก.

ื“ื™ ืคืืœื’ืขื ื“ืข ืื™ื– ืึท ืœืึทื ื’ ืจืฉื™ืžื” ืคื•ืŸ ืจื™ืงื•ื•ืขืก ืฆื• microsoft.com. ืžื™ืจ ืคืึทืจืฉืคึผืึทืจืŸ ืึทืœืข ื“ืึธื•ืžื™ื™ื ื– ืกื˜ืึทืจื˜ื™ื ื’ ืคื•ืŸ ื“ื™ ื“ืจื™ื˜ ืฉื˜ืึทืคึผืœ.

ื ื•ืžืขืจ ืคื•ืŸ ื–ื™ื™ืขืจ ืขืจืฉื˜ืขืจ ืกื•ื‘ื“ืึธืžืึทื™ื ืก
ื•ื•ื™ ื˜ื•ื˜ ื“ื™ ื”ื™ื™ื ืื™ื ื˜ืขืจื ืขื˜ ืœืขื‘ืŸ ืื•ืŸ ืคืขืœื“ ื ืึธืžืขืŸ ืกืขืจื•ื•ืขืจ ืกื˜ืึทื˜ื™ืกื˜ื™ืง?

ืึทื–ื•ื™, ื“ืขืจ ืขืจืฉื˜ืขืจ 10 ืžื™ื ื•ื˜ ืคื•ืŸ ื˜ื•ืจื ื™ื ื’ ืื•ื™ืฃ ื“ื™ ื•ื•ื™ื™ืจืœื™ืก ืื™ื ื˜ืขืจื ืขื˜.
ื™ืึธืก ืคึผืึธืœืœืก ื“ื™ ืžืขืจืกื˜ ืกื•ื‘ื“ืึธืžืึทื™ื ืก - 32. ื ืื›ื’ืขื’ืื ื’ืขืŸ ื“ื•ืจืš ืึทื ื“ืจื•ื™ื“ - 24, ื“ืขืžืึธืœื˜ Windows - 15 ืื•ืŸ ืœืขืกืึธืฃ ื‘ืœืึทืงืงื‘ืขืจืจื™ - 9.
ื“ื™ ืคืึทืกืขื‘ืึธืึธืง ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ ืึทืœื™ื™ืŸ ืคึผืึธืœืœืก 10 ื“ืึธื•ืžื™ื™ื ื–, ืกืงื™ืคึผืข ืคึผืึธืœืœืก 9 ื“ืึธื•ืžื™ื™ื ื–.

ื“ืขืจ ืžืงื•ืจ ืคื•ืŸ ืื™ื ืคึฟืึธืจืžืึทืฆื™ืข

ื“ืขืจ ืžืงื•ืจ ืคึฟืึทืจ ื“ื™ ืึทื ืึทืœื™ืกื™ืก ืื™ื– ื’ืขื•ื•ืขืŸ ื“ื™ ื‘ื™ื ื“9 ื”ื™ื’ืข ืกืขืจื•ื•ืขืจ ืงืœืึธืฅ ื˜ืขืงืข, ื•ื•ืึธืก ื›ึผื•ืœืœ ื“ื™ ืคืืœื’ืขื ื“ืข ืคึฟืึธืจืžืึทื˜:

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 ืงื•ื•ื™ืจื™ื–.
ื“ืขืจ ืกืขืจื•ื•ืขืจ ืึทืงื˜ ื•ื•ื™ ืึท ืงืึทืฉ; ืจื™ืงื•ื•ืขืก ืงื•ืžืขืŸ ืคื•ืŸ ื“ื™ ืจืึทื•ื˜ืขืจ, ืึทื–ื•ื™ ืขืก ืื™ื– ืฉื˜ืขื ื“ื™ืง ืื™ื™ืŸ ื‘ืงืฉื” ืงืœื™ืขื ื˜. ื ืกื™ืžืคึผืœืึทืคื™ื™ื“ ื˜ื™ืฉ ืกื˜ืจื•ืงื˜ื•ืจ ืื™ื– ื’ืขื ื•ื’, ื“.ื”. ื“ืขืจ ื‘ืึทืจื™ื›ื˜ ืจื™ืงื•ื•ื™ื™ืขืจื– ื“ื™ ืฆื™ื™ื˜ ืคื•ืŸ ื“ื™ ื‘ืงืฉื”, ื“ื™ ื‘ืขื˜ืŸ ื–ื™ืš ืื•ืŸ ื“ื™ ืฆื•ื•ื™ื™ื˜ืข ืžื“ืจื’ื” ืคืขืœื“ ืคึฟืึทืจ ื’ืจื•ืคึผื™ื ื’.

ื“ื“ืœ ื˜ื™ืฉืŸ

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

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’