Home > World Of ICT > Mrogram Part-2 Python Mikrotik DNS Cache

Mrogram Part-2 Python Mikrotik DNS Cache


Part 2  saya membahas bagaimana python membaca nilai DNS  Cache count lalu menyimpan ke database dan memunculkan data statistik dalam bentuk grafik, hasilnya seperti dibawah

dnscache

How To Python and MIkrotik /ip dns cache

1. BUat File di Mikrotik:  
/file print file=dnscache

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

Buat Scheduler

;;; dnscache COUNT
name="dnscache" start-date=jan/02/1970 start-time=05:00:00 
interval=1m4s on-event=dnscache 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_dnscache` ( `waktu` datetime DEFAULT NULL,  `dnscache` int(40) DEFAULT NULL);                    Query OK, 0 rows 

affected (0.13 sec)

4. Isi Direktori

root@radius:/mahmud/python/mikrotik/dnscache# 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 dnscache.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/dnscache#

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 = 'dnscache.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]
bacad= int(f)

conn = mdb.connect("localhost", "root", "OkeOkeSaja", "mikrotik");
with conn:
  cursor = conn.cursor ()
  cursor.execute ("SELECT * FROM user_dnscache")
  cursor.execute ("""INSERT INTO user_dnscache(waktu,dnscache) VALUES(%s,"%s")""" , (now,bacad))
  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_dnscache.waktu, user_dnscache.dnscache  FROM user_dnscache 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 dnscache di jaringan UnilaNET')  #Label pada sumbu Y
plt.xlabel('Waktu')  #Label pada sumbu X
plt.grid()
plt.title('Statistik dnscache  UnilaNET  per tanggal : [%s] (Credit to RaspberryPi+Python+MySQL)'%(now))  #Judul Grafik
plt.savefig('/mahmud/python/mikrotik/dnscache/dnscache.png', dpi=100) #Simpan file dengan judul xxx.png
Image.open('/mahmud/python/mikrotik/dnscache/dnscache.png').save('/mahmud/python/mikrotik/dnscache/dnscache.jpg','JPEG')
#plt.show() #Tampilkan hasil script

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

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

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

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