Hi everyone,
I made a script to get all of the connections from my router’s ip/firewall/connections and then aggregate all the IPs and sum up their download and upload rates so I can monitor the bandwidth used by IP.
Also fetched the DHCP server leases table and joined them by address with the connections table so I can see the host name of every specific IP address. Everything works pretty good, except the fact that connecting to an SSL secured router is pretty slow.
Here is the script code (maybe someone will also find it useful):
import librouteros as ros
import pandas as pd
import ssl
from librouteros.login import token
#api = ros.connect( #connecting to non-SSL encrypted router
# username='myusername',
# password='mypass',
# host='my.ip.address',
#)
ctx = ssl.create_default_context() #connecting to SSL encrypted router
ctx.check_hostname = False
ctx.set_ciphers('ADH:@SECLEVEL=0')
api = ros.connect(
username='myusername',
password='mypass',
host='my.ip.address',
ssl_wrapper=ctx.wrap_socket,
login_method=token,
port=8729
)
connections = api.path('ip', 'firewall', 'connection') #fetching all IP connections from router
leases= api.path('ip', 'dhcp-server', 'lease') #fetching all DHCP server IP leases from router
conn = pd.DataFrame.from_dict(connections) #converting ferched connections from dictionary to pandas dataframe format
conn.drop(conn.columns.difference(['src-address', 'orig-rate', 'repl-rate']), axis=1, inplace=True) #dropping irrelevant columns from dataframe
conn['src-address'] = conn['src-address'].str.split(':',expand=True) #removing port number from src-address fields
conn_grped = conn.groupby(['src-address'],sort=False, as_index=False).sum() #aggregating IPs by src-address and summing up respective DL and UL values
conn_grped.sort_values(by=['repl-rate'], inplace=True, ascending=False) #sorting IPs by DL rate
lease=pd.DataFrame.from_dict(leases) #converting fetched leases from dictionary to pandas dataframe format
lease.drop(lease.columns.difference(['address', 'host-name']), axis=1, inplace=True) #dropping irrelevant columns from leases dataframe
lease.rename(columns={"address": "src-address"}, inplace=True) #renaming address column in leases dataframe to match connections dataframe column
result = conn_grped.join(lease.set_index('src-address'), on='src-address') #joining leases table with conections table by address
print(result)
We have 3 routers out of which 2 are SSL encrypted (although the certificates are currently expired, don’t know if that’s a factor?).
When I connect using the first method (marked by comment), I get the results basically instantly. When I use the second method (also marked), the script takes about 5 seconds to get me the results.
Now, I don’t completely understand what the SSL methods do (ctx, ssl_wrapper etc.) as I copied the code from https://librouteros.readthedocs.io/en/latest/connect.html .
The routers do use pre 6.43 RouterOS versions (6.40 and 6.42) so I had to use the token method from the link above (as it states in the code).
Could that be the slower method, hence the slowing down of the script? Could updating the RouterOS versions speed the process up, by using the plain text method?
Keep in mind that I am doing this in a production environment so the only time I can do a RouterOS upgrade is on the weekends, that’s why they are a bit out of date (pls no blame).
Any advice on the situation is appreciated!