Per IP Traffic accounting

[mekabe remain]

Hi,

I am finally migrating to a AX88U from AC88U
But I could not find how to enable Per IP Traffic accounting on this.
I use the same Merlin version on both AC88U and AX88U
On AC88U I have the option under Tools -> Other Settings -> Traffic Monitoring
But it is not there on the AX88U

where is it ?

 

[ColinTaylor]

where is it ?

It doesn't exist. It's not compatible with the new routers.

 

[mekabe remain]

so how can I get a view of which device used how much traffic ?
I was getting the output to my influx db and display with Grafana.
now how can I measure ?

 

[ColinTaylor]

I don't use it but I believe you need to enable the Traffic Analyzer. I think the data is stored in an SQLite database. IIRC there are some user scripts posted on the forum that can extract the data.

 

[mekabe remain]

ok I already enabled Traffic Analyzer.
How can I find those scripts that can extract per IP accounting data ?

 

[ColinTaylor]

How can I find those scripts that can extract per IP accounting data ?

Try using the "Better Search" function at the top of the page.

 

[eightiescalling]

ok I already enabled Traffic Analyzer.
How can I find those scripts that can extract per IP accounting data ?

If they're the scripts I'm thinking of you'll want to look for rstats and cstats. From memory being router stats and client stats - I seem to recall they break down in to 2 minute chunks of data but most of the scripts I saw kicking around the internet were fairly old. For some reason searching for them against Tomato rather that Asus or Merlin found them more reliably - can't comment on whether they're still valid against current firmware though.

 

[ColinTaylor]

If they're the scripts I'm thinking of you'll want to look for rstats and cstats..

No they're for the old Traffic Monitor and IPTraffic files.

 

[eightiescalling]

No they're for the old Traffic Monitor and IPTraffic files.

That'll be the pre-SQLite world then - thanks for correcting me. It was a while ago...

 

[mekabe remain]

I am confused.
So rstats / ctats are not useful with AX88U ?

GitHub - VREMSoftwareDevelopment/rstats-cstats: Tomato USB Python scripts to dump RStats and CStats information

Tomato USB Python scripts to dump RStats and CStats information - VREMSoftwareDevelopment/rstats-cstats

github.com

how can I access sqlite ?
I can write my own script if someone tell me where I can find the data.

 

[mekabe remain]

I found that with below command I can reach a table named "traffic"
But that is a table with MAc address , service name and numeric data.

example:
00:0F:06:CF:C7:CE|DNS|Management tools and protocols|1630785605|116|180
00:0F:06:CF:C7:CE|Network Time Protocol|Network protocols|1630785605|76|76
00:0F:06:CB:19:58|DNS|Management tools and protocols|1630785605|60|180
00:0F:06:CB:19:58|Network Time Protocol|Network protocols|1630785605|76|76
00:03:FC:11:92:32|Network Time Protocol|Network protocols|1630785605|76|76

how can I get "per device" total input/output ?

Other DB files on router:
# find / -name *.db
/tmp/bwdpi/bwdpi.bndwth.db
/tmp/bwdpi/bwdpi.beh.db
/tmp/bwdpi/bwdpi.app.db
/tmp/bwdpi/bwdpi.cat.db
/tmp/bwdpi/bwdpi.appdb.db
/tmp/bwdpi/bwdpi.devdb.db
/tmp/bwdpi/bwdpi.rule.db
/tmp/diag_db_cloud/upload/conn_diag_1630713600_1630786625.db
/tmp/diag_db_cloud/upload/conn_diag_1630713600_1630775192.db
/tmp/db/visdata.db
/tmp/mnt/router/.minidlna/files.db
/tmp/mnt/router/entware/share/spdmerlin.d/spdstats.db
/tmp/mnt/router/entware/share/connmon.d/connstats.db
/tmp/mnt/data/.minidlna/files.db
/jffs/.sys/WebHistory/WebHistory.db
/jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db
/jffs/.sys/nc/nt_center.db
/jffs/.sys/diag_db/conn_diag_1630713600.db
/jffs/.sys/diag_db/conn_diag_1630713600_1630786625.db

thanks

 

[eightiescalling]

I was clearly half asleep earlier...

So the following is an extract (scrubbed!) of a script I put on a friends AX router because he wanted to get an alert if a client downloaded above a threshold in a day - grab it from the SQLite database and uses Pushover to send the alert.

#!/bin/sh

# Variables - mac address of the client, where teh DB is and how many MB over how many days is of interest
CLIENT_NAME="hostname"
CLIENT_MAC="12:34:56:78:90:AB"
TRAFFIC_DB="/jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db"
RX_THRESHOLD=120
RX_WINDOW=1

## Pushover configuration
PUSHOVER_APP="app-hash"
PUSHOVER_USER="user-hash"


SQL_QUERY="SELECT IFNULL((SUM(rx)/1024/1024), 0) FROM traffic WHERE mac='${CLIENT_MAC}' AND datetime(timestamp, 'unixepoch')>datetime('now', '-${RX_WINDOW} day')"

SQLITE_CMD=`which sqlite3`
CURL_CMD=`which curl`

echo "${SQL_QUERY}"

