The script is written in Python 2.7.3 and collects data (CPU Utilization, Used Processor Memory, Used IO Memory, Number of UP Ethernet Interfaces) from all the Cisco devices in a network and exports this info to a MySQL database.
It also extracts useful info about each device (in the NetworkDevices table) or Top 3 resource-intensive devices in every other table.
#######PREREQUISITES#######
#1. Permissions on the script: "root# chmod 755 script.py".
#2. SSHv2 enabled and RSA 1024-bit key generated on every device.
#3. Python 2.x
#4. MySQL Server
#5. IP addresses of all devices must be inserted into NETWORK_IP.txt one on each line.
#6. Cisco SSH username and password must be inserted into SSH_USERPASS.txt on a single line, separated by comma.
#7. MySQL server address, username, password, database name must be inserted into SQL_CONN.txt on a single line, separated by comma.
#8. The MySQL database should be created prior to running the script for the first time.
#######MySQL SETUP#######
root#apt-get install mysql-server
root#service mysql start
root# mysql -u root -p
Enter password: (blank password)
mysql> create database NetMon;
mysql> create user 'test_user'@'localhost' identified by 'test_pass';
mysql> use NetMon;
mysql> grant all on NetMon.* to 'test_user'@'localhost';
mysql> create table NetworkDevices (Hostname VARCHAR(30), MACAddr VARCHAR(20) PRIMARY KEY, Vendor VARCHAR(10), Model VARCHAR(10), Image VARCHAR(50), IOSVersion VARCHAR(20), SerialNo VARCHAR(30), Uptime INT, CPUModel VARCHAR(20), CPUSpeed VARCHAR(10), SerialIntfNo INT, CiscoNeighbors VARCHAR(200), IntRoutingPro VARCHAR(30), ExtRoutingPro VARCHAR(30));
mysql> create table CPUUtilization (NetworkCPUUtilizationPercent FLOAT(6,2), Top3CPUDevices VARCHAR(50), PollTimestamp VARCHAR(25));
mysql> create table ProcMemUtilization (NetworkProcMemUtilizationPercent FLOAT(6,2), Top3ProcMemDevices VARCHAR(50), PollTimestamp VARCHAR(25));
mysql> create table IOMemUtilization (NetworkIOMemUtilizationPercent FLOAT(6,2), Top3IOMemDevices VARCHAR(50), PollTimestamp VARCHAR(25));
mysql> create table UPEthInterfaces (NetworkUPEthIntfPercent FLOAT(6,2), Top3UPEthIntf VARCHAR(50), PollTimestamp VARCHAR(25));
#######MySQL ALLOW REMOTE ACCESS#######
Allowing remote connection to MySQL server (running on Debian-based distribution):
1. # vi /etc/my.cnf
2. Once file opened, locate line that read as follows: [mysqld]
3. Make sure line skip-networking is commented (or remove line) and add following line: bind-address=YOUR-SERVER-IP
4. # /etc/init.d/mysql restart
5. $ mysql -u root -p mysql
mysql> GRANT ALL ON NetMon.* TO 'test_user'@'192.168.1.1' IDENTIFIED BY 'test_pass';
mysql> exit
6. For Linux firewall:
# /sbin/iptables -A INPUT -i eth1 -s 192.168.2.0/24 -p tcp --destination-port 3306 -j ACCEPT
# service iptables save
7. Testing, from the remote system: $ mysql -u test_user h 192.168.1.1 p
#######SCHEDULING A TASK TO EXECUTE THE SCRIPT REGULARLY#######
#Procedure for configuring Linux scheduler:
root# crontab -l =view scheduled tasks
root# crontab -e =edit scheduler
# Add the following line to run the script every 5 minutes, every hour, every day, every month:
# */5 * * * * /path_to_file/NetMon_SQL_v1.0.py /path_to_file/NETWORK_IP.txt /path_to_file/SSH_USERPASS.txt /path_to_file/SQL_CONN.txt
# For more info about configuring scheduler: http://kvz.io/blog/2007/07/29/schedule-tasks-on-linux-using-crontab/
# Running the script is recommended at intervals of at least 5 minutes.
#######OTHER#######
#Before installing the script as a scheduled task, it should be run manually in the console for error checking. The files are passed as arguments in the following order:
root# python NetMon_SQL_v1.0.py NETWORK_IP.txt SSH_USERPASS.txt SQL_CONN.txt
#Each poll operation is timestamped.
#All SQL-generated errors are logged to the SQL_Error_Log.txt file. This file should NOT be renamed or moved from the folder containing the script.
#Using the MySQL for Excel plug-in, all tables in a database can be imported into Excel (2007 or above). A connection from the Windows PC to the MySQL server is necessary. Tested on Windows 7 and 8, Excel 2010.
http://www.mysql.com/why-mysql/windows/excel/
#Tested on the following Cisco platforms (see the Model column):
mysql> select * from NetworkDevices;
+----------+----------------+--------+---------+-------------------------+-------------+--------------------------+--------+----------+----------+--------------+----------------+---------------+---------------+
| Hostname | MACAddr | Vendor | Model | Image | IOSVersion | SerialNo | Uptime | CPUModel | CPUSpeed | SerialIntfNo | CiscoNeighbors | IntRoutingPro | ExtRoutingPro |
+----------+----------------+--------+---------+-------------------------+-------------+--------------------------+--------+----------+----------+--------------+----------------+---------------+---------------+
| R1 | c003.25b4.0000 | Cisco | 2691 | C2691-ADVENTERPRISEK9-M | 12.4(25c) | XXXXXXXXXXX | 60 | R7000 | 160 | 2 | | eigrp 100 | bgp 65000 |
| R100 | c208.2e78.0000 | Cisco | 3725 | C3725-ADVENTERPRISEK9-M | 12.4(12) | FTX0945W0MY | 60 | R7000 | 240 | 0 | | eigrp 100 | bgp 65000 |
| R666 | c806.27d0.0000 | Cisco | 2621 | C2600-ADVENTERPRISEK9-M | 12.4(15)T9 | FTX0945W0MY | 120 | MPC860 | unknown | 0 | | eigrp 100 | bgp 65000 |
| R10000 | ca0a.2cd0.0008 | Cisco | 7206VXR | C7200-JK9S-M | 12.4(13b) | 4279256517 | 60 | NPE400 | 150 | 0 | | eigrp 100 | bgp 65000 |
| R111 | cc06.2580.0000 | Cisco | 3660 | C3660-JK9O3S-M | 12.4(12) | FTX0945W0MY | 600 | R527x | 250 | 0 | | eigrp 100 | bgp 65000 |
| R222 | d00e.2e98.0001 | Cisco | 1710 | C1700-ADVENTERPRISEK9-M | 12.4(15)T10 | FTX0945W0MY (4279256517) | 120 | MPC860T | unknown | 0 | | eigrp 100 | bgp 65000 |
+----------+----------------+--------+---------+-------------------------+-------------+--------------------------+--------+----------+----------+--------------+----------------+---------------+---------------+
6 rows in set (0.00 sec)
