Mrogram Part-1 Python-Mikrotik DHCP Server lease Count

Beberapa hari ini ingin menyalurkan hasrat mrogram,  penasaran saja bagaimana python bisa bekerja sama dengan mikrotik untuk menghasilkan data statistik load network,  part 1 ini saya membahas bagaimana python membaca nilai DHCP lease count lalu menyimpan ke database dan memunculkan data statistik dalam bentuk grafik, hasilnya seperti dibawah

Langsung sajalah liat note saya selama mrogram, kalau ada yang mau coba dan tidak jelas silahkan tinggalkan komen dibawah

HOW TO Mikrotik Python [ip dhcp-server lease print count-only]

1. Buat File di Mikrotik:  
/file print file=dhcp-server

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

Buat Scheduler

;;; DHCP-SERVER COUNT
name="dhcp-server" start-date=jan/02/1970 start-time=05:00:00 
interval=1m4s on-event=dhcp-server 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           |
+--------------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE `user_dhcpserver` (   `waktu` datetime DEFAULT NULL,   `dhcpserver` int(40) DEFAULT NULL);                    Query OK, 0 rows affected (0.13 sec)

4. Isi Direktori

root@radius:/mahmud/python/mikrotik/dhcp-server# 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 dhcp-server.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 runhost.sh*
-rwxr-xr-x 1 root root  104 Mar  7 09:38 runplot.sh*
root@radius:/mahmud/python/mikrotik/dhcp-server#

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 = 'dhcp-server.txt'
ftp = ftplib.FTP("192.168.1.253")
ftp.login("mahmudsaja", "SukaSukaMahmud")
ftp.cwd(path)
ftp.retrbinary("RETR " + filename ,open(filename, 'wb').write)
ftp.quit()

f = open('dhcp-server.txt').readlines()[0]
bacadhcp= int(f)

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

6. Buat Script Python Plot Graph

##By-Gigih-F, iseng-iseng aja : )
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_dhcpserver.waktu, user_dhcpserver.dhcpserver  FROM user_dhcpserver 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 DHCP-Server Lease')

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

7. Liat isi direktori apakah plotting sudah berhasil
root@radius:/mahmud/python/mikrotik/dhcp-server# 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 dhcpserver.jpg
-rw-r--r-- 1 root root  73499 Mar  7 10:05 dhcpserver.png
-rwxr-xr-x 1 root root      4 Mar  7 09:58 dhcp-server.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 runhost.sh*
-rwxr-xr-x 1 root root    104 Mar  7 09:38 runplot.sh*
root@radius:/mahmud/python/mikrotik/dhcp-server#

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

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

root@radius:/mahmud/python/mikrotik/dhcp-server# more runplot.sh
#!/bin/sh
while [ true ]
do
    python /mahmud/python/mikrotik/dhcp-server/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/dhcp-server/rundhcp.sh &
/mahmud/python/mikrotik/dhcp-server/runplot.sh &

exit 0

Done