Script to convert logs columns to rows

fono · 05-04-2009, 08:57 PM

Hi all, i have a bunch of log files that needs to be imported into a database for analysis but the logs comes in the following format:

****************************CDR 1****************************
CDR_TYPE: CallRec
callingNumber: 7590405
calledNumber: 7500820
answerTime: 00:03:39
releaseTime:00:07:34
callDuration: 235
*************************************************************

****************************CDR 2****************************
CDR_TYPE: MOSMSRecord
serviceCentre: (91)685750004
recordingEntity: (91)685750007
location: (LAC: 00 0A CELLID: 75 49)
messageReference: 31
originationTime: 2007-09-01 00:07:34 - 0B00
destinationNumber: (91)6857513394
*************************************************************

****************************CDR 3****************************
CDR_TYPE: CallRec
callingNumber: 7583021
calledNumber: 7552525
answerTime: 00:04:39
releaseTime: 00:07:37
callDuration: 178
*************************************************************

I need a script to output data in rows insted of columns filtering only data of CDR_TYPE: CallRec

The output should be :

CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration
CallRec 7590405 7500820 00:03:39 00:07:34 235
CallRec 7583021 7552525 00:04:39 00:07:37 178
......

Thanks in advance
fono

BrianK · 05-04-2009, 09:56 PM

Argh... misread the problem. This will not solve it.
If you know python, you can probably use what's below as a guide, but will need to add more logic. If you don't know python, this is likely no help. At the end of the day, you just need to see if the call is of type "CallRec" & print out every line after the first ':' until you see a bunch of '*', then start over. I may try to come back & fix this script up, but for now, I'm going to go eat dinner.

Again, what's below is my initial response & does not address your problem correctly

Not too terribly difficult in python:

Code:

#!/usr/bin/python2.5

import sys
import string

class LQ_Parser(object):
    def __init__(self,some_file):
        """
        Parses a file, keying on the stuff before the ':'
        """

        # read the file, storing each line in an array:
        contents = open(some_file,'r').readlines()

        # set up some containers:
        self.data_dict = {}   # this dictionary will allow one key for multiple vals
        self.ret_string = ""  # this string will hold the return value

        for line in map(string.strip,contents):
            parts = line.split(':')
            if len(parts) < 2:
                continue
            key = parts[0]
            val = string.join(parts[1:],':').strip()
            if not self.data_dict.has_key(key):
                self.data_dict[key] = [val]
            else:
                self.data_dict[key].append(val)

        for dk in sorted(self.data_dict.keys()):
            self.ret_string += "%s %s\n" % (dk,string.join(self.data_dict[dk],' '))

        print self.ret_string


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print >>sys.stderr, "\nMust give a file name.\n"
        sys.exit(1)
    LQ_Parser(sys.argv[1])

runs like so:

Code:

akane:/tmp/lq>./parser.py data.txt
CDR_TYPE CallRec MOSMSRecord CallRec
answerTime 00:03:39 00:04:39
callDuration 235 178
calledNumber 7500820 7552525
callingNumber 7590405 7583021
destinationNumber (91)6857513394
location (LAC: 00 0A CELLID: 75 49)
messageReference 31
originationTime 2007-09-01 00:07:34 - 0B00
recordingEntity (91)685750007
releaseTime 00:07:34 00:07:37
serviceCentre (91)685750004

edited to add: I made the variables "self." so that they could be used in other functions...
I made the whole thing a class so that it could be easily used as a module later, in another application.
The thoery is you could make some other accessor function like "show_results" that returns the self.ret_string, then some other python app could do something like:

Code:

from parser import LQ_Parser
db_stuff = LQ_Parser(data_file).show_results()
for line in db_stuff.split('\n'):
   insert_into_db(line)

ghostdog74 · 05-04-2009, 11:12 PM

if you have Python and can use it

Code:

data=open("file").read().split("\n\n")
print "CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration"
for items in data:
    if "callingNumber" in items:
        s=""
        items=items.split("\n")
        for i in items[1:-1]:
            s=s+" "+i.split(": ")[-1]
        print s

output:

Code:

# ./test.py
CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration
 CallRec 7590405 7500820 00:03:39 00:07:34 235
 CallRec 7583021 7552525 00:04:39 00:07:37 178

fono · 05-05-2009, 01:08 AM

thanks guys,
dont really know python, but ill try...coz i came from a C++ /Visual Basic background..

thanks for help anyway

fono · 05-18-2009, 05:44 PM

sorry, wrong question

fono · 05-19-2009, 03:11 PM

Thanks ghostdog74

but one more question please, suppose i want to read in and print out only 3 fields, callingNumber, calledNumber and callDuration, where do i made decisions within the code you posted???

data=open("file").read().split("\n\n")
print "CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration"
for items in data:
if "callingNumber" in items:
s=""
items=items.split("\n")
for i in items[1:-1]:
s=s+" "+i.split(": ")[-1]
print s

osor · 05-19-2009, 04:02 PM

Quote:

Originally Posted by fono

data=open("file").read().split("\n\n")
print "CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration"
for items in data:
if "callingNumber" in items:
s=""
items=items.split("\n")
for i in items[1:-1]:
s=s+" "+i.split(": ")[-1]
print s

