I am playing with the Traffic Analyzer reports. They are very interesting.
I am able to open the sqlite database storing raw data used to generate the reports.
/jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db
It has a traffic table which stores five columns for each traffic record:
* mac : MAC address
* app_name: Application name
* cat_name: category name
* timestamp
* tx: outgoing traffic bytes
* rx: incoming traffice bytes
I was wondering how to obtain URL information also for each record? So the traffic data is more interesting?
//------------------------
I tried to join the traffic table with the history table of the Web History sqlite table (WebHistory.db). It has:
* mac
* timestamp
* url
select *
from history, TrafficAnalyzer.traffic
where history.mac = TrafficAnalyzer.traffic.mac
and history.timestamp = TrafficAnalyzer.traffic.timestamp
However, only a very few records from two tables match. For several hours records, only two timestamps match.
So it seems like the timestamps from two tables do not equal to each other.
Some example results of the sql joining two tables (history and traffic):
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AE:xx HTTP Web services 1612658460 80 40
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AE:4xx Google APIs(SSL) Network protocols 1612658460 1977 5808
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AExx Google Web services 1612658460 1742 5949
//------------------------
This begs more fundamental questions:
Should web history records a subset of traffic records in the first place ?
Or them capture totally different things at different layers?
Is there a way to extract url information for more detailed traffic information? My understanding is packets should have source and destination urls.
I am able to open the sqlite database storing raw data used to generate the reports.
/jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db
It has a traffic table which stores five columns for each traffic record:
* mac : MAC address
* app_name: Application name
* cat_name: category name
* timestamp
* tx: outgoing traffic bytes
* rx: incoming traffice bytes
I was wondering how to obtain URL information also for each record? So the traffic data is more interesting?
//------------------------
I tried to join the traffic table with the history table of the Web History sqlite table (WebHistory.db). It has:
* mac
* timestamp
* url
select *
from history, TrafficAnalyzer.traffic
where history.mac = TrafficAnalyzer.traffic.mac
and history.timestamp = TrafficAnalyzer.traffic.timestamp
However, only a very few records from two tables match. For several hours records, only two timestamps match.
So it seems like the timestamps from two tables do not equal to each other.
Some example results of the sql joining two tables (history and traffic):
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AE:xx HTTP Web services 1612658460 80 40
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AE:4xx Google APIs(SSL) Network protocols 1612658460 1977 5808
8C:AE:xxx 1612658460 mycyberuniverse.com 8C:AExx Google Web services 1612658460 1742 5949
//------------------------
This begs more fundamental questions:
Should web history records a subset of traffic records in the first place ?
Or them capture totally different things at different layers?
Is there a way to extract url information for more detailed traffic information? My understanding is packets should have source and destination urls.
