Solved How to clear uiDivStats Query Log record?

[chongnt]

After a few reboot by year end, some stats seems to have messed up with the year 2020 to 2021. Dec 2020 logs appears as Dec 2021 in the query log entries. Since now is only January 2021, these Dec 2021 logs is always there and not going away. I waited for the stats to generate new weekly report and rotate through the historical dnsmasq.log hoping that it will eventually get flush away. Unfortunately it is still shown in the GUI. Anyone face this issue and how to fix this?

Edit: Tried to uninstall, reboot then reinstall but still the same. Further trying found dnsqueries.db under /opt/share/uiDivStats.d which is not deleted. I suspect those logs that messed up with the year are in this db file. Am not familiar with db file, will try to see if I can remove those entries. Else I will try to delete and let it build a new db file.

Edit2: Deleted dnsqueries.db and let it rebuild clean db. Issue fixed.
On a side note, noticed /opt/share/connmon.d/config: Input/output error. Formatted USB drive and reinstall everything.

 

[Ronald Schwerer]

I just noticed the 14 GB usb partition containing /opt/share/uiDivStats.d was 100% full and the dnsqueries.db file was over 12 GB. I wasn't noticing any other issues - just happened to notice this while browsing the partition. First, I ran amtm and tried updating uiDivStats. But it errored rebuilding the indexes (no surprise). Then I found this thread and uninstalled uiDivStats, deleted the dnsqueries.db file, and reinstalled . I'll wait until tomorrow to see if it it working.

 

[Jack Yaz]

I just noticed the 14 GB usb partition containing /opt/share/uiDivStats.d was 100% full and the dnsqueries.db file was over 12 GB. I wasn't noticing any other issues - just happened to notice this while browsing the partition. First, I ran amtm and tried updating uiDivStats. But it errored rebuilding the indexes (no surprise). Then I found this thread and uninstalled uiDivStats, deleted the dnsqueries.db file, and reinstalled . I'll wait until tomorrow to see if it it working.

12gb?! How many dns queries do you have going through the router? Mine is 500mb for 30days and 25 or so clients

 

[Ronald Schwerer]

12gb?! How many dns queries do you have going through the router? Mine is 500mb for 30days and 25 or so clients

Is there any postmortem way to tell? I don't know why I'd get an unusual number of dns queries in a typical home environment. Since being recreated 3 hrs ago, the file has stayed at 24576 bytes. I'll keep an eye on the size.

 

[Ronald Schwerer]

Update: it has been nearly 24 hrs since I deleted and my dnsqueries.db file has grown to over 31 MB:

 ls -l  ./share/uiDivStats.d
drwxrwxrwx    2 admin    root          4096    Jan 20 10:22 csv
-rw-r--r--       1 admin    root      31817728 Jan 20 10:22 dnsqueries.db
-rw-r--r--       1 admin    root         32768   Jan 20 10:22 dnsqueries.db-shm
-rw-r--r--       1 admin    root         20632   Jan 20 10:22 dnsqueries.db-wal
-rw-rw-rw-    1 admin    root           372      Jan 20 10:22 dnsqueriestotal.sql

I'm not sure if that is normal or not. I know very little about sqlite (or SQL in general), so I don't know how to see inside the db. I've got around 35 clients with a bunch IoT stuff (mostly smart lights/switches and controllers) which should have minimal traffic. Also have 4 Yi cameras which do send traffic to their server (IIRC in China) every 6 seconds.

 

[Jack Yaz]

Update: it has been nearly 24 hrs since I deleted and my dnsqueries.db file has grown to over 31 MB:

ls -l  ./share/uiDivStats.d
drwxrwxrwx    2 admin    root          4096    Jan 20 10:22 csv
-rw-r--r--       1 admin    root      31817728 Jan 20 10:22 dnsqueries.db
-rw-r--r--       1 admin    root         32768   Jan 20 10:22 dnsqueries.db-shm
-rw-r--r--       1 admin    root         20632   Jan 20 10:22 dnsqueries.db-wal
-rw-rw-rw-    1 admin    root           372      Jan 20 10:22 dnsqueriestotal.sql

I'm not sure if that is normal or not. I know very little about sqlite (or SQL in general), so I don't know how to see inside the db. I've got around 35 clients with a bunch IoT stuff (mostly smart lights/switches and controllers) which should have minimal traffic. Also have 4 Yi cameras which do send traffic to their server (IIRC in China) every 6 seconds.

You could watch the query log on the WebUI file to see what's going through. It would also be a good at-a-glance check to see how many requested domains are going through if you can post screenshots. if you're concerned with privacy about any of the domains then please PM me instead

 

[paulml]

Update: it has been nearly 24 hrs since I deleted and my dnsqueries.db file has grown to over 31 MB:

ls -l  ./share/uiDivStats.d
drwxrwxrwx    2 admin    root          4096    Jan 20 10:22 csv
-rw-r--r--       1 admin    root      31817728 Jan 20 10:22 dnsqueries.db
-rw-r--r--       1 admin    root         32768   Jan 20 10:22 dnsqueries.db-shm
-rw-r--r--       1 admin    root         20632   Jan 20 10:22 dnsqueries.db-wal
-rw-rw-rw-    1 admin    root           372      Jan 20 10:22 dnsqueriestotal.sql

I'm not sure if that is normal or not. I know very little about sqlite (or SQL in general), so I don't know how to see inside the db. I've got around 35 clients with a bunch IoT stuff (mostly smart lights/switches and controllers) which should have minimal traffic. Also have 4 Yi cameras which do send traffic to their server (IIRC in China) every 6 seconds.

Hi, were you able to solve your issue? Been experiencing reboots on my router these past few days and I just noticed my 16gb drive was full due to dnsqueries.db being over 10gb in size. I cleared it using the reset all data option in amtm-uidivstats menu and reboots have stopped so far. Curious on the cause so I can hopefully prevent it from happening again in the future. Thanks!