View stats by date

[flewid]

Can we view stats by date - I am trying to monitor my bandwidth month to month and would like to check a specific period?

[Nixellion]

Not currently, it's something I plan on adding.
Database already keeps monthly record of bandwidth usage, it has two tables - one for each entry in accounting, and another is monthly aggregation. So adding monthly table should be simple, but specifying period may be trickier.

I am trying to wrap my head around the peewee SQL request that needs to be built to present a sum for date range. I can just count it in python, but SQL request would be 10 times faster than counting it manually line by line.

But maybe I'll just add it first and optimize it later, seems fair.

[flewid]

Ah yeah even just month to month is cool for my particular needs.

[Nixellion]

Added new page /months where you can view data for all months or use /months?month=5 in URL to show specific months. Kinda hacked together quickly, sorry for poor UI\UX :D

Honestly I don't plan for this tool to become something large and complicated to compete with traffic flow analyzers, it's just something to get at least some glance on your data usage on Router OS through Accounting table. Because setting up any of the big NetFlow\TrafficFlow analyzers is a pain. At least I did not find any that would be easy to install, free and work well. Zabbix is a disaster of multiple docker containers at best, ntop now requires you to buy ntopng license to work with Mikrotik, Wireshark requires hooking it up to an unmanaged hub to sniff network's traffic... sigh

But I will keep updating it with small updates and features. But if someone steps in to help add more features then yay, I'm all for it.

[flewid]

Yeah I hear that. I spent a week on Cacti and it was still a confusing mess! This is perfect for basic accounting.

The reason I wanted dates was my contract with my ISP starts on the 4th of every month and I'm severely limited in bandwidth so I wanted to check each month in comparison to what their statistics say.

Which leads me to two additional questions;

1. Is it possible to make it show me 'total bandwidth in and out' on an interface of my choosing for the totals at the top (I assume that is cumulative of all IP's registered in accounting).

2. Is it possible to hide some ip's from the interface - for example, not really sure i need to see the broadcast addresses (255.255.255.0 etc)

I would love to help, but I don't know much about python - however, i can help with documentation !

[Nixellion]

Right now all you see is all it does.

1. It's not possible to choose interface, I don't think so, because this tool can only show what accounting shows. All it does is collects data from accounting table, and it only has dst and src IPs and bytes in and out, you can view accounting table in RouterOS UI. That's all the info it has.

2. Hm, odd, I only seem to see LAN IPs of devices. I could add ignore list for IPs into config file or something? Would that help? It will exclude IP from collecting into database completely.

[flewid]

1. Okay makes sense.

2. So I see a ton of IP's, see this screengrab. My LAN is 10.0.20.x and one of these is my external IP but then the rest????

[flewid]

https://www.dropbox.com/s/bk624e1q4z38pug/screencapture-10-0-20-10-1234-2020-06-11-07_17_43.png?dl=0

[Nixellion]

Interesting. I think the error is somewhere in Collector class, it probably fails to properly filter your LAN, however I checked, and for "10.0.20.1" it parses LAN into "10.0.20." which seems to be correct.

I've made some adjustments to check not only source address but also destination for either of them to have lan address. Before it was only checking if src is your LAN, and then assuming that if not then it must be dst. I'm not sure, but maybe that's it. At least it's the first thing that came to mind, will need to investigate further.

Please update and try again after deleting database between restarts.

[flewid]

Cool will do - how do i delete the database?

[Nixellion]

Sorry for late reply, just remove the .db file that's inside a 'data' folder. IF you have shm and wal files there remove them too, I think those are sqlite cache files, sometimes they dont get deleted if you close the app

[Nixellion]

@flewid I also found a script for RouterOS that creates simple queues for each client device, it's hooked to DHCP lease event. This allows you to monitor live traffic for each device, as well as record some data at least while the device is connected, as script removes the queue when it disconnects. But it can be adjusted to not do that too.

It should also record used traffic on mikrotik and draw graphs. I'm thinking about alllowing to use data from there as well to feed into the database.

[flewid]

okay great - Just deleted db, pulled changes, and will check. I'd be interested in checking out the script as well to see what it does - can i 'stack' scripts - i already have a script to associate my dns entries to dhcp so i can look up my computers locally - not a huge deal but something to ask :)

[Nixellion]

It's something like this:

:local queueName "Guest $leaseActMAC";

:if ($leaseBound = "1") do={
    /queue simple add name=$queueName parent=GUESTS place-before="Guest STATIC" target=($leaseActIP . "/32") max-limit=10M/10M comment=[/ip dhcp-server lease get [find where active-mac-address=$leaseActMAC && active-address=$leaseActIP] host-name];
} else={
    /queue simple remove $queueName
}

[flewid]

Great thanks. I have implemented the script and will keep an eye on things :) does this script auto run every day or something?

