Wednesday, October 15, 2014

Parse switch logs to MySQL database

This is my first program utilizing Python and MySQL together. It is not perfect but it works. 

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