if [ -x ${SQLITE_CMD} ]; then
  VALUE=`${SQLITE_CMD} "${TRAFFIC_DB}" "${SQL_QUERY}"`
  echo ${VALUE}
  if [ ${VALUE} -gt ${RX_THRESHOLD} ]; then
    if [ -x ${CURL_CMD} ]; then
      PUSH_MESSAGE="${CLIENT_NAME} has downloaded more than ${RX_THRESHOLD}MB over the last ${RX_WINDOW} days."
       echo ${PUSH_MESSAGE}
      ${CURL_CMD} -F "token=${PUSHOVER_APP}" -F "user=${PUSHOVER_USER}" -F "message=${PUSH_MESSAGE}" https://api.pushover.net/1/messages.json
    fi
  fi
fi

 

[mekabe remain]

I used something like below:
sqlite> select mac,sum(tx),sum(rx),timestamp from traffic group by mac order by timestamp;

but the timestamps in this table are pretty old. (2 hours earlier than live at most)
I need live data.

 

[mekabe remain]

I was clearly half asleep earlier...

So the following is an extract (scrubbed!) of a script I put on a friends AX router because he wanted to get an alert if a client downloaded above a threshold in a day - grab it from the SQLite database and uses Pushover to send the alert.

#!/bin/sh

# Variables - mac address of the client, where teh DB is and how many MB over how many days is of interest
CLIENT_NAME="hostname"
CLIENT_MAC="12:34:56:78:90:AB"
TRAFFIC_DB="/jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db"
RX_THRESHOLD=120
RX_WINDOW=1

## Pushover configuration
PUSHOVER_APP="app-hash"
PUSHOVER_USER="user-hash"


SQL_QUERY="SELECT IFNULL((SUM(rx)/1024/1024), 0) FROM traffic WHERE mac='${CLIENT_MAC}' AND datetime(timestamp, 'unixepoch')>datetime('now', '-${RX_WINDOW} day')"

SQLITE_CMD=`which sqlite3`
CURL_CMD=`which curl`

echo "${SQL_QUERY}"

if [ -x ${SQLITE_CMD} ]; then
  VALUE=`${SQLITE_CMD} "${TRAFFIC_DB}" "${SQL_QUERY}"`
  echo ${VALUE}
  if [ ${VALUE} -gt ${RX_THRESHOLD} ]; then
    if [ -x ${CURL_CMD} ]; then
      PUSH_MESSAGE="${CLIENT_NAME} has downloaded more than ${RX_THRESHOLD}MB over the last ${RX_WINDOW} days."
       echo ${PUSH_MESSAGE}
      ${CURL_CMD} -F "token=${PUSHOVER_APP}" -F "user=${PUSHOVER_USER}" -F "message=${PUSH_MESSAGE}" https://api.pushover.net/1/messages.json
    fi
  fi
fi

from your script I wrote this query:
SELECT mac,IFNULL((SUM(rx)/1024), 0) FROM traffic WHERE datetime(timestamp, 'unixepoch')>datetime('now', '-10 minute') group by mac;

and it populates mac addresses with rx/1024
Can I assume that all lines are for the last 10 minutes ?
If yes, I will run it every 10 minutes and push to my influxDB

Btw, how can I pair the replace the mac address with host names ? is there a table which holds mac-hostname or mac-ip pairs ?

 

[eightiescalling]

In my case it only needed to run once a day so I didn't worry too much about 10 minutes vs hourly. Best bet would be to spend a bit of time looking at the data and see - suggest taking a copy of the DB for that while experimenting though!

There only seems to be the single traffic table in the DB but given it is historical data and mac is the only fixed point (instead of user controlled hostname of DHCP allocated IP) that makes sense. You may need to grab a copy of lease allocations each time (maybe from /var/lib/misc/dnsmasq.leases) and patch up after the fact. Depends how much your DHCP leases vary across devices as to how much that messes up any queries you later do based on IP/hostname.

 

[mekabe remain]

interesting but "/var/lib/misc/dnsmasq.leases" is too short in my case.
I only see about 25 IP addresses leased where I have more than 50 clients.

so not all mac addresses pair with IP addresses in the lease file.

sqlite3 /jffs/.sys/TrafficAnalyzer/TrafficAnalyzer.db "SELECT lower(mac),IFNULL((SUM(rx)/1024), 0
) FROM traffic WHERE datetime(timestamp, 'unixepoch')>datetime('now', '-100 minute') group by mac;"  |sed 's/|/ /g' | while read a b
do
z=`cat /var/lib/misc/dnsmasq.leases | awk '{print $2,$3}' | grep $a`
echo $z $b
done

 

[mekabe remain]

why can't we enable ipt_acct on this device ?
really this s.cks...

the table does not include live traffic.
for example I start a speedtest on my PC
I can't see it in the table.

 

[ColinTaylor]

why can't we enable ipt_acct on this device ?

http://www.snbforums.com/threads/duplicate-iptables-rules.44003/post-373239

 

[mekabe remain]

ok. even if it has bugs can't we enable it adding a module ?

 

[ColinTaylor]

ok. even if it has bugs can't we enable it adding a module ?

You have an AX88U (HND):

poking at private kernel structures (which is no longer permitted under 3.x, which is why that module doesn't work at all on the HND platform)