Home > World Of ICT > Mrogram Part-1 Python-Mikrotik DHCP Server lease Count

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

dhcpserver

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

  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.