How does the home Internet and domain server statistics live?
A home router (in this case FritzBox) can register a lot: how much traffic is going when, who is connected at what speed, etc. To find out what is hidden under strange addresses, I was helped by a domain name server (DNS) on the local network.
Overall, DNS had a positive impact on the home network: added speed, resilience, and manageability.
Below is a diagram that raised questions and the need to understand what is happening. The results have already filtered out well-known and working queries to domain name servers.
Why is it that 60 obscure domains are interrogated every day while they are still sleeping?
Every day, 440 unknown domains are polled in active time. Who are they and what are they doing?
At night, wireless access is disabled and device activity is expected, i.e. no survey of obscure domains. This means that the most activity comes from devices with operating systems such as Android, iOS and Blackberry OS.
List the domains that are being polled intensively. The intensity will be determined by such parameters as the number of requests per day, the number of days of activity, and how many hours of the day were noticed.
The list included all the expected suspects.
We block iccc.blackberry.com and iceberg.blackberry.com, which the manufacturer will justify for security reasons. Result: when you try to connect to the WLAN, it shows the login page and never connects again. Unlock.
detectportal.firefox.com is the same mechanism, only implemented in the Firefox browser. If necessary, log in to the WLAN, first show the login page. It is not clear why ping the address so often, but the mechanism is available from the manufacturer.
skype. The actions of this program are similar to a worm: it hides and does not allow itself to be killed in the taskbar, it generates a lot of traffic on the network, pings 4 domains every 10 minutes. When making a video call, the Internet connection is constantly mobilizing, when it can no longer be better. While it is necessary, so it remains.
upload.fp.measure.office.com - refers to Office 365, did not find a decent description.
browser.pipe.aria.microsoft.com - I didn’t find a decent description.
Both are blocking.
connect.facebook.net is a facebook chat application. It remains.
mediator.mail.ru The analysis of all mail.ru domain requests showed the presence of a huge amount of advertising resources and statistics collectors, which causes distrust. The mail.ru domain is completely blacklisted.
google-analytics.com - it doesn’t affect the functionality of devices, therefore we block it.
doubleclick.net - counts ad clicks. We block.
A lot of requests go to googleapis.com. Blocking led to a joyful disconnection of short messages on the tablet that seem silly to me. But the playstore stopped working, so we unlock it.
cloudflare.com - they write that they love open source code and, in general, write a lot about themselves. The intensity of the domain survey, which is often much higher than the activity on the Internet, is not entirely clear. Let's leave it for now.
Thus, the intensity of requests is often associated with the necessary functionality of the devices. But those who overdid with activity were also discovered.
At the moment of turning on the wireless Internet, they are still sleeping and there is an opportunity 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 of time 60 domains are polled daily:
Firefox checks the WLAN connection for a login page.
Citrix pings its server, although the application is not actively running.
Symantec verifies certificates.
Mozilla checks for updates, although in the settings asked not to do this.
mmo.de is a game 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, every button click is sent here for search optimization. Very suspicious, but related to functionality. We leave.
The following is a long list of calls to microsoft.com. All domains, starting from the third level, are blocked.
The number of subdomains of the very first
So, the first 10 minutes of turning on the wireless Internet.
Most subdomains are polled by iOS - 32. It is followed by Android - 24, then Windows - 15 and the last Blackberry - 9.
Facebook alone polls 10 domains, skype polls 9 domains.
The source for analysis was the log file of the local bind9 server, which contains the following format:
The file was imported into the sqlite database and analyzed using SQL queries.
The server serves as a cache, requests come from the router, so the request client is always one. A fairly simplified table structure, i.e. for the report you need the request time, the request itself and the second level domain for grouping.
Thus, as a result of the analysis of the domain name server log, more than 50 records were censored and placed on the list for blocking.
The need for some queries is well described by software manufacturers and inspires confidence. However, most of the activity is unreasonable and questionable.
Overall, DNS had a positive impact on the home network: added speed, resilience, and manageability.
Below is a diagram that raised questions and the need to understand what is happening. The results have already filtered out well-known and working queries to domain name servers.
Why is it that 60 obscure domains are interrogated every day while they are still sleeping?
Every day, 440 unknown domains are polled in active time. Who are they and what are they doing?
Average daily searches per hour
SQL report request
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 disabled and device activity is expected, i.e. no survey of obscure domains. This means that the most activity comes from devices with operating systems such as Android, iOS and Blackberry OS.
List the domains that are being polled intensively. The intensity will be determined by such parameters as the number of requests per day, the number of days of activity, and how many hours of the day were noticed.
The list included all the expected suspects.
Intensively polled domains
SQL report request
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
We block iccc.blackberry.com and iceberg.blackberry.com, which the manufacturer will justify for security reasons. Result: when you try to connect to the WLAN, it shows the login page and never connects again. Unlock.
detectportal.firefox.com is the same mechanism, only implemented in the Firefox browser. If necessary, log in to the WLAN, first show the login page. It is not clear why ping the address so often, but the mechanism is available from the manufacturer.
skype. The actions of this program are similar to a worm: it hides and does not allow itself to be killed in the taskbar, it generates a lot of traffic on the network, pings 4 domains every 10 minutes. When making a video call, the Internet connection is constantly mobilizing, when it can no longer be better. While it is necessary, so it remains.
upload.fp.measure.office.com - refers to Office 365, did not find a decent description.
browser.pipe.aria.microsoft.com - I didn’t find a decent description.
Both are blocking.
connect.facebook.net is a facebook chat application. It remains.
mediator.mail.ru The analysis of all mail.ru domain requests showed the presence of a huge amount of advertising resources and statistics collectors, which causes distrust. The mail.ru domain is completely blacklisted.
google-analytics.com - it doesn’t affect the functionality of devices, therefore we block it.
doubleclick.net - counts ad clicks. We block.
A lot of requests go to googleapis.com. Blocking led to a joyful disconnection of short messages on the tablet that seem silly to me. But the playstore stopped working, so we unlock it.
cloudflare.com - they write that they love open source code and, in general, write a lot about themselves. The intensity of the domain survey, which is often much higher than the activity on the Internet, is not entirely clear. Let's leave it for now.
Thus, the intensity of requests is often associated with the necessary functionality of the devices. But those who overdid with activity were also discovered.
Very first
At the moment of turning on the wireless Internet, they are still sleeping and there is an opportunity 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 of time 60 domains are polled daily:
SQL report request
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 checks the WLAN connection for a login page.
Citrix pings its server, although the application is not actively running.
Symantec verifies certificates.
Mozilla checks for updates, although in the settings asked not to do this.
mmo.de is a game 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, every button click is sent here for search optimization. Very suspicious, but related to functionality. We leave.
The following is a long list of calls to microsoft.com. All domains, starting from the third level, are blocked.
The number of subdomains of the very first
So, the first 10 minutes of turning on the wireless Internet.
Most subdomains are polled by iOS - 32. It is followed by Android - 24, then Windows - 15 and the last Blackberry - 9.
Facebook alone polls 10 domains, skype polls 9 domains.
A source of information
The source for 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 the sqlite database and analyzed using SQL queries.
The server serves as a cache, requests come from the router, so the request client is always one. A fairly simplified table structure, i.e. for the report you need the request time, the request itself and the 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 'na', TIME_NK TEXT NOT NULL DEFAULT 'na', CLI TEXT, -- client IP TEXT, REQUEST_NK TEXT NOT NULL DEFAULT 'na', -- requested domain DOMAIN TEXT NOT NULL DEFAULT 'na', -- domain second level QUERY TEXT, UNIQUE (LINE_NK, DATE_NK, TIME_NK, REQUEST_NK) );
Conclusion
Thus, as a result of the analysis of the domain name server log, more than 50 records were censored and placed on the list for blocking.
The need for some queries is well described by software manufacturers and inspires confidence. However, most of the activity is unreasonable and questionable.
All Articles