Web History Reporting and Management (Traffic Analyzer/Aiprotection Monitor)

[Martineau]

Recently there was a query regarding the Web History tracking to which I replied, so I thought I would revisit my crude script I posted.

I believe the Web History is now more mature, and probably can provide useful info, but extracting the info into a printable report is sadly lacking.

So here is my shoddy WebHistory_Report.sh script to allow queries to be made on the Web History database
EDIT:22/11/2018 v1.08 Released see Release notes
EDIT:20/11/2018 v1.07 Fix 'time=HH:' matching etc. see post #6

So here is my shoddy TrafficAnalyzer_Report.sh script to allow queries to be made on the Traffic Analyzer database
So here is my shoddy AiProtectionMonitor_Report.sh" script to allow queries to be made on the AiProtection Monitor database
EDIT:13/12/2018 v1.09 Released see Release notes
EDIT:22/11/2018 v1.08 Released see Release notes
EDIT:20/11/2018 v1.07 Released:




Prerequisites:
1. Obviously Web History must be ENABLED
2. Entware must be installed (why isn't it? ) together with

EDIT: 10/05/2019 Firmware v384.11 now includes 'sqlite3 v3.7.2' rather than Entware's
'sqlite3 v3.26.0' utility (opkg install sqlite3-cli) (if not already silently installed by Skynet)

'find' utility (opkg install findutils) Thanks @jtp10181​

./WebHistory_Report.sh -h

#======================================================================================= © 2016-2018 Martineau v1.08
#
# Scan Web History database
#
#    WebHistory_Report     [help | -h] ['ip='{[ip_address[,...] | hostname[...]]} ['flush']] ['url='{url_string[,...]}] ['nofilter'] ['email'] ['mode=or'] ['noscript']
#                          ['date='[yyyy/mm/dd[,...]]] ['time='[hh:mm:ss[,...]]] ['sqldb='{database}] ['backup'] ['purgeallreset'] ['count'] ['sortby='column]
#
#    WebHistory_Report
#                          Will list 'Todays' URL entries in the Web History database containing strings 'facebook' OR 'youtube'
#    WebHistory_Report     count
#                          Will count 'Todays' URL entries in the Web History database containing strings 'facebook' OR 'youtube'
#                          and will only display the result count. No records are displayed on screen.
#    WebHistory_Report     nofilter
#                          Will list ALL entries in the Web History database.
#    WebHistory_Report     nofilter sortby=url
#                          Will list ALL entries in the Web History database sorted by column URL
#    WebHistory_Report     nofilter email
#                          Will list ALL entries in the Web History database and will send an email with the results
#    WebHistory_Report     url=amazon,netflix
#                          Will list URL entries in the Web History database containing strings either 'amazon' OR 'netflix'
#    WebHistory_Report     ip=192.168.1.1 url=amazon,netflix time=18:,19: mode=or
#                          Will list URL entries in the Web History database for 192.168.1.1 or between 18:00-19:59 or URLs as above
#                          Without 'mode=or' then the databse records must match ALL three criteria
#    WebHistory_Report     date=2017/02/30
#                          Will list entries in the Web History database created on '30th Feb 2017'
#                          NOTE: The date specification can be an abbreviation e.g. '2017/02' for records created in 'Feb 2017'
#    WebHistory_Report     ip=10.88.8.123, 192.168.1.120-192.168.1.123, CAMERAS
#                          Will list database entries for five devices, plus all IPs for 'CAMERAS' entry in '/jffs/configs/IPGroups'
#                          NOTE: Only MAC addresses are stored in the database so if the devices are not 'reserved/static'
#                                then the report could be inaccurate.
#    WebHistory_Report     ip=10.88.8.123 flush url="www.veryexpensiveshoes.com"
#                          Will delete all URL 'www.veryexpensiveshoes.com' history for '10.88.8.123' Ha ha - wife mode eh? ;-)
#    WebHistory_Report     ip=10.88.8.123 flush
#                          Will delete all history for '10.88.8.123'
#    WebHistory_Report     time=09:
#                          Will list entries in the Web History database created between '09:00' to '09:59'
#                          NOTE: A full time specification can be used e.g. '12:05:30' but the report may never find a match!
#    WebHistory_Report     backup
#                          The current Web History database will be backed up to '/opt/var/WebHistory/'
#    WebHistory_Report     sqldb=/opt/var/WebHistory/WebHistory.db-Backup-20180401-060000
#                          The report/queries will be extracted from the archive/backup database '/opt/var/WebHistory/WebHistory.db-Backup-20180401-060000'
#    WebHistory_Report     purgeallreset
#                          The current Web History database is PURGED of ALL history!!!!! (NOTE: a backup is taken first ;-)

e.g. Default filter in AND mode... i.e. both date= and url= must match
Find all URLs containing either of the strings 'facebook' or 'youtube' created 'today'

./WebHistory_Report.sh

Complex query using OR mode
e.g. Find all URLs containing any of the strings ( 'amazon' or 'kindle' or 'game'), or any records timestamped between '10:00-10:59' or any records for LAN device 10.88.8.157 (which happens to be a kids phone )

./WebHistory_Report.sh ip=10.88.8.157 url=amazon time=10 url=kindle,game    mode=or

I have similar scripts for Traffic Analyzer and AiProtection Monitor, but first I'd like the community to try out (aka debug ) the Web History script first to see if there is a need for such scripts.

As usual all/any feedback appreciated.

EDIT: If the Web History is large i.e. was enabled weeks ago?, then

./WebHistory_Report.sh   nofilter

will trundle slowly through ALL the database records on screen - starting with the first timestamped record and could take days

e.g. Database contains 116458 records
Script takes approx. 9 seconds before the first record is displayed on screen, then takes a further 8 seconds to fill the screen with the first 32 records.​

So, a quick tip to instantly dump/display ALL records in the database is to issue

./WebHistory_Report.sh   nofilter   noscript

and the 'raw' SQL records are returned within a few seconds.

i.e. the same database all 116458 records can be dumped/displayed on screen in <10 seconds! so if cosmetic formatting isn't required simple grep/sed/awk can be used to apply the filters.

 

[HardCat]

@Martineau - this is nice! Running fine on RT-AC86U... Have tried nofilter option and url= so far. Will play some more and report back if I find anything not working as expected.

Thanks again...

 

[Martineau]

@Martineau - this is nice! Running fine on RT-AC86U... Have tried nofilter option and url= so far. Will play some more and report back if I find anything not working as expected.

Thanks again...

You again!

You're a glutton for punishment...but thanks for being the first to test!

 

[kvic]

This is an interesting discovery. Didn't know about it until I came across this thread.

I would think it deserves more attention than it currently is. Something goes wrong with the readership of this forum..

 

[joe scian]

Recently there was a query regarding the Web History tracking to which I replied, so I thought I would revisit my crude script I posted.

I believe the Web History is now more mature, and probably can provide useful info, but extracting the info into a printable report is sadly lacking.

So here is my shoddy WebHistory_Report.sh script to allow queries to be made on the Web History database

Prerequisites:
1. Obviously Web History must be ENABLED
2. Entware must be installed (why isn't it? ) together with the 'sqlite3' utility (opkg install sqlite3-cli) (if not already silently installed by Skynet)

./WebHistory_Report.sh -h

#======================================================================================= © 2016-2018 Martineau v1.06
#
# Scan Web History database
#
#    WebHistory_Report     [help | -h] ['ip='{ip_address[,...]} ['flush']] ['url='{url_string[,...]}] ['nofilter'] ['email'] ['mode=or'] ['noscript']
#                          ['date='{yyyy/mm/dd[,...]}] ['time='{hh:mm:ss[,...]}] ['backup'] ['sqldb={path_to_database}']['purgeallreset']
#
#    WebHistory_Report
#                          Will list 'Todays' URL entries in the Web History database containing strings 'facebook' OR 'youtube'
#    WebHistory_Report     nofilter
#                          Will list ALL entries in the Web History database.
#    WebHistory_Report     nofilter email
#                          Will list ALL entries in the Web History database and will send an email with the results
#    WebHistory_Report     url=amazon,netflix
#                          Will list URL entries in the Web History database containing strings either 'amazon' OR 'netflix'
#    WebHistory_Report     ip=192.168.1.1 url=amazon,netflix time=18:,19: mode=or
#                          Will list URL entries in the Web History database for 192.168.1.1 or between 18:00-19:59 or URLs as above
#                          Without 'mode=or' then the database records must match ALL three criteria.
#    WebHistory_Report     date=2017/02/30
#                          Will list entries in the Web History database created on '30th Feb 2017'
#                          NOTE: The date specification can be an abbreviation e.g. '2017/02' for records created in 'Feb 2017'
#    WebHistory_Report     ip=10.88.8.123,192.168.1.99
#                          Will list entries in database for two devices - either '10.88.8.123' OR '192.168.1.99'
#                          NOTE: Only MAC addresses are stored in the database so if the devices are not 'reserved/static'
#                                then the report could be inaccurate.
#    WebHistory_Report     ip=10.88.8.123 flush url="www.veryexpensiveshoes.com"
#                          Will delete all URL 'www.veryexpensiveshoes.com' history for '10.88.8.123' Ha ha - wife mode eh? ;-)
#    WebHistory_Report     ip=10.88.8.123 flush
#                          Will delete all history for '10.88.8.123'
#    WebHistory_Report     time=09
#                          Will list entries in the Web History database created between '09:00' to '09:59'
#                          NOTE: A full time specification can be used e.g. '12:05:30' but the report may never find a match!
#    WebHistory_Report     backup
#                          The current Web History database will be backed up to '/opt/var/WebHistory/'
#    WebHistory_Report     sqldb=/opt/var/WebHistory/WebHistory.db-Backup-20180401-060000
#                          The report/queries will be extracted from the archive/backup database '/opt/var/WebHistory/WebHistory.db-Backup-20180401-060000'
#    WebHistory_Report     purgeallreset
#                          The current Web History database is PURGED of ALL history!!!!! (NOTE: a backup is taken first ;-)

e.g. Default filter in AND mode... i.e. both date= and url= must match
Find all URLs containing either of the strings 'facebook' or 'youtube' created 'today'

./WebHistory_Report.sh

View attachment 15121

Complex query using OR mode
e.g. Find all URLs containing any of the strings ( 'amazon' or 'kindle' or 'game'), or any records timestamped between '10:00-10:59' or any records for LAN device 10.88.8.157 (which happens to be a kids phone )

./WebHistory_Report.sh ip=10.88.8.157 url=amazon time=10 url=kindle,game    mode=or

View attachment 15120

I have similar scripts for Traffic Analyzer and AiProtection Monitor, but first I'd like the community to try out (aka debug ) the Web History script first to see if there is a need for such scripts.

As usual all/any feedback appreciated.

EDIT: If the Web History is large i.e. was enabled weeks ago?, then

./WebHistory_Report.sh   nofilter

will trundle slowly through ALL the database records on screen - starting with the first timestamped record and could take days

e.g. Database contains 116458 records
Script takes approx. 9 seconds before the first record is displayed on screen, then takes a further 8 seconds to fill the screen with the first 32 records.​

So, a quick tip to instantly dump/display ALL records in the database is to issue

./WebHistory_Report.sh   nofilter   noscript

and the 'raw' SQL records are returned within a few seconds.

i.e. the same database all 116458 records can be dumped/displayed on screen in <10 seconds! so if cosmetic formatting isn't required simple grep/sed/awk can be used to apply the filters.

Hi Martineau
just an observation - your example complex query above will pick up 19:10:53 and 22:17:10 as well as 10:15:46 - on my system.
any reason for that when we specify time=10?

 

[Martineau]

Hi Martineau
just an observation - your example complex query above will pick up 19:10:53 and 22:17:10 as well as 10:15:46 - on my system.
any reason for that when we specify time=10?

Its a feature!

The ability to specifiy 'time=10' or 'time=10:' is intentional but poorly implemented and I spotted it after v1.06 was posted.

Here is the change list for the as yet unreleased

WebHistory_Report v1.07

Fix - Missing space for SQL statement for explicitly filtering 'time=HH' rather than 'nn' anywhere in 'time'

[ -z "$TIME_SQL" ] && TIME_SQL=$TIME_SQL"(time LIKE '% "$TIME"%'" || TIME_SQL=$TIME_SQL" OR time LIKE '% "$TIME"%'" #v1.07 Fix

i.e.

 "time LIKE '$TIME'%"

change to

 "time LIKE ' $TIME'%"

Fix - Using the default filters with the single 'noscript' arg fails as "today's" date cannot match on the missing SQL 'time' column
Change - When dumping the raw SQL records e.g. 'noscript' specified, prefix the record with human-friendly YYYY-MM-DD HH:MM:SS
Change - Allow null args for 'date=' and 'time=' to imply "today's" date and the "current Hour", and 'mode' or 'mode=' implies 'OR'
Change - Enforce strict parsing order i.e. reject 'flush ip=' or orphaned 'flush'

and
'mode=' MUST precede any filters i.e. 'mode=or date= ip=192.168.111.123' is OK, but 'date= ip=192.168.111.123 mode=or' is INVALID​

Change - Reject invalid directives
Change - Optimise processing using built in shell commands rather than external utilities such as 'cut' etc.​

EDIT: 20/11/2018 v1.07 released
So you can fix the problem @LINE 358 whilst I decide when to push v1.07


Although I am still tinkering with the features, given the lack of interest for the three reports I might decide to abandon development.

 

[joe scian]

plenty of interest here !! Im surprised too that there isnt more interest. Hopefully for us you will get more hits.

 

[Martineau]

plenty of interest here !! Im surprised too that there isnt more interest. Hopefully for us you will get more hits.

Many thanks for the feedback.

So v1.07 of all three scripts see post #1 is now available.

 

[jtp10181]

I saw this post the other day on my phone and flagged it. I had web history shut off because the ASUS interface is useless. I had thought about trying to make my own script to read it but never followed through. Going to try this out. Thanks!
----
EDIT: I found that by default due to my paths it was trying to use the busybox standard 'find' utility which does not have the -type test and it would fail. I edited all the lines using find to have the full path of /opt/bin/find and that fixed it.

 

[Martineau]

EDIT: I found that by default due to my paths it was trying to use the busybox standard 'find' utility which does not have the -type test and it would fail.

I edited all the lines using find to have the full path of /opt/bin/find and that fixed it.

Many thanks for the feedback.

I assume you meant the 'find -type' command?

So presumably you only altered this one line ?

# Add 'executable' as 'stubby' leaves behind two directories containing the string 'stubby'
if [ -d /opt ] && [ -n "$(find /opt/ -type f -executable -name $ENTWARE_UTILITY)" ];then

as I'm sure the Busybox version of 'find' (subsequently used solely to search for available SQL database(s) ) should be OK.

However, I have added a Fix/sanity check in v1.08, but rightly or wrongly I assumed that Entware's 'find' utility was always installed when Entware is installed doh!

I have updated the Prerequisites accordingly.

 

[joe scian]

brilliant scripts Martineau - thanks heaps

 

[Martineau]

v1.08 Released

WebHistory_Report v1.08

Fix - Issue ***ERROR message if Entware version of 'find' (opkg install find-utils) isn't installed. Thanks @jtp10181
Change - The report will now use "Today's" date and the "current" hour by default. Use 'date=' or 'time=' to clear the date/time filters
New - Add 'sortby=' option for time, mac or url (IP is not held in database TBA) and column header in Green
New - Add 'count' option to display ONLY the number of filter results. (The matching records are not displayed on screen).
Change - Allow Hostnames to be used in 'ip=' filter specification e.g. 'ip=MyLaptop,192.168.1.99,iphone'
Change - Allow Group names defined in '/jffs/configs/IPGroups' to be used in 'ip=' filter specification e.g. 'ip=cameras,alexa'
New - Provide Summary of record count
Change - In the "Filter by" description; show IPs 'Filter by IP ==> 192.168.1.23' rather than MACs 'Filter by IP ==> XX:XX:XX:XX:XX:XX'​

TrafficAnalyzer_Report v1.08

Fix - Script will display incorrect Tx/Rx Totals when attempting to summarise more than 1GB traffic volumes

e.g. TOTAL=$((1191071409+2037987240)) returns '-1065908647'
​

As per Web History except 'sortby=' options are different​

AiProtectionMonitor_Report v1.08

As per Web History except 'sortby=' options are different​

 

[Martineau]

v1.09 Released

TrafficAnalyzer_Report v1.09

Fix - Rx byte counter is now 12-digits!! i.e. 999.99GB; and Tx Byte counter is now 11-digits!!

i.e. 99.99GB rather than 10-digits.​

Fix - Incorrectly checks NVRAM Web history enabled flag so refuses to run.

Use Trend Micro EULA as "**Warning" or "***Warning if Traffic Analyzer is NOT enabled" as the

script may need to be run against an INACTIVE/Archived SQL database.​

AiProtectionMonitor_Report v1.09

Fix - Incorrectly checks NVRAM Web history enabled flag so refuses to run.

Use Trend Micro EULA as "**Warning" or "**Warning if AiProtection NOT enabled" as the script may need to be run against an INACTIVE/Archived SQL database.​

 

[TicoMan]

Recently there was a query regarding the Web History tracking to which I replied, so I thought I would revisit my crude script I posted....

So I copied your script to /jffs/scripts, but when I try to run it I get "Not found" (Screenshots attached)

What am I doing wrong?

Appreciate the help

 

[dave14305]

So I copied your script to /jffs/scripts, but when I try to run it I get "Not found" (Screenshots attached)

What am I doing wrong?

Appreciate the help

You have to match your capitalization. Also chmod u+x /jffs/scripts/webhistory_report.sh

 

[TicoMan]

You have to match your capitalization. Also chmod u+x /jffs/scripts/webhistory_report.sh

Thanks Dave. So I ran the command with the right capitalization and indeed I got a Permission denied message, I then ran the chmod command you advised me, but after that one, I again got the Not Found message :/

 

[dave14305]

Thanks Dave. So I ran the command with the right capitalization and indeed I got a Permission denied message, I then ran the chmod command you advised me, but after that one, I again got the Not Found message :/

What if you run:

sh /jffs/scripts/webhistory_report.sh

 

[FreshJR]

Nice work, but not to be a downer but WebHistory has a flaw where it doesn't record HTTPS requests/domains due to that part of the packet header being encrypted.

Wouldn't it be better to have DNSMASQ logs all requests to an external thumb drive, then parse all DNS requests in the log file for stats. This would include both HTTPS & non-HTTPS domain requests per device.

I was thinking of making a WebUI stats page out of daily requests per DEVICE and also a tally per domain requested from raw DNSMASQ data.

 

[TicoMan]

What if you run:

sh /jffs/scripts/webhistory_report.sh

No luck yet...

 

[TicoMan]

...I was thinking of making a WebUI stats page out of daily requests per DEVICE and also a tally per domain requested from raw DNSMASQ data.

Pleaseee do it!