Home > World Of ICT > Mrogram Part-3 Python Mikrotik IPv4 connection

Mrogram Part-3 Python Mikrotik IPv4 connection


Part 3  saya membahas bagaimana python membaca nilai IPv4 Firewall Connection  count lalu menyimpan ke database dan memunculkan data statistik dalam bentuk grafik, hasilnya seperti dibawah

ipfirewall

1. Buat File di Mikrotik:  
/file print file=ipfirewall

2. Buat Script dan Scheduler di Mikrotik:
:local first [/ip ipfirewall connection print count-only brief];
/file set ipfirewall.txt contents= \ "  $first "

Buat Scheduler

;;; ipfirewall COUNT
name="ipfirewall" start-date=jan/02/1970 start-time=05:00:00 
interval=1m4s on-event=ipfirewall owner=mahmudsaja 
policy=reboot,read,write,policy,test,password,sniff,sensitive 
run-count=12353 next-run=10:36:00 

3. Buat Database
mysql> show tables;
+--------------------+
| Tables_in_mikrotik |
+--------------------+
| user_host          |
| user_sso
| user_dnscache      |
+--------------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE `user_ipfirewall` ( `waktu` datetime DEFAULT NULL,  `ipfirewall` int(40) DEFAULT NULL);                    Query OK, 0 rows 

affected (0.13 sec)

4. Isi Direktori

root@radius:/mahmud/python/mikrotik/ipfirewall# ll
total 28
drwxr-xr-x 2 root root 4096 Mar  7 10:01 ./
drwxr-xr-x 3 root root 4096 Mar  7 09:38 ../
-rwxr-xr-x 1 root root    4 Mar  7 09:58 ipfirewall.txt*
-rwxr-xr-x 1 root root 1356 Mar  7 09:38 plot.py*
-rwxr-xr-x 1 root root  681 Mar  7 09:58 readhost.py*
-rwxr-xr-x 1 root root  108 Mar  7 09:38 runprog.sh*
-rwxr-xr-x 1 root root  104 Mar  7 09:38 runplot.sh*
root@radius:/mahmud/python/mikrotik/ipfirewall#

5. Buat Script Python Read FTP File

import datetime
now = datetime.datetime.now()
jam= now.strftime("%Y-%m-%d %H:%M")
import MySQLdb as mdb
import sys
import ftplib

path = ''
filename = 'ipfirewall.txt'
ftp = ftplib.FTP("192.168.xxx.123")
ftp.login("mahmudsaja", "SukaSukaMahmud")
ftp.cwd(path)
ftp.retrbinary("RETR " + filename ,open(filename, 'wb').write)
ftp.quit()

f = open('dnscache.txt').readlines()[0]
baca= int(f)

conn = mdb.connect("localhost", "root", "OkeOkeSaja", "mikrotik");
with conn:
  cursor = conn.cursor ()
  cursor.execute ("SELECT * FROM user_ipfirewall")
  cursor.execute ("""INSERT INTO user_ipfirewall(waktu,ipfirewall) VALUES(%s,"%s")""" , (now,baca))
  cursor.close()

6. Buat Script Python Plot Graph

##By-Gigih-F, iseng-iseng aja : 21-06-2012 16:38 WIB)
import matplotlib
matplotlib.use('Agg')
import Image
import MySQLdb as mdb
import sys
import matplotlib.pyplot as plt
import datetime

now = datetime.datetime.now()
print now
#Inisialisasi ke Database
conn = mdb.connect('localhost', 'root', 'OkeOkeSaja', 'mikrotik');

with conn:
   cursor = conn.cursor ()
   cursor.execute ("SELECT user_ipfirewall.waktu, user_ipfirewall.ipfirewall  FROM user_ipfirewall WHERE DATE (waktu) = DATE(NOW()) ")
   row = cursor.fetchall()
   print "RESULT:", row[0]
   waktu, user=zip(*row) #Fetching setiap row

fig = matplotlib.pyplot.gcf()
fig.set_size_inches(18.5,10.5)

ax = plt.subplot(111)
plt.plot(waktu, user,marker='^',linestyle='-',color='g',label='Total ipfirewall Lease')

plt.legend(loc=10)
plt.ylabel('Total ipfirewalldi jaringan UnilaNET')  #Label pada sumbu Y
plt.xlabel('Waktu')  #Label pada sumbu X
plt.grid()
plt.title('Statistik ipfirewallUnilaNET  per tanggal : [%s] (Credit to RaspberryPi+Python+MySQL)'%(now))  #Judul Grafik
plt.savefig('/mahmud/python/mikrotik/ipfirewall/ipfirewall.png', dpi=100) #Simpan file dengan judul xxx.png
Image.open('/mahmud/python/mikrotik/ipfirewall/ipfirewall.png').save('/mahmud/python/mikrotik/ipfirewall/ipfirewall.jpg','JPEG')
#plt.show() #Tampilkan hasil script

7. Liat isi direktori apakah plotting sudah berhasil
root@radius:/mahmud/python/mikrotik/ipfirewall# ll
total 204
drwxr-xr-x 2 root root   4096 Mar  7 10:05 ./
drwxr-xr-x 3 root root   4096 Mar  7 09:38 ../
-rw-r--r-- 1 root root 105371 Mar  7 10:05 ipfirewall.jpg
-rw-r--r-- 1 root root  73499 Mar  7 10:05 ipfirewall.png
-rwxr-xr-x 1 root root      4 Mar  7 09:58 ipfirewall.txt*
-rwxr-xr-x 1 root root   1342 Mar  7 10:05 plot.py*
-rwxr-xr-x 1 root root    681 Mar  7 09:58 readhost.py*
-rwxr-xr-x 1 root root    108 Mar  7 09:38 runprog.sh*
-rwxr-xr-x 1 root root    104 Mar  7 09:38 runplot.sh*
root@radius:/mahmud/python/mikrotik/ipfirewall#

8. Buat Script cron daemon berjalan setiap 5 menit sekali.

root@radius:/mahmud/python/mikrotik/ipfirewall# more runprog.sh
#!/bin/sh
while [ true ]
do
    python /mahmud/python/mikrotik/ipfirewall/readhost.py
    sleep 60
done

root@radius:/mahmud/python/mikrotik/ipfirewall# more runplot.sh
#!/bin/sh
while [ true ]
do
    python /mahmud/python/mikrotik/ipfirewall/plot.py
    sleep 60
done

9. Buat Start-up script agar jalan pada saat server boot
vi /etc/rc.local

#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.

/mahmud/python/mikrotik/ipfirewall/runprog.sh &
/mahmud/python/mikrotik/ipfirewall/runplot.sh &

exit 0

Done
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: