Note: Inserting an element into MySQL one-by-one would take days (the input is several hundred-thousand lines). I used executemany() to stick a whole array into one insertion. Cut my time from 10 hours to 4 minutes!
# parse_syslog.py
# This is a script to parse switch syslogs nightly into a MySQL Database
# Input is a .gz file of Cisco switch logs
import MySQLdb
import time
import gzip
# mySQL Config
mysqlhost = 'localhost'
mysqlusername = 'user'
mysqlpassword = 'notrealpassword'
mysqldatabase = 'switch_logs'
mysqldb = MySQLdb.connect(mysqlhost, mysqlusername, mysqlpassword, mysqldatabase)
# ask user for name of .gz file
filename = raw_input('Enter a file name: ')
# open .gz file for input
input_file = gzip.open(filename, 'rb')
try:
# initializations
msgValid = False
interface1 = None
interface2 = None
vlan = None
cnt = 0
err = '%'
import_list = []
# get line
line = input_file.readline()
# get date
ldate = time.strftime("%Y-%m-%d")
# go through every line in the file
while line:
# parse the line
args = line.split()
# get next line
line = input_file.readline()
# extract information from parsed line
# get error message
msg = [s for s in args if "%" in s]
if len(msg) > 0:
if msg[0][0] == '%':
msg = msg[0]
msgValid = True
# if found error msg, get time, ip, interface, vlan
if msgValid == True:
# increment count
cnt=cnt+1
# get time
time = args[2]
# get ip address
ip = args[3]
# get vlan
try:
vlanIndex = args.index('vlan')+1
vlan = args[vlanIndex].split('.',1)[0]
if vlan.isdigit() == False:
vlan = None
except ValueError:
pass
# get interfaces
# create list of interfaces found in args
interfaces = [s for s in args if "Gi" in s]
# if found Gi interfaces
if len(interfaces) > 0:
interface1 = interfaces[0].split(',',1)[0]
if len(interfaces) == 2:
interface2 = interfaces[1]
# if not found Gi, find Fa interfaces
else:
interfaces = [s for s in args if "Fa" in s]
if len(interfaces) > 0:
interface1 = interfaces[0]
if len(interfaces) == 2:
interface2 = interfaces[1]
# insert into list
arr = [ip, time, ldate, msg, interface1, interface2, vlan]
import_list.append(arr)
# reinitialize variables
interface1 = None
interface2 = None
msgValid = False
vlan = None
msg = None
finally:
# batch insert into MySQL Database
stmt = "INSERT INTO test VALUES(%s, %s, %s, %s, %s, %s, %s)"
with mysqldb:
mysqlcur = mysqldb.cursor()
mysqlcur.executemany(stmt, import_list)
input_file.close()
No comments:
Post a Comment