A home router (in this case, FritzBox) can register a lot: how much traffic when it goes, who is connected at what speed, etc. To find out what is hidden under incomprehensible addressees, the domain name server (DNS) on the local network helped me.
In general, DNS has had a positive impact on the home network: it has added speed, stability, and manageability.
Below is a diagram that raised questions and the need to understand what is happening. The results have already filtered known and working queries to domain name servers.
Why are 60 obscure domains polled every day while everyone else is sleeping?
Every day, 440 unknown domains are polled during active time. Who are they and what do they do?
Average number of requests per day by hour
SQL query report
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'))At night, wireless access is turned off and device activity is expected, i.e. no polling of obscure domains. This means that the greatest activity comes from devices with such operating systems as Android, iOS and Blackberry OS.
Let's display the domains that are intensively polled. The intensity will be determined by parameters such as the number of requests per day, the number of days of activity and at what hours of the day were seen.
All the expected suspects were on the list.
Highly Polled Domains
SQL query report
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 20We block iss.blackberry.com and iceberg.blackberry.com, which the manufacturer will justify for security reasons. Result: when trying to connect to the WLAN, it shows the login page and never connects anywhere else. Unlock.
detectportal.firefox.com is the same mechanism, only implemented in the Firefox browser. If it is necessary to log in to the WLAN network, it will show the login page first. It is not entirely clear why ping the address so often, but the mechanism is clearly described by the manufacturer.
skype. The actions of this program are similar to a worm: it hides and does not let itself be killed in the taskbar, generates a lot of traffic on the network, pings 10 domains every 4 minutes. When making a video call, the Internet connection constantly mobilizes, when it cannot be any better. As long as it is necessary, therefore it remains.
upload.fp.measure.office.com - refers to Office 365, I did not find a decent description.
browser.pipe.aria.microsoft.com - I did not find a decent description.
We block both.
connect.facebook.net is the facebook chat app. Remains.
mediator.mail.ru An analysis of all requests for the mail.ru domain showed the presence of a huge number of advertising resources and statistics collectors, which causes mistrust. The mail.ru domain is sent entirely to the blacklist.
google-analytics.com - does not affect the functionality of devices, so we block it.
doubleclick.net - counts ad clicks. We block.
Many requests go to googleapis.com. The blocking has led to a joyful disabling of short messages in the tablet, which I find stupid. But the playstore stopped working, so we unlock it.
cloudflare.com - they write that they love open source and, in general, write a lot about themselves. The intensity of the domain poll is not entirely clear, which is often much higher than the activity itself on the Internet. Let's leave it for now.
Thus, the intensity of requests is often related to the required functionality of devices. But those who overdid it with activity were also found.
Very first
At the moment the wireless Internet is turned on, they are still sleeping and it is possible to see which requests are sent to the network first. So, at 6:50, the Internet turns on and in the first ten-minute period, 60 domains are polled daily:
SQL query report
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 checks the WLAN connection for a login page.
Citrix is ββpinging its server even though the application is not actively running.
Symantec verifies certificates.
Mozilla checks for updates even though it was asked in the settings not to do so.
mmo.de is a gaming service. Most likely the request initiates a facebook chat. We block.
Apple activates all of its services. api-glb-fra.smoot.apple.com - judging by the description, each button click is sent here for search engine optimization purposes. Highly suspicious, but related to functionality. We leave.
What follows is a long list of hits to microsoft.com. We block all domains, starting from the third level.
The number of subdomains of the very first
So, the first 10 minutes of turning on the wireless Internet.
iOS polls the most subdomains - 32. It is followed by Android - 24, then Windows - 15 and the latest Blackberry - 9.
The facebook application alone polls 10 domains, skype polls 9 domains.
The source of information
The source for the analysis was the log file of the local bind9 server, which contains the following format:
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)
The file was imported into sqlite database and parsed using SQL queries.
The server acts as a cache, requests come from the router, so the request client is always the same. A simplified table structure is sufficient, i.e. the report requires the time of the request, the request itself, and a second-level domain for grouping.
DDL tables
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)
);Hack and predictor Aviator
Thus, as a result of the analysis of the domain name server log, more than 50 entries were censored and placed on the block list.
The need for some queries is well described by the software vendors and inspires confidence. However, much of the activity is unfounded and questionable.
Source: habr.com