Can "Web History" be sorted by date (rather than hostname)?

[David White]

The "Adaptive QoS -> Web History" section looks like this:

Strangely, this list is sorted by hostname, not by date. That makes it almost impossible to monitor traffic. (What sites did my children visit today? There's no easy way to tell.) Furthermore, the columns are not clickable so there is no way to sort this list.

Could I request that this sorting feature be added to Asuswrt-Merlin? Being able to see everything in (reverse) date order is really the only way I can see that the Web Traffic feature would be useful.

Thank you!

 

[RMerlin]

Closed source implementation from Trend Micro and Asus.

 

[David White]

Could it perhaps be parsed and re-presented in a separate tab, where it could be sorted?

 

[AndreiV]

Could it perhaps be parsed and re-presented in a separate tab, where it could be sorted?

Closed source means Merlin can't do anything to alter web history.

 

[barzot]

Retrieve WebHistory.db (at /jffs/.sys/WebHistory/WebHistory.db) with scp/pscp, then use an sqlite viewer with proper sql to view as you like, eg:

SELECT datetime(timestamp, 'unixepoch', 'localtime') AS time, mac, url FROM history ORDER BY time;

 

[David White]

Closed source means Merlin can't do anything to alter web history.

Right, so I was asking if a client solution could be implemented in Asuswrt-Merlin. For example it would be ugly, but I could write a Python tool and use requests and maybe BeautifulSoup to screen-scrape the contents of the Web Traffic box. I was wondering if the firmware, being closer to that web traffic implementation than my external Python script would be, would be able to do something similar and then put it into a new tab. The answer may still be "no" for reasons I'm unaware of, but that was the nature of my second question.

Retrieve WebHistory.db (at /jffs/.sys/WebHistory/WebHistory.db) with scp/pscp, then use an sqlite viewer with proper sql to view as you like

Oh, that sounds like a good possibility! Much better than screen-scraping. Not sure where the SSH (scp) connection options are, but I'll hunt around and see if I can find it.

(And again, this seems like it would be a nice candidate for a new tab on the QoS section. But maybe there are technical reasons I'm not aware of why Asuswrt-Merlin can't parse that sqlite DB and regenerate the output in a sortable table.)

 

[RMerlin]

The httpd daemon gets all that information through get_web_hook(char *client, char *page, char *num, int *retval, webs_t wp) located in the closed source libbwdpi_sql.so library. To do what you ask means I don't just have to modify existing code, I actually have to rewrite everything from scratch. And that database structure could change at any time in the future, which could break whatever code I blindly wrote to access it. And we have to assume there isn't any special locking mechanism necessary to access that database while the bwdpi engine is still using it...

It's just not something I'm willing to devote days of development time to. My advice is to ask Asus to implement it upstream.

 

[Martineau]

I could write a Python tool

No need for python...

Check if Entware sqlite3 has quietly been installed by a 3rd party script:

if so then you may immediately create a Web History report using (@barzot's 'SELECT') based on a filter using simple grep etc.
e.g. To search for say Google or Youtube access, simply copy'n'paste the following into the command prompt:

NOTE: I have deliberately inserted the two '_' chars around the 'select' command to be able to post

sqlite3 /jffs/.sys/WebHistory/WebHistory.db "_select_ datetime(timestamp, 'unixepoch', 'localtime') AS time, mac, url FROM history ORDER BY time;" | grep -iE "google|youtube"

2018-11-12 08:25:00|xx:xx:xx:xx:xx:xx|ajax.googleapis.com
2018-11-12 08:33:12|xx:xx:xx:xx:xx:xx|www.google.com
2018-11-12 08:36:13|xx:xx:xx:xx:xx:xx|clients3.google.com
2018-11-12 08:36:14|xx:xx:xx:xx:xx:xx|cast.google.com
2018-11-12 08:36:39|xx:xx:xx:xx:xx:xx|inbox.google.com
2018-11-12 08:48:00|xx:xx:xx:xx:xx:xx|android.clients.google.com
2018-11-12 08:48:02|xx:xx:xx:xx:xx:xx|play.googleapis.com
2018-11-12 08:48:02|xx:xx:xx:xx:xx:xx|mail.google.com
2018-11-12 08:58:09|xx:xx:xx:xx:xx:xx|clients4.google.com
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|adservice.google.co.uk
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|apis.google.com
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|www.youtube.com
2018-11-12 09:03:58|xx:xx:xx:xx:xx:xx|googleads.g.doubleclick.net
2018-11-12 09:03:58|xx:xx:xx:xx:xx:xx|accounts.google.com
2018-11-12 09:04:00|xx:xx:xx:xx:xx:xx|pagead2.googlesyndication.com
2018-11-12 09:08:00|xx:xx:xx:xx:xx:xx|www.googleapis.com
2018-11-12 09:08:11|xx:xx:xx:xx:xx:xx|www.google.com

So using a script you can have an email sent to you daily, or you could add a new TAB to the GUI (as Kvic did for the NTP Daemon) etc.

 

[Frank Monroe]

The "Adaptive QoS -> Web History" section looks like this:

Strangely, this list is sorted by hostname, not by date. That makes it almost impossible to monitor traffic. (What sites did my children visit today? There's no easy way to tell.) Furthermore, the columns are not clickable so there is no way to sort this list.

Could I request that this sorting feature be added to Asuswrt-Merlin? Being able to see everything in (reverse) date order is really the only way I can see that the Web Traffic feature would be useful.

Thank you!

It used to be sorted by date until ASUS made a change last year to the firmware. I opened a case about it but got nowhere. I really wish they would revert it. Its useless to me sorting by domain name.

 

[SamHNB]

Thank you @Martineau . I downloaded the WebHistory.db and opened it in "DB Browser for SQLite".

Just a suggestion, instead of using grep, you can apply the filter in SQL query like this:

SELECT

datetime(timestamp, 'unixepoch', 'localtime') AS time, mac, url​

FROM

history​

WHERE

url REGEXP 'google|microsoft'​

ORDER BY

time DESC​

If you want to filter by date/time use this in the where clause instead:

WHERE

date(datetime(timestamp, 'unixepoch', 'localtime'))

between '2020-02-26' and '2020-02-27'​

No need for python...

Check if Entware sqlite3 has quietly been installed by a 3rd party script:

if so then you may immediately create a Web History report using (@barzot's 'SELECT') based on a filter using simple grep etc.
e.g. To search for say Google or Youtube access, simply copy'n'paste the following into the command prompt:

NOTE: I have deliberately inserted the two '_' chars around the 'select' command to be able to post

sqlite3 /jffs/.sys/WebHistory/WebHistory.db "_select_ datetime(timestamp, 'unixepoch', 'localtime') AS time, mac, url FROM history ORDER BY time;" | grep -iE "google|youtube"

2018-11-12 08:25:00|xx:xx:xx:xx:xx:xx|ajax.googleapis.com
2018-11-12 08:33:12|xx:xx:xx:xx:xx:xx|www.google.com
2018-11-12 08:36:13|xx:xx:xx:xx:xx:xx|clients3.google.com
2018-11-12 08:36:14|xx:xx:xx:xx:xx:xx|cast.google.com
2018-11-12 08:36:39|xx:xx:xx:xx:xx:xx|inbox.google.com
2018-11-12 08:48:00|xx:xx:xx:xx:xx:xx|android.clients.google.com
2018-11-12 08:48:02|xx:xx:xx:xx:xx:xx|play.googleapis.com
2018-11-12 08:48:02|xx:xx:xx:xx:xx:xx|mail.google.com
2018-11-12 08:58:09|xx:xx:xx:xx:xx:xx|clients4.google.com
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|adservice.google.co.uk
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|apis.google.com
2018-11-12 09:03:56|xx:xx:xx:xx:xx:xx|www.youtube.com
2018-11-12 09:03:58|xx:xx:xx:xx:xx:xx|googleads.g.doubleclick.net
2018-11-12 09:03:58|xx:xx:xx:xx:xx:xx|accounts.google.com
2018-11-12 09:04:00|xx:xx:xx:xx:xx:xx|pagead2.googlesyndication.com
2018-11-12 09:08:00|xx:xx:xx:xx:xx:xx|www.googleapis.com
2018-11-12 09:08:11|xx:xx:xx:xx:xx:xx|www.google.com

So using a script you can have an email sent to you daily, or you could add a new TAB to the GUI (as Kvic did for the NTP Daemon) etc.