[Nixellion]

You're supposed to put it under IP - DHCP Server - your dhcp (defconf by default) - double click it to bring up it's properties, at the bottom there will be a "Lease script" field. You should put it there.

It runs every time a DHCP lease is issued, as in every time a device connects or disconnects from the router. So phone connected to the router, it got it's own queue with the script. It disconnected, queue removed.

[flewid]

Ah okay so my full code there now is

When "1" all DNS entries with IP address of DHCP lease are removed

:local dnsRemoveAllByIp "1"

When "1" all DNS entries with hostname of DHCP lease are removed

:local dnsRemoveAllByName "1"

When "1" addition and removal of DNS entries is always done also for non-FQDN hostname

:local dnsAlwaysNonfqdn "1"

DNS domain to add after DHCP client hostname

:local dnsDomain "thelake.lan"

DNS TTL to set for DNS entries

:local dnsTtl "00:15:00"

Source of DHCP client hostname, can be "lease-hostname" or any other lease attribute, like "host-name" or "comment"

:local leaseClientHostnameSource "lease-hostname"

:local leaseComment "dhcp-lease-script_$leaseServerName_$leaseClientHostnameSource"
:local leaseClientHostname
:if ($leaseClientHostnameSource = "lease-hostname") do={
:set leaseClientHostname $"lease-hostname"
} else={
:set leaseClientHostname ([:pick
[/ip dhcp-server lease print as-value where server="$leaseServerName" address="$leaseActIP" mac-address="$leaseActMAC"]
0]->"$leaseClientHostnameSource")
}
:local leaseClientHostnameShort "$leaseClientHostname"
:local leaseClientHostnames "$leaseClientHostname"
:if ([:len [$dnsDomain]] > 0) do={
:set leaseClientHostname "$leaseClientHostname.$dnsDomain"
:if ($dnsAlwaysNonfqdn = "1") do={
:set leaseClientHostnames "$leaseClientHostname,$leaseClientHostnameShort"
}
}
:if ($dnsRemoveAllByIp = "1") do={
/ip dns static remove [/ip dns static find comment="$leaseComment" and address="$leaseActIP"]
}
:foreach h in=[:toarray value="$leaseClientHostnames"] do={
:if ($dnsRemoveAllByName = "1") do={
/ip dns static remove [/ip dns static find comment="$leaseComment" and name="$h"]
}
/ip dns static remove [/ip dns static find comment="$leaseComment" and address="$leaseActIP" and name="$h"]
:if ($leaseBound = "1") do={
:delay 1
/ip dns static add comment="$leaseComment" address="$leaseActIP" name="$h" ttl="$dnsTtl"
}
}

:local queueName "Guest $leaseActMAC";

:if ($leaseBound = "1") do={
/queue simple add name=$queueName parent=GUESTS place-before="Guest STATIC" target=($leaseActIP . "/32") max-limit=10M/10M comment=[/ip dhcp-server lease get [find where active-mac-address=$leaseActMAC && active-address=$leaseActIP] host-name];
} else={
/queue simple remove $queueName
}

[flewid]

is that okay?

[flewid]

hey! it seems to be working - I don't have any of those extra ip's anymore in my list.

https://www.dropbox.com/s/x45fgzhu66kid8o/Screenshot%202020-06-20%2000.16.41.png?dl=0

questions:

1. is it normal that it takes like 3 minutes for the page to load on a rpi 3a ?

2. is it possible to add a column with 'hostname' ?

[flewid]

also now because of this script , i'm missing the ip on the 'bridge' interface (ie: my external ip) which is the main one i want to know total in/out on is it possible to 'grep' that out of the script or whatever?

[Nixellion]

also now because of this script , i'm missing the ip on the 'bridge' interface (ie: my external ip) which is the main one i want to know total in/out on is it possible to 'grep' that out of the script or whatever?

What do you mean? DHCP lease script should not affect anything and frankly it's not part of this project either. This dashboard only affects Accounting table. When I get into "bridge1" details on my router I can still see traffic counters there, since last reboot at least, but it's not persistent anyway.

I also had to turn fasttrack back on to make things a bit faster, so I'm rethinking the whole thing about how to track traffic usage.

[flewid]

Sorry that was confusing :)

• Make sense on the speed increase thanks.

• So, now that I have enabled this script, I ONLY see 10.0.20.x ip's. However my bridge interface gets a 192.168 address (I am behind double nat). So I would like to see it in the report as well so I can verify the bandwidth/in/out as a whole for the entire network to the ISP. Does that make sense?