Messing With Mikrotik - Part II

Getting useful logs from Mikrotik

Edits 2020/11/7:
Updated the python2.7 call to python3
Published additional cron tasks for midnight counter reset.

Equipment:
Renewed Dell 7010 running Ubuntu 20.04
- syslog-ng
- Splunk

We’ll be focusing on 3 primary sources of logs: The firewall logs, the accounting logs, and the mangle logs. Combined this should be enough to indicate what devices are contacting what devices on what ports, and in what amounts (bytes). I’ve gone after this traffic specifically because I want to provide a check to what my ISP has as my internet usage. It’s also good practice to see what destinations in home devices may be contacting.

In your Mikrotik GUI, you’ll find logging under the system menu. Go to System → Logging → Actions, and add a new action. Set up a new remote action, the naming convention is your choice. I use syslog:

Out of habit, I keep the report ports above 1024 to avoid system restrictions. Click Apply/Ok, and it’ll drop you back to the Actions Menu. Go to Rules and Add New:

Before we go and enable the firewall rules to actually log, we’ll need to get syslog-ng set up. I’ve installed syslog-ng on Ubuntu with a sudo apt-get install syslog-ng command. This is my configuration that lives in /etc/syslog-ng/syslog-ng.conf

# /etc/syslog-ng/syslog-ng.conf
# Used for capturing syslog streams to home log server

Save and quit out of the file. I have the files being written out with a Splunk group, which my Splunk user will be apart of for later reading. If you’d like to confirm syslog-ng has been configured properly (don’t forget to (re)start the application), don’t forget your pants:

netstat -pant | grep -e ":1234"

With syslog-ng now listening, we can ssh into the router, and begin enabling syslog out for firewalls.

ssh admin@192.168.85.1

That’s all that is required to get logs like this:

Oct 19 14:18:28 192.168.85.1 firewall,info forward: in:ether1 out:bridge, src-mac 00:b2:4e:33:f2:19, proto UDP, x.x.x.x:53722->192.168.85.12:62181, NAT x.x.x.x:53722 ->(x.x.x.x:51233->192.168.85.12:62181), len 78

Accounting requires a little bit of a work around. These logs enable the user to see information like this:

192.168.85.14 x.x.x.x 282 7

Internal IP communicated with external IP, exchanging 282 bytes of information over the course of 7 packets. I’m more interested personally in the byte aggregation of my devices and household overall. I’ve used a two step process for this (taking suggestions).

For Splunk, I use the following monitor statement and props.conf entries to ingest these logs:

# inputs.conf located in /opt/splunk/etc/apps/trex/local/

On the Router’s GUI, go to IP → Accounting. Select Enable Accounting. I’ve set my threshold to 2500, knowing I’m going to be accessing the table and pulling it every minute. By accessing the table for a snapshot you clear it.

Click Web Access and enable access via the web. I’ve limited the access to just my immediate logging system (192.168.85.191/32). This prevents me from accidentally accessing the page from another system before it’s been logged.

With the web access enabled, you need to pull the information from it. This is a basic (and boy howdy do I mean basic) python script to pull the information:

# Python for pulling mikrotik accounting logs

I’ve created a directory in /opt/data/ for scripts. Substitute the url for your http://dns or ip of your router. This outputs the accounting page. However, it also appends the information with a double * *, which Splunk has trouble with. So I set a Cronjob to run the script, and remove those characters.

* * * * * python3 /opt/splunk/etc/apps/mercer/bin/mikrotik_accounting.py > /opt/data/scripts/accounting_raw.log && cat /opt/data/scripts/accounting_raw.log | sed "s/*//g" > /opt/data/scripts/accounting.log

I’ve stored the python script in my home splunk app directory so I can make changes later on. This takes the _raw output, and writes it to a log file, which we can monitor for in Splunk.

# inputs.conf located in /opt/splunk/etc/apps/trex/local/

I hope this helps you if you are trying to figure out just how much bandwidth that iPad that seems to be streaming the Hallmark channel is really pulling.

Update 2020/10/21

After messing about with Accounting and not feeling like it was pulling the full set of stats, I’ve added another source of information from Mikrotik in the form of the Mangle logs.

In a similar fashion to the accounting logs, I have a piece of python reaching out and logging into the router, pulling the mangle read out to a file.
Script:

import paramiko

I’ve then scheduled it in Cron on the system, running at the top of every hour. Except when the mangle counter needs to be cleared. So we have to bifurcate the calls into the top of the hour for each hour between 01 and 2300, and again at 2359 just before the midnight reset. The reset is the same script but it replaces the command with /ip firewall mangle reset-counters-all

59 23 * * * python3 /opt/splunk/etc/apps/mercer/bin/mikrotik_mangle.py > /opt/data/scripts/mangle.log

The result looks something like this:

Bringing that into Splunk with a monitor statement is easy enough, and all I need to set for the custom sourcetype is the props for line merging. I’ve also got data extraction going on the postrouting portion of the log.

# inputs.conf

Now I’m trying to reconcile the accounting logs, and mangle count to see if I can get them to match. The sum of the bytes from each in the same timeframe should be close if not matching. So far that has not been the case.

A data loving dinosaur, usually found on http://splk.it/slack Trust Cohort 2018,2019,2020,2021; Amateur Cook, (he/him)