Parsing log in SQL DB to change IPs to hostnames

[KDawg44]

On Apr 10, 1:54 pm, "KDawg44" <KDaw... at gmail.com> wrote:
> 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!

Also, i tried changing seqNum = row[1] to seqNum = int(row[1]) to cast
it as an integer and I get the same error (because I think that
pulling from a DB makes everything a string by default?)

Thanks.