uiDivStats uiDivStats - WebUI for Diversion statistics

[Jack Yaz]

No luck, still sitting there, barely idling.

How big is your db?

 

[thelonelycoder]

How big is your db?

112 MB

 

[Jack Yaz]

112 MB

It should really not be taking that long :/ (these indexes will be created on fresh dbs for new installs, so I am not anticipating any problems there, early adopters seem to be suffering though
Manual method:

killall uiDivStats
/opt/etc/init.d/S90taildns stop
cru d uiDivStats_generate
cru d uiDivStats_trim
cru d uiDivStats_querylog

Then, open the database:

sqlite3 /opt/share/uiDivStats.d/dnsqueries.db

Run each of the below commands one at a time:

drop index idx_dns;
drop index idx_dns_clients;
create index idx_dns_domains on dnsqueries (ReqDmn,Timestamp);
create index idx_dns_time on dnsqueries (Timestamp,ReqDmn);
create index idx_dns_clients on dnsqueries (SrcIP,Timestamp,ReqDmn);

Once completed:

touch /jffs/addons/uiDivStats.d/.upgraded
touch /jffs/addons/uiDivStats.d/.upgraded2
/opt/etc/init.d/S90taildns start
uiDivStats

Really sorry for this, I don't know why running the commands as part of the launching isn't working

 

[heslo]

You may have been struck by the same bug/issue - follow the steps in https://www.snbforums.com/threads/u...iversion-statistics.56393/page-31#post-589694 please

Thanks for that man, I've since reverted to 1.3.2 as I don't have the time today to tinker. Tomorrow I'll have fiddle and report back with how it went for me

 

[QuikSilver]

All four cores are busy, I'll let it heat up the office for the time being.

@Jack Yaz next project coming soon....Merlin supported router office heater!

 

[Jack Yaz]

@Jack Yaz next project coming soon....Merlin supported router office heater!

with my haphazard coding, probably

 

[QuikSilver]

with my haphazard coding, probably

When in doubt, call it a "feature".

 

[gattaca]

Does anyone know why the pixelserv stats are not being updated in the "Addons" tab of Merlin+AMTM? When I access the stats directly from my browser (http://192.168.111.2/servstats), I see current, updating pixelserv stats. Then when viewing off the Addons tab, expanding the "Pixelserv Statistics Report" the WebGUI shows me the same report that's always 6 days old. I updated AMTM and then restarted the router this AM - no change in behavior. THANKS!

 

[Jack Yaz]

Does anyone know why the pixelserv stats are not being updated in the "Addons" tab of Merlin+AMTM? When I access the stats directly from my browser (http://192.168.111.2/servstats), I see current, updating pixelserv stats. Then when viewing off the Addons tab, expanding the "Pixelserv Statistics Report" the WebGUI shows me the same report that's always 6 days old. I updated AMTM and then restarted the router this AM - no change in behavior. THANKS!

Are other stats updating correctly?

 

[gattaca]

^^^ Actually, great point - No. The Diversion Stats are stuck on 24 May 2020 10:00. Thoughts? I've upgraded everything and do so weekly. Already done a couple of full reboots this AM. It's been working awesome! I've also done a "uf" forced update to uiDivStats.

All of the ping, ntp, uptime monitoring appear to be updating with current time etc..

OK, actually maybe this helps. I logged out using Brave and then logged back in using Chrome and the Addon > uiDivStats is now showing current. Maybe there's some reason those pages are not being refreshed? They don't appear to "refresh" when I hit refresh in the browser. I log into the WEBGUI every couple of days so I know I've logged in (using Brave) many times since 24 May. Thoughts?

 

[Jack Yaz]

^^^ Actually, great point - No. The Diversion Stats are stuck on 24 May 2020 10:00. Thoughts? I've upgraded everything and do so weekly. Already done a couple of full reboots this AM. It's been working awesome! I've also done a "uf" forced update to uiDivStats.

All of the ping, ntp, uptime monitoring appear to be updating with current time etc..

OK, actually maybe this helps. I logged out using Brave and then logged back in using Chrome and the Addon > uiDivStats is now showing current. Maybe there's some reason those pages are not being refreshed? They don't appear to "refresh" when I hit refresh in the browser. I log into the WEBGUI every couple of days so I know I've logged in (using Brave) many times since 24 May. Thoughts?

They should update hourly - do you get any errors if you update manually via the CLI menu?

 

[gattaca]

^^ When updating in the uiDivStats > 1, the stats are regenerated with no errors. Then refreshing either Brave or Chrome shows an update with the time I manually generated the update. Could there be a cron job not running?

Just passed 13:00 and ALL the stats appear to have regenerated. I'll continue to monitor and report as more top of hour (00) passes.

 

[thelonelycoder]

It should really not be taking that long :/ (these indexes will be created on fresh dbs for new installs, so I am not anticipating any problems there, early adopters seem to be suffering though
Manual method:

killall uiDivStats
/opt/etc/init.d/S90taildns stop
cru d uiDivStats_generate
cru d uiDivStats_trim
cru d uiDivStats_querylog

Then, open the database:

sqlite3 /opt/share/uiDivStats.d/dnsqueries.db

Run each of the below commands one at a time:

drop index idx_dns;
drop index idx_dns_clients;
create index idx_dns_domains on dnsqueries (ReqDmn,Timestamp);
create index idx_dns_time on dnsqueries (Timestamp,ReqDmn);
create index idx_dns_clients on dnsqueries (SrcIP,Timestamp,ReqDmn);

Once completed:

/opt/etc/init.d/S90taildns start
uiDivStats

Really sorry for this, I don't know why running the commands as part of the launching isn't working

Still not working.

sqlite> .recover
SQL error: no such table: sqlite_dbpage
sqlite> .tables
dnsqueries         dnsqueriesmonthly  dnsqueriesweekly
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: /opt/share/uiDivStats.d/dnsqueries.db
sqlite> drop index idx_dns;
Error: no such index: idx_dns
sqlite> drop index idx_dns_clients;
sqlite> create index idx_dns_domains on dnsqueries (ReqDmn,Timestamp);
Error: index idx_dns_domains already exists
sqlite> create index idx_dns_time on dnsqueries (Timestamp,ReqDmn);
Error: index idx_dns_time already exists
sqlite> create index idx_dns_clients on dnsqueries (SrcIP,Timestamp,ReqDmn);
sqlite> .quit
tlc@RT-AX88U-D378:/tmp/home/root# /opt/etc/init.d/S90taildns start
 Starting taildns...              done.
tlc@RT-AX88U-D378:/tmp/home/root# uiDivStats

I moved the existing db and started new.
After a fresh install of the unreleased 2.0.0 version, I get this error on first run, subsequent runs don't. I have not run option 1 before:

Choose an option:    2

sed: /jffs/addons/uiDivStats.d/SQLData.js: No such file or directory
uiDivStats: Stats updated successfully

 

[Jack Yaz]

Still not working.

sqlite> .recover
SQL error: no such table: sqlite_dbpage
sqlite> .tables
dnsqueries         dnsqueriesmonthly  dnsqueriesweekly
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: /opt/share/uiDivStats.d/dnsqueries.db
sqlite> drop index idx_dns;
Error: no such index: idx_dns
sqlite> drop index idx_dns_clients;
sqlite> create index idx_dns_domains on dnsqueries (ReqDmn,Timestamp);
Error: index idx_dns_domains already exists
sqlite> create index idx_dns_time on dnsqueries (Timestamp,ReqDmn);
Error: index idx_dns_time already exists
sqlite> create index idx_dns_clients on dnsqueries (SrcIP,Timestamp,ReqDmn);
sqlite> .quit
tlc@RT-AX88U-D378:/tmp/home/root# /opt/etc/init.d/S90taildns start
 Starting taildns...              done.
tlc@RT-AX88U-D378:/tmp/home/root# uiDivStats

I moved the existing db and started new.
After a fresh install of the unreleased 2.0.0 version, I get this error on first run, subsequent runs don't. I have not run option 1 before:

Choose an option:    2

sed: /jffs/addons/uiDivStats.d/SQLData.js: No such file or directory
uiDivStats: Stats updated successfully

Idiot over here forgot to include the command to "touch" the file to tell uiDivStats that an upgrade was not necessary. I am not doing well today, sorry

I'll take a look at the sed error

 

[Jack Yaz]

@thelonelycoder sed error fixed, good find! https://github.com/jackyaz/uiDivStats/commit/9538f39b759c4ed74f4eee995134c71f09df373e

I've also added a call to do a "fullrefresh" as the final step in the upgrade sequence

 

[Jack Yaz]

v2.0.0 is now available!
Changelog:

Complete re-write! uiDivStats now pushes dnsmasq log entries to SQLite in realtime via the "taildns" script. All graphical data is then generated against this database. The database will keep the last 30 days worth of requests and is typically a minimum of 200-300MB with 30 days' worth of data. The database is stored on an attached USB, in /opt/share/uiDivStats.d

The tab has been relocated from Addons to LAN - it may stay in Addons until you reboot, however.

Data is now displayed for:
- Last 24 hours
- Last 7 days
- Last 30 days
and is available per-client.

Data for "Last 24 hours" is refreshed hourly. Data for "Last 7 days" and "Last 30 days" is refreshed daily, at midnight.

A query log is now available in the WebUI, for ease of checking if a domain is blocked (refreshes every 60 seconds)

A big thanks to @thelonelycoder @dave14305 @QuikSilver @SomeWhereOverTheRainBow @visortgw @heslo @brianward317 for the massive help in the testing effort! Apologies to anyone I've missed!

 

[heslo]

Great stuff Jack; installing now!

Sorry I couldn't give any more feedback the past few days, just been too busy with work but thanks for all your hard work

EDIT: Updated but now when I go to enter uiDivStats in amtm it just sits there loading the page with nothing happening. The tab in LAN in the router web GUI also shows no stats. Any ideas?

 

[Jack Yaz]

Great stuff Jack; installing now!

Sorry I couldn't give any more feedback the past few days, just been too busy with work but thanks for all your hard work

EDIT: Updated but now when I go to enter uiDivStats in amtm it just sits there loading the page with nothing happening. The tab in LAN in the router web GUI also shows no stats. Any ideas?

I'll PM you, there was a db change that didn't upgrade correctly for some users who beta tested

 

[Dan Ljungberg]

The update via amtm seemed to go well, but the web GUI shows no stats.
When updating in the uiDivStats> 1, the stats are regenerated with:

awk: cmd. line:1: Division by zero
uiDivStats: Stats updated successfully

Any suggestions?

 

[tomsk]

Hi Jack,
Updated to V2 and rebooted to make the tab show in LAN but to no avail. I went back into amtm and selected the j5 option. i got some Entware install and config activity but something is hanging and i'm not being returned to the menu

Enter option  j5
Downloading http://bin.entware.net/armv7sf-k2.6/Packages.gz
Updated list of available packages in /opt/var/opkg-lists/entware
Package grep (3.4-1) installed in root is up to date.
Package sqlite3-cli (3310100-1) installed in root is up to date.
Installing procps-ng-pkill (3.3.15-4) to root...
Downloading http://bin.entware.net/armv7sf-k2.6/procps-ng-pkill_3.3.15-4_armv7-2.6.ipk
Installing procps-ng (3.3.15-4) to root...
Downloading http://bin.entware.net/armv7sf-k2.6/procps-ng_3.3.15-4_armv7-2.6.ipk
Configuring procps-ng.
Configuring procps-ng-pkill.
uiDivStats: New version of taildns.tar.gz downloaded