The code you highlighted is only the heading, and doesn’t actually do the “hard part” of your program. The relevant part is in the line:

Code:

        for i in items[1:-1]:

You can change this to be any subsequence of the list items that you wish, either by index or by matching to your criteria (or you might even remove the offending elements before getting to this line).

P.S.
When quoting code (especially with spacing sensitive languages such as python, use “[code] Code goes here! [/code]” to preserve indentation.

P.P.S.
Just for fun, here is the original request in a perl one-liner:

Code:

perl -00naF'\n' -e 'print"@{[map{s/^.*: //;$_}grep/:/,@F]}\n"if/CallRec/' file

Or, for the selective request:

Code:

perl -00naF'\n' -e 'print"@{[map{s/^.*: //;$_}grep/callingNumber|calledNumber|callDuration/,@F]}\n"if/CallRec/' file

fono · 05-19-2009, 05:04 PM

but in response to ghostdog74,
the output im diggin should be:

# ./test.py
callingNumber calledNumber callDuration
7590405 7500820 235
7583021 7552525 178

stripping off other fields
is this possible using: import re

then use re.match() to filter off only needed fields?

Quote:

Originally Posted by osor

The code you highlighted is only the heading, and doesn’t actually do the “hard part” of your program. The relevant part is in the line:

Code:

        for i in items[1:-1]:

You can change this to be any subsequence of the list items that you wish, either by index or by matching to your criteria (or you might even remove the offending elements before getting to this line).

P.S.
When quoting code (especially with spacing sensitive languages such as python, use “[code] Code goes here! [/code]” to preserve indentation.

P.P.S.
Just for fun, here is the original request in a perl one-liner:

Code:

perl -00naF'\n' -e 'print"@{[map{s/^.*: //;$_}grep/:/,@F]}\n"if/CallRec/' file

Or, for the selective request:

Code:

perl -00naF'\n' -e 'print"@{[map{s/^.*: //;$_}grep/callingNumber|calledNumber|callDuration/,@F]}\n"if/CallRec/' file

ghostdog74 · 05-19-2009, 07:06 PM

Quote:

Originally Posted by fono

Thanks ghostdog74

but one more question please, suppose i want to read in and print out only 3 fields, callingNumber, calledNumber and callDuration, where do i made decisions within the code you posted???

data=open("file").read().split("\n\n")
print "CDR_TYPE callingNumber calledNumber answerTime releaseTime callDuration"
for items in data:
if "callingNumber" in items:
s=""
items=items.split("\n")
for i in items[1:-1]:
s=s+" "+i.split(": ")[-1]
print s

put Python codes in code tags as suggested by osor. Python uses indentation, so if i can't see indentation at relevant parts of your code, its hard to see what went wrong...

Code:

#!/usr/bin/env python
data=open("file").read().split("\n\n")
print "callingNumber calledNumber callDuration"
for items in data:
    if "callingNumber" in items:
        s=""
        items=items.split("\n")
        items=items[2:4]+items[-2:-1] 
        for i in items:
            s=s+" "+i.split(": ")[-1]
        print s
sys.exit()

output

Code:

# ./test.py
callingNumber calledNumber callDuration
 7590405 7500820 235
 7583021 7552525 178

Python lists go be indexing..please read Python docs to get understanding of how it works. (see my sig)

fono · 05-19-2009, 08:19 PM

thank you so much ghostdog74,

exactly what i needed.. thanks again just for my sake, could you please brief on line:

items=items[2:4]+items[-2:-1]

cheers

Quote:

Originally Posted by ghostdog74

put Python codes in code tags as suggested by osor. Python uses indentation, so if i can't see indentation at relevant parts of your code, its hard to see what went wrong...

Code:

#!/usr/bin/env python
data=open("file").read().split("\n\n")
print "callingNumber calledNumber callDuration"
for items in data:
    if "callingNumber" in items:
        s=""
        items=items.split("\n")
        items=items[2:4]+items[-2:-1] 
        for i in items:
            s=s+" "+i.split(": ")[-1]
        print s
sys.exit()

output

Code:

# ./test.py
callingNumber calledNumber callDuration
 7590405 7500820 235
 7583021 7552525 178

Python lists go be indexing..please read Python docs to get understanding of how it works. (see my sig)

ghostdog74 · 05-19-2009, 08:29 PM

Quote:

Originally Posted by fono

thank you so much ghostdog74,

exactly what i needed.. thanks again just for my sake, could you please brief on line:

items=items[2:4]+items[-2:-1]

cheers

please play with the Python interpreter by typing "python" on the command prompt

Code:

# python
ActivePython 2.6.2.2 (ActiveState Software Inc.) based on
Python 2.6.2 (r262:71600, Apr 21 2009, 14:36:21)
[GCC 3.3.1 (SuSE Linux)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> alist = [1,2,3,4,5,6,7,8,9,10]
>>> alist[2:4]
[3, 4]
>>> alist[-2:-1]
[9]
>>> alist[2:4] + alist[-2:-1]
[3, 4, 9]

do i need to explain more? please go to the Python site , read its tutorial (see my sig).