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

ColinTaylor

Part of the Furniture
No luck yet...
Looks like you have created the file in DOS/Windows format instead of Unix format. Run the following command to correct the file (and any other files you have created this way).
Code:
dos2unix /jffs/scripts/webhistory_report.sh
 

TicoMan

Occasional Visitor
Looks like you have created the file in DOS/Windows format instead of Unix format. Run the following command to correct the file (and any other files you have created this way).
Code:
dos2unix /jffs/scripts/webhistory_report.sh

That was it! thanks a lot for the help!
 

killeriq

Regular Contributor
just tried today and it works...
But i dont see any IP / Hostname, do i need to somehow specify it in the command?

@Martineau any clue?

Thanks

1641203665396.png
 

Martineau

Part of the Furniture

killeriq

Regular Contributor
i was on v386.3.2
i tired afterwards 386.4 and look like this:

1641210920320.png


Anyway thanks for fast action ! :)

btw is there possible to somehow track full URLs like in Web browser History? instead of "main URL" only?

Thanks
 

Martineau

Part of the Furniture

killeriq

Regular Contributor
All is good now :) great support!
--

btw is there possible to somehow track full URLs like in Web browser History? instead of "main URL" only?

Thanks
 

Martineau

Part of the Furniture
btw is there possible to somehow track full URLs like in Web browser History? instead of "main URL" only?
You would have to ask ASUS to implement this feature request.
 
Last edited:

killeriq

Regular Contributor
Hi @Martineau

1. after some time i ran it again and in hostname is showing just "?" even before it was showing host name properly. Any clue what might be wrong this time (Current Version : 386.4) ?
2. When i ran "sh WebHistory_Report.sh nofilter" or "nofilter report=WebReport.csv" its listing it, but is kind of slow , like one entry per 1 second.
So if i want to run backup for month, it takes ages.

Speed is dependable on Router? as i dont see any load on 4 core more then 5%. Any way to make it faster or issue is somewhere on my side?

Thanks


1643389848090.png
 

Martineau

Part of the Furniture
Hi @Martineau

1. after some time i ran it again and in hostname is showing just "?" even before it was showing host name properly. Any clue what might be wrong this time (Current Version : 386.4) ?
So when it was working, what has changed since?
2. When i ran "sh WebHistory_Report.sh nofilter" or "nofilter report=WebReport.csv" its listing it, but is kind of slow , like one entry per 1 second.
So if i want to run backup for month, it takes ages.

Speed is dependable on Router? as i dont see any load on 4 core more then 5%. Any way to make it faster or issue is somewhere on my side?

Thanks


View attachment 39006
It's no doubt probably my manky coding skills, but it definitely depends on the size of the database.

i.e. The fancy graphics/colourful formatting will take a toll, and as advised (in post #1- reproduced) below, you may need to simply eschew them and supply your own SQL Query direct, or in combination with external tools/filters:

1643397156367.png
 

killeriq

Regular Contributor
i haven't changed anything, as there was no new FW since , so no clue what is wrong.
Tried also Router restart and same behavior "?"

tried as you suggested: "./WebHistory_Report.sh nofilter noscript"

Or also "./WebHistory_Report.sh nofilter report=WebReport.csv nodisplay"

it went really fast with "nodisplay" or "noscript" option, but is ignoring "Local Host name"

"2022-01-15 20:15:27,30:A7:A2:AE:A1:D1,www.xxx.xxx"

Thanks
 

Martineau

Part of the Furniture
i haven't changed anything, as there was no new FW since , so no clue what is wrong.
Tried also Router restart and same behavior "?"

tried as you suggested: "./WebHistory_Report.sh nofilter noscript"

Or also "./WebHistory_Report.sh nofilter report=WebReport.csv nodisplay"

it went really fast with "nodisplay" or "noscript" option, but is ignoring "Local Host name"

"2022-01-15 20:15:27,30:A7:A2:AE:A1:D1,www.xxx.xxx"

Thanks
Do any of the following produce output

Code:
cat /etc/dnsmasq.conf | grep "^dhcp-host"

nvram get dhcp_staticlist

grep "30:A7:A2:AE:A1:D1" /etc/dnsmasq.conf
 

killeriq

Regular Contributor
i tried "cat /etc/dnsmasq.conf | grep "^dhcp-host" and give this...
this - nvram get dhcp_staticlist - make the same but all in single like, no host names
1644434497810.png



grep "30:A7:A2:AE:XX:XX" /etc/dnsmasq.conf

1644434645587.png
 

killeriq

Regular Contributor
One more thing all values were like 15-18h old last 2 days, so i thought maybe is full log or what

So I tried to "clear" in UI , then shutdown, 2min wait and boot ...

Now the hostname still shows "?" on all values...

dont want to do factory reset for now...aaah :(
 

killeriq

Regular Contributor
so i ended up with Factory reset, as many things were wrong, no clue why...
will see how it goes few days, then i try your script again as i have no clue what is causing those strange problems

 

killeriq

Regular Contributor
today i tried your script again...all seems fine in the command window. All is there
YYYY/MM/DD HH:MM:SS MAC address Host Name IP address URL

But when i want to export "nofilter report=WebReport.csv nodisplay"
It creates CSV file only with Date,MAC,URL

How to have it with Host Name and IP as well? Its easier to identify what is what

Thanks!
 

Martineau

Part of the Furniture
today i tried your script again...all seems fine in the command window. All is there
YYYY/MM/DD HH:MM:SS MAC address Host Name IP address URL

But when i want to export "nofilter report=WebReport.csv nodisplay"
It creates CSV file only with Date,MAC,URL

How to have it with Host Name and IP as well? Its easier to identify what is what

Thanks!
Unfortunately the ASUS database doesn't contain the Hostname/IP

i.e. if you issue
Code:
sqlite3 /jffs/.sys/WebHistory/WebHistory.db "select datetime(timestamp, 'unixepoch', 'localtime') AS time, mac, url FROM History;"
the displayed records only contain three fields/columns - Date,MAC,URL

However, you can try these three commands
Code:
sqlite3 /jffs/.sys/WebHistory/Addons.db "CREATE TABLE IF NOT EXISTS hosts(mac TEXT,hostname TEXT,ip TEXT);"
Code:
sqlite3 /jffs/.sys/WebHistory/Addons.db "DELETE FROM hosts";arp -a | tr -d '()' | awk '{print toupper($4)","$2","$1}' | sqlite3 -csv /jffs/.sys/WebHistory/Addons.db ".import '|cat -' hosts"; sqlite3 /jffs/.sys/WebHistory/Addons.db "SELECT * FROM hosts"
Code:
sqlite3 /jffs/.sys/WebHistory/WebHistory.db "attach '/jffs/.sys/WebHistory/WebHistory.db' as asus;attach '/jffs/.sys/WebHistory/Addons.db' as killeriq;select datetime(timestamp, 'unixepoch', 'localtime') AS time, x.hostname, x.ip, h.mac, h.url from asus.history h join killeriq.hosts x on h.mac = x.mac;" | sort
 

Latest threads

Sign Up For SNBForums Daily Digest

Get an update of what's new every day delivered to your mailbox. Sign up here!
Top