Parsing log in SQL DB to change IPs to hostnames

[KDawg44]

On Apr 10, 11:11 am, "Kushal Kumaran" <kushal.kuma... at gmail.com>
wrote:
> On Apr 10, 8:37 pm, "KDawg44" <KDaw... at gmail.com> wrote:
>
>
>
> > Hi,
>
> > I am brand new to Python.  In learning anything, I find it useful to
> > actually try to write a useful program to try to tackle an actual
> > problem.
>
> > I have a syslog server and I would like to parse the syslog messages
> > and try to change any ips to resolved hostnames.  Unfortunately, I am
> > not getting any matches on my regular expression.
>
> > A message will look something like this:
> >  Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: 1.1.1.1 Accessed URL
> > 10.10.10.10:/folder/folder/page.html
>
> > I would like to change the message to have the hostnames, or even
> > better actually, have it appear as hostname-ip address.  So a changed
> > message would look like:
>
> >  Apr 10 2007 00:30:58 DEVICE : %DEVICEINFO: pcname-1.1.1.1 Accessed
> > URLwww.asite.com-10.10.10.10:/folder/folder/page.html
>
> > or some equivalent.
>
> > Here is what i have so far.  Please be kind as it is my first python
> > program.... :)
>
> > #! /usr/bin/python
>
> > import socket
> > import re
> > import string
> > import MySQLdb
>
> > ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
> > ipRegEx = re.compile(ipRegExC)
>
> > try:
> >         conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
> > passwd="REMOVED", db="REMOVED")
>
> > except MySQLdb.Error, e:
> >         print "Error connecting to the database: %d - %s " %
> > (e.args[0], e.args[1])
> >         sys.exit(1)
>
> > cursor = conn.cursor()
> > cursor.execute("SELECT msg, seq FROM REMOVED WHERE seq = 507702")
> > # one specific message so that it doesn't parse the whole DB during
> > testing...
> > while(1):
> >         row = cursor.fetchone()
> >         if row == None:
> >                 break
> >         if ipRegEx.match(row[0]):
> >             ....
> > <snipped rest of the code>
>
> See the documentation of the re module for the difference between
> matching and searching.
>
> --
> Kushal

Thank you very much.  I think I have it figured out, except for an
error on the SQL statement:


[----- BEGIN ERROR ---]
Traceback (most recent call last):
  File "changeLogs.py", line 47, in ?
    cursor.execute("""UPDATE logs SET msg = %s WHERE seq = %i""",
(newMsg,seqNum))
  File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
    query = query % db.literal(args)
TypeError: int argument required
[----- END ERROR ---]

Here is my code

[----- BEGIN CODE ---]
#! /usr/bin/python

import socket
import sys
import re
import string
import MySQLdb

def resolveHost(ipAdds):
        ipDict = {}
        for ips in ipAdds:
                try:
                        ipDict[ips] = socket.gethostbyaddr(ips)[0]
                except:
                        ipDict[ips] = "Cannot resolve"
        return ipDict


ipRegExC = r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
ipRegEx = re.compile(ipRegExC)

try:
        conn = MySQLdb.connect(host="REMOVED", user="REMOVED",
passwd="REMOVED", db="REMOVED")

except MySQLdb.Error, e:
        print "Error connecting to the database: %d - %s " %
(e.args[0], e.args[1])
        sys.exit(1)

cursor = conn.cursor()
cursor.execute("SELECT msg, seq FROM `logs` WHERE seq = 507702")
while(1):
        row = cursor.fetchone()
        ipAddresses = []
        resolvedDict = {}
        if row == None:
                break
        if ipRegEx.search(row[0]):
                seqNum = row[1]
                ipAddresses = ipRegEx.findall(row[0])
                resolvedDict = resolveHost(ipAddresses)
                newMsg = row[0]
                for ip in resolvedDict.keys():
                        newMsg = newMsg.replace(ip,ip + "-" +
resolvedDict[ip])
                cursor.execute("""UPDATE REMOVED SET msg = %s WHERE
seq = %i""", (newMsg,seqNum))


[----- END CODE ---]

Thanks again!