Teneo !!!

Aaron’s blog on Networking, and Enterprise Technology

Parsing Cisco CDR into PostgreSQL DB

Over the last 2 days, I’ve been wanting to develop a program which will report Call Metrics from my Cisco Call Manager systems. The first thing I needed to do was create a quick script that will parse the CDR’s from Cisco, and place them into a Database. More specifically, a PostgreSQL database. Personally, I’m a MySQL guy, but that’s another posting……. Technically, if you are Python-aware, it shouldn’t be very hard to change this to use MySQL, SQL, etc.

I first started out using Java to parse the data, but I needed to develop a prototype faster, so I chose Python. Basically, it goes through each CDR in a folder, parses it, places it in the database, then archives it for historical reasons.


First, you’ll need to create the table, so take the createCDRTable.sql file, and create your table.

[apaxson@host]# psql -U database < createCDRTable.sql
CREATE TABLE
[apaxson@host]#

Once that’s done, run the python script, taking care to change the variable names. If everything works well, schedule the python script in a cron job, and you should be good to go.

—- createCDRTable.sql —-

CREATE TABLE calldetails (
cdrRecordType				INTEGER,
globalCallID_callManagerId		INTEGER,
globalCallID_callId			INTEGER,
origLegCallIdentifier			INTEGER,
dateTimeOrigination			INTEGER,
origNodeId				INTEGER,
origSpan				INTEGER,
origIpAddr				INTEGER,
callingPartyNumber			VARCHAR(50),
callingPartyUnicodeLoginUserID		VARCHAR(50),
origCause_location			INTEGER,
origCause_value				INTEGER,
origPrecedenceLevel			INTEGER,
origMediaTransportAddress_IP		INTEGER,
origMediaTransportAddress_Port		INTEGER,
origMediaCap_payloadCapability		INTEGER,
origMediaCap_maxFramesPerPacket		INTEGER,
origMediaCap_g723BitRate		INTEGER,
origVideoCap_Codec			INTEGER,
origVideoCap_Bandwidth			INTEGER,
origVideoCap_Resolution			INTEGER,
origVideoTransportAddress_IP		INTEGER,
origVideoTransportAddress_Port		INTEGER,
origRSVPAudioStat			VARCHAR(64),
origRSVPVideoStat			VARCHAR(64),
destLegIdentifier			INTEGER,
destNodeId				INTEGER,
destSpan				INTEGER,
destIpAddr				INTEGER,
originalCalledPartyNumber		VARCHAR(50),
finalCalledPartyNumber			VARCHAR(50),
finalCalledPartyUnicodeLoginUserID	VARCHAR(50),
destCause_location			INTEGER,
destCause_value				INTEGER,
destPrecedenceLevel			INTEGER,
destMediaTransportAddress_IP		INTEGER,
destMediaTransportAddress_Port		INTEGER,
destMediaCap_payloadCapability		INTEGER,
destMediaCap_maxFramesPerPacket		INTEGER,
destMediaCap_g723BitRate		INTEGER,
destVideoCap_Codec			INTEGER,
destVideoCap_Bandwidth			INTEGER,
destVideoCap_Resolution			INTEGER,
destVideoTransportAddress_IP		INTEGER,
destVideoTransportAddress_Port		INTEGER,
destRSVPAudioStat			VARCHAR(64),
destRSVPVideoStat			VARCHAR(64),
dateTimeConnect				INTEGER,
dateTimeDisconnect			INTEGER,
lastRedirectDn				VARCHAR(50),
pkid					VARCHAR(50),
originalCalledPartyNumberPartition	VARCHAR(50),
callingPartyNumberPartition		VARCHAR(50),
finalCalledPartyNumberPartition		VARCHAR(50),
lastRedirectDnPartition			VARCHAR(50),
duration				INTEGER,
origDeviceName				VARCHAR(129),
destDeviceName				VARCHAR(129),
origCallTerminationOnBehalfOf		INTEGER,
destCallTerminationOnBehalfOf		INTEGER,
origCalledPartyRedirectOnBehalfOf	INTEGER,
lastRedirectRedirectOnBehalfOf		INTEGER,
origCalledPartyRedirectReason		INTEGER,
lastRedirectRedirectReason		INTEGER,
destConversationId			INTEGER,
globalCallId_ClusterID			VARCHAR(50),
joinOnBehalfOf				INTEGER,
comment					TEXT,
authCodeDescription			VARCHAR(50),
authorizationLevel			INTEGER,
clientMatterCode			VARCHAR(32),
origDTMFMethod				INTEGER,
destDTMFMethod				INTEGER,
callSecuredStatus			INTEGER
);

—- createCDR-records.py —-
My apologies for the lack of comments. I was in a hurry.

#!/usr/bin/env python

import _pg, sys, os
import string
import traceback
import logging
import zipfile

# Variables

#LOGGING=CRITICAL,ERROR,WARNING,INFO,DEBUG
loggingLevel=logging.INFO

# Log file path
logFilePath='/var/log/calldata-import.log'

# Path to CDR files
cdr_path='/home/apaxson/Documents/Cisco-CDR'

# Database name to export data into
database='CallData'

# Database server
dbhost='localhost'

# User to access the database
dbuser='username'

# Database table to insert date into
table='calldetails'

# Zip file to archive data to
fileArchive=cdr_path+'/archive.zip'

######## Functions ############
def createCdrSQL():
# TODO:  Refactor this so it's not CDR specific.  We'll need
# to also import CMR data as well
    totalRecordCount=0
    logging.debug("Creating CDR SQL")
    cdrListing = listCallFiles(cdr_path,"cdr")
    startSQL="INSERT INTO " + table + " ("
    fileCount=0

    logging.info("Parsing %d files" % len(cdrListing))
    for file in cdrListing:
        columns=""
        fileRecordCount=0
        try:
            logging.debug("Opening file %d of %d: %s" % (fileCount,len(cdrListing),file))
            cdrFile = open(cdr_path+"/"+file,'r')
            try:
                for line in cdrFile:

                    if line.startswith('"cdrRecordType"'):
                        logging.debug("Building columns...")
                        headers = line.split(',')
                        for header in headers:
                            newHeader=header.replace('"','')
                            columns+=newHeader+','
                        #There will be a trailing comma at the end.  Remove.
                        cleanedColumns=columns.rstrip(',')
                        cleanedColumns+=")"
                        logging.debug("Column data='" + cleanedColumns + "'")

                    elif not line.startswith('INTEGER'):
                        logging.debug("Building values...")
                        values=""
                        splitLineArray = line.split(',')
                        for value in splitLineArray:
                            if value == '""':
                                value = "null"
                            newValue=value.replace('"',"'")
                            values+=newValue+','

                        #Remove the trailing comma at the end
                        cleanedValues=values.rstrip(',')
                        cleanedValues+=");"
                        logging.debug("Value data='" + cleanedValues+"'")

                        logging.debug("Building full SQL statement")
                        fullSQL=startSQL+cleanedColumns+" VALUES (" + cleanedValues
                        logging.debug("Generated SQL is: " + fullSQL)

                        logging.debug("Inserting to DB")
                        try:
                            conn.query(fullSQL)
                            fileRecordCount+=1
                        except:
                            logging.critical("Unable to insert the following SQL: " + fullSQL)
                            logging.error(traceback.print_exc(file=sys.stdout))  

            except:
                logging.warning("Could not enumerate files")
                cdrFile.close()
                logging.error(traceback.print_exc(file=sys.stdout))
        except:
            logging.critical("Could not open File")
        fileCount+=1
        totalRecordCount+=fileRecordCount
        logging.info("Inserted %d records for file %d of %d: %s" % (fileRecordCount,fileCount,len(cdrListing),file))
        logging.debug("closing file: %s" % (file))
        cdrFile.close()
        logging.info("Adding file to zip archive")
        addFileToZip(file)
    conn.close()
    logging.info("Inserted %d records total" % (totalRecordCount))         

def listCallFiles(dir,type):
    fileListing=[]
    files = os.listdir(dir)
    for file in files:
        if file.startswith(type):
            fileListing.append(file)
    return fileListing

def addFileToZip(file):
    logging.debug("Adding file %s to archive %s" % (file,fileArchive))

    #If the zip file exists, add to it, otherwise, create it.
    if os.path.exists(fileArchive):
        zippedFile=zipfile.ZipFile(fileArchive,'a')
    else:
        zippedFile=zipfile.ZipFile(fileArchive,'w')
    try:
        zippedFile.write(cdr_path+'/'+file,file)
        logging.debug("File: %s was successfully added to archive" % (file))
        zippedFile.close()
        os.remove(cdr_path+'/'+file)
    except:
        logging.error("Could not write to zip archive")
        zippedFile.close()

######### Enable Logging ###############
logging.basicConfig(level=loggingLevel,
                    format='%(asctime)s %(levelname)-8s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S',
                    filename=logFilePath,
                    filemode='a')

logging.info("Starting call-data import..")

########## Try to connect to the Database #############
try:
    conn = _pg.connect(dbname='opennms',host='localhost',user='opennms')
except:
    # Ooops... couldn't connect
    logging.critical("Unable to connect to the database.  Terminating..")
    logging.error(traceback.print_exc(file=sys.stdout))
    sys.exit(2)

############ Insert SQL ####################################

createCdrSQL()

Technorati Tags: , ,

Powered by ScribeFire.

Advertisements

25 responses to “Parsing Cisco CDR into PostgreSQL DB

  1. Pushkar Bhatkoti October 21, 2008 at 6:35 pm

    Excellent, just tested it with CCM 6x CDR and works greately.

    cheers and thanks for sharing mate…

    -pushkar bhatkoti
    sydney

  2. Abdullah November 28, 2008 at 2:33 am

    Thanks Aaron, this article helped me a lot,

    Of course i’m not that much in pythone 🙂

    i’m doing it using VB.Net and MySQL

    as Pushkar said:

    cheers and thanks for sharing mate

  3. Nathan Gregory February 23, 2009 at 1:24 am

    Aaron,
    Thanks for the very useful code. You saved me a lot of time!
    It seems that there are a couple of additional fields that have been added to the CDR spec for CUCM 6.x which will cause the script to spit the dummy.

    The following fields need to be added to the end of the list when creating the table:


    origConversationId INTEGER,
    origMediaCap_Bandwidth INTEGER,
    destMediaCap_Bandwidth INTEGER,
    authorizationCodeValue VARCHAR(32)

    Also, for those that want to use mySQL, make the following changes to the python code:
    From this: import _pg, sys, os
    To this: import MySQLdb, sys, os

    and From this: conn = _pg.connect(dbname='opennms',host='localhost',user='opennms')

    To this: conn = MySQLdb.connect(host=dbhost,user=dbuser,passwd=dbpassword,db=database)

    The database creation script will import straight into mySQL without any modification.

    Also, don’t forget to remove the comment from the line:
    #os.remove(cdr_path+'/'+file)
    otherwise the same files will keep being reimported, duplicating your data.

    Thanks again for doing the hard work on this!

  4. Aaron Paxson February 24, 2009 at 6:40 am

    Fantastic, Nathan! Thanks so much for contributing your code and experiences! I’ll try to get your update worked in soon!

    Thanks for the update on the commented “os.remove(cdr_path+’/’+file)”!! I must have left that comment in there for debugging, and forgot to remove it for my post!

    –Aaron

  5. ramboza March 2, 2009 at 7:07 am

    Hello,

    When i run the script, everything seems to be OK, no errors, log file contains “Inserted cdr values etc…”, but the table in the mysql dababase (changed the code as Nathan adviced) is EMPTY.
    Even the CDR’s are being archived…

    Could you please provide any advice?

  6. Anthony March 24, 2009 at 2:57 pm

    Hi Aaron,

    Do you have any knowledge of how to easily identify the different call types? For instance, Voice Mail, Single Number Reach, CUPC, Dual Mode and CUMC calls?

    Thanks,
    Anthony

  7. Aaron Wasilewski May 26, 2009 at 4:24 pm

    Hello. We recentlly upgraded to UCM 7.X and, subsequentlly, went from SQL db to the now apparentlly native Informix db.

    Are you parsing (with your script) the data from the files exported from the UCM environment?

    Are you generating any reports from the “new” db instance?

    • Aaron Paxson May 26, 2009 at 5:12 pm

      Hi Aaron (good name by the way!).

      I am still using the 5.x environment. Subsequently, I don’t use a SQL DB export. These are flat files that contain call detail records that I parse and insert in my own database. The files are ftp’d regularly.

      What a small world. My wife was in the major city near you for part of her high school. You must be close to the area?

      • William Westman Malmi June 9, 2009 at 8:52 am

        This is the table structure if you are using Callmanager 7.x. I have inserted the code in a transaction in case you want to change values and need to re-upload it again and again. I have also called the table CDR, to distinguish between CDR and CMR inserts.

        I can attest to that the python script that Aaron has made works fine with Call Manager 7.x, even though you have to change it so it works with dynamic file names.

        SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

        SET AUTOCOMMIT=0;
        START TRANSACTION;

        --
        -- Database `CallData`
        --

        -- --------------------------------------------------------

        --
        -- Structure for table `cdr`
        --

        DROP TABLE IF EXISTS `cdr`;
        CREATE TABLE IF NOT EXISTS `cdr` (
        `cdrRecordType` int(11) default NULL,
        `globalCallID_callManagerId` int(11) default NULL,
        `globalCallID_callId` int(11) default NULL,
        `origLegCallIdentifier` int(11) default NULL,
        `dateTimeOrigination` int(11) default NULL,
        `origNodeId` int(11) default NULL,
        `origSpan` int(11) default NULL,
        `origIpAddr` int(11) default NULL,
        `callingPartyNumber` varchar(50) default NULL,
        `callingPartyUnicodeLoginUserID` varchar(50) default NULL,
        `origCause_location` int(11) default NULL,
        `origCause_value` int(11) default NULL,
        `origPrecedenceLevel` int(11) default NULL,
        `origMediaTransportAddress_IP` int(11) default NULL,
        `origMediaTransportAddress_Port` int(11) default NULL,
        `origMediaCap_payloadCapability` int(11) default NULL,
        `origMediaCap_maxFramesPerPacket` int(11) default NULL,
        `origMediaCap_g723BitRate` int(11) default NULL,
        `origVideoCap_Codec` int(11) default NULL,
        `origVideoCap_Bandwidth` int(11) default NULL,
        `origVideoCap_Resolution` int(11) default NULL,
        `origVideoTransportAddress_IP` int(11) default NULL,
        `origVideoTransportAddress_Port` int(11) default NULL,
        `origRSVPAudioStat` varchar(64) default NULL,
        `origRSVPVideoStat` varchar(64) default NULL,
        `destLegIdentifier` int(11) default NULL,
        `destNodeId` int(11) default NULL,
        `destSpan` int(11) default NULL,
        `destIpAddr` int(11) default NULL,
        `originalCalledPartyNumber` varchar(50) default NULL,
        `finalCalledPartyNumber` varchar(50) default NULL,
        `finalCalledPartyUnicodeLoginUserID` varchar(50) default NULL,
        `destCause_location` int(11) default NULL,
        `destCause_value` int(11) default NULL,
        `destPrecedenceLevel` int(11) default NULL,
        `destMediaTransportAddress_IP` int(11) default NULL,
        `destMediaTransportAddress_Port` int(11) default NULL,
        `destMediaCap_payloadCapability` int(11) default NULL,
        `destMediaCap_maxFramesPerPacket` int(11) default NULL,
        `destMediaCap_g723BitRate` int(11) default NULL,
        `destVideoCap_Codec` int(11) default NULL,
        `destVideoCap_Bandwidth` int(11) default NULL,
        `destVideoCap_Resolution` int(11) default NULL,
        `destVideoTransportAddress_IP` int(11) default NULL,
        `destVideoTransportAddress_Port` int(11) default NULL,
        `destRSVPAudioStat` int(11) default NULL,
        `destRSVPVideoStat` int(11) default NULL,
        `dateTimeConnect` int(11) default NULL,
        `dateTimeDisconnect` int(11) default NULL,
        `lastRedirectDn` varchar(50) default NULL,
        `pkid` text COMMENT 'UNIQUEIDENTIFIER',
        `originalCalledPartyNumberPartition` varchar(50) default NULL,
        `callingPartyNumberPartition` varchar(50) default NULL,
        `finalCalledPartyNumberPartition` varchar(50) default NULL,
        `lastRedirectDnPartition` varchar(50) default NULL,
        `duration` int(11) default NULL,
        `origDeviceName` varchar(129) default NULL,
        `destDeviceName` varchar(129) default NULL,
        `origCallTerminationOnBehalfOf` int(11) default NULL,
        `destCallTerminationOnBehalfOf` int(11) default NULL,
        `origCalledPartyRedirectOnBehalfOf` int(11) default NULL,
        `lastRedirectRedirectOnBehalfOf` int(11) default NULL,
        `origCalledPartyRedirectReason` int(11) default NULL,
        `lastRedirectRedirectReason` int(11) default NULL,
        `destConversationId` int(11) default NULL,
        `globalCallId_ClusterID` varchar(50) default NULL,
        `joinOnBehalfOf` int(11) default NULL,
        `comment` varchar(2048) default NULL,
        `authCodeDescription` varchar(50) default NULL,
        `authorizationLevel` int(11) default NULL,
        `clientMatterCode` varchar(32) default NULL,
        `origDTMFMethod` int(11) default NULL,
        `destDTMFMethod` int(11) default NULL,
        `callSecuredStatus` int(11) default NULL,
        `origConversationId` int(11) default NULL,
        `origMediaCap_Bandwidth` int(11) default NULL,
        `destMediaCap_Bandwidth` int(11) default NULL,
        `authorizationCodeValue` varchar(32) default NULL,
        `outpulsedCallingPartyNumber` varchar(50) default NULL,
        `outpulsedCalledPartyNumber` varchar(50) default NULL,
        `origIpv4v6Addr` varchar(64) default NULL,
        `destIpv4v6Addr` varchar(64) default NULL
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

        COMMIT;

  8. Aaron Wasilewski May 26, 2009 at 5:48 pm

    Any thoughts on the 7.x environment and the loss of the SQL db? There are still flat files which can/are exportbale and can be parsed. Our need is to create a web-based application which would allow users to query the db.
    As a matter of fact, I am in Yakima.

    Please reply aaron.wasilewski@co.yakima.wa.us.

  9. Jcampos August 18, 2009 at 5:57 pm

    Thank you!!
    I use your script with my MySQL database, works like a charm…

  10. Amro BaniHani September 13, 2009 at 1:08 pm

    Hello

    I don’t know anything about Python, if anyone have developed it using JAVA please share it.

    if not

    I’ve tried to run the code but there is something wrong with import _PG. so please help..

    Regards
    Amro

  11. john September 22, 2009 at 1:17 am

    Hi

    Thank you for mysql example, working flawlessly on CCM 5.1

    Regards

  12. Rob Newton March 30, 2010 at 11:54 am

    Found that the following fields were needed in addition to the fields appended by Nathan.


    ALTER TABLE `cisco_calldetail` ADD `origVideoCap_Codec_Channel2` INT NOT NULL ,
    ADD `origVideoCap_Bandwidth_Channel2` INT NOT NULL ,
    ADD `origVideoCap_Resolution_Channel2` INT NOT NULL ,
    ADD `origVideoTransportAddress_IP_Channel2` INT NOT NULL ,
    ADD `origVideoTransportAddress_Port_Channel2` INT NOT NULL ,
    ADD `origVideoChannel_Role_Channel2` INT NOT NULL ,
    ADD `destVideoCap_Codec_Channel2` INT NOT NULL ,
    ADD `destVideoCap_Bandwidth_Channel2` INT NOT NULL ,
    ADD `destVideoCap_Resolution_Channel2` INT NOT NULL

  13. Rob Newton April 5, 2010 at 12:36 pm

    We are running version 7.1. In that version (not sure when it was introduced), CCM has an option to push out CDR records to one or more FTP (or SFTP) servers.

    ****** edited by Aaron ******

    Rob’s post was too long to place as a comment, but actually has a better place as a standalone page. You can view his post here: https://teneo.wordpress.com/extras/parsing-cisco-cdr-for-callmanager-7-1-using-java/

  14. Troy Fisher May 1, 2010 at 10:06 am

    I couldn’t get the posted version to work for Call Manager 6. I changed a few things to make it work for me. Hope it helps someone. Also, below is an SQL statement that will change the Cisco CDR date for you…

    select callingPartyNumber, originalCalledPartyNumber, Duration, DateTimeOrigination,CONVERT_TZ(FROM_UNIXTIME(DateTimeOrigination),’-5:00′, ‘+0:00′) AS DateTimeOrigination FROM calldetails

    [This is for mySQL and the convert_tz is to change the date from GMT to your timezone.]

    My knowledge of python is limited to 2 days of knowledge. So, I didn’t get the archive feature working or the multiple file processing. I hope you all can get that updated.

    #!/usr/bin/env python

    import MySQLdb, sys, os
    import string
    import traceback
    import logging
    import zipfile

    # Variables

    #LOGGING=CRITICAL,ERROR,WARNING,INFO,DEBUG
    loggingLevel=logging.INFO

    # Log file path
    logFilePath=’C:\logs\calldata-import.log’

    # Path to CDR files
    cdr_path=’/cdr’

    # Database name to export data into
    database=’callmanager’

    # Database server
    dbhost=’localhost’

    # User to access the database
    dbuser=’root’

    # User to access the database
    pswrd=”

    # Database table to insert date into
    table=’calldetails’

    # Zip file to archive data to
    fileArchive=cdr_path+’/archive.zip’
    global cleanedColumns

    ######## Functions ############
    def createCdrSQL():
    # TODO: Refactor this so it’s not CDR specific. We’ll need
    # to also import CMR data as well
    totalRecordCount=0
    logging.debug(“Creating CDR SQL”)
    cdrListing = listCallFiles(cdr_path,”CDR”)
    startSQL=”INSERT INTO ” + table + ” (”
    fileCount=0

    logging.info(“Parsing %d files” % len(cdrListing))
    for file in cdrListing:
    columns=””
    fileRecordCount=0
    try:
    logging.debug(“Opening file %d of %d: %s” % (fileCount,len(cdrListing),file))
    #print file
    #cdrFile = open(cdr_path+”/”+file,’r’)
    cdrFile = open(“C:\Program Files (x86)\Python2.5\cdr\cdr_3.txt”,’r’)
    try:
    for line in cdrFile:
    if line.startswith(‘cdrRecordType’):
    logging.debug(“Building columns…”)
    headers = line.split(‘,’)
    for header in headers:
    newHeader=header.replace(‘”‘,”)
    columns+=newHeader+’,’
    #There will be a trailing comma at the end. Remove.
    cleanedColumns=columns.rstrip(‘,’)
    cleanedColumns+=”)”
    logging.debug(“Column data='” + cleanedColumns + “‘”)

    elif not line.startswith(‘INTEGER’):
    logging.debug(“Building values…”)
    values=””
    cnter=1
    splitLineArray = line.split(‘,’)
    for value in splitLineArray:
    if cnter == 1:
    value = value
    #cdrRecordType
    elif cnter == 2:
    value = value
    #globalCallID_callManagerId
    elif cnter == 3:
    value = value
    #globalCallID_callId
    elif cnter == 4:
    value = value
    #origLegCallIdentelifier
    elif cnter == 5:
    value = value
    #dateTimeOrigination
    elif cnter == 6:
    value = value
    #origNodeId
    elif cnter == 7:
    value = value
    #origSpan
    elif cnter == 8:
    value = value
    #origIpAddr
    elif cnter == 9:
    value = “‘” + value + “‘”
    #callingPartyNumber
    elif cnter == 10:
    value = “‘” + value + “‘”
    #callingPartyUnicodeLoginUserID
    elif cnter == 11:
    value = value
    #origCause_location
    elif cnter == 12:
    value = value
    #origCause_value
    elif cnter == 13:
    value = value
    #origPrecedenceLevel
    elif cnter == 14:
    value = value
    #origMediaTransportAddress_IP
    elif cnter == 15:
    value = value
    #origMediaTransportAddress_Port
    elif cnter == 16:
    value = value
    #origMediaCap_payloadCapability
    elif cnter == 17:
    value = value
    #origMediaCap_maxFramesPerPacket
    elif cnter == 18:
    value = value
    #origMediaCap_g723BitRate
    elif cnter == 19:
    value = value
    #origVideoCap_Codec
    elif cnter == 20:
    value = value
    #origVideoCap_Bandwidth
    elif cnter == 21:
    value = value
    #origVideoCap_Resolution
    elif cnter == 22:
    value = value
    #origVideoTransportAddress_IP
    elif cnter == 23:
    value = value
    #origVideoTransportAddress_Port
    elif cnter == 24:
    value = value
    #destLegIdentifier
    elif cnter == 25:
    value = value
    #destNodeId
    elif cnter == 26:
    value = value
    #destSpan
    elif cnter == 27:
    value = value
    #destIpAddr
    elif cnter == 28:
    value = “‘” + value + “‘”
    #originalCalledPartyNumber
    elif cnter == 29:
    value = “‘” + value + “‘”
    #finalCalledPartyNumber
    elif cnter == 30:
    value = “‘” + value + “‘”
    #finalCalledPartyUnicodeLoginUserID
    elif cnter == 31:
    value = value
    #destCause_location
    elif cnter == 32:
    value = value
    #destCause_value
    elif cnter == 33:
    value = value
    #destPrecedenceLevel
    elif cnter == 34:
    value = value
    #destMediaTransportAddress_IP
    elif cnter == 35:
    value = value
    #destMediaTransportAddress_Port
    elif cnter == 36:
    value = value
    #destMediaCap_payloadCapability
    elif cnter == 37:
    value = value
    #destMediaCap_maxFramesPerPacket
    elif cnter == 38:
    value = value
    #destMediaCap_g723BitRate
    elif cnter == 39:
    value = value
    #destVideoCap_Codec
    elif cnter == 40:
    value = value
    #destVideoCap_Bandwidth
    elif cnter == 41:
    value = value
    #destVideoCap_Resolution
    elif cnter == 42:
    value = value
    #destVideoTransportAddress_IP
    elif cnter == 43:
    value = value
    #destVideoTransportAddress_Port
    elif cnter == 44:
    value = value
    #dateTimeConnect
    elif cnter == 45:
    value = value
    #dateTimeDisconnect
    elif cnter == 46:
    value = “‘” + value + “‘”
    #lastRedirectDn
    elif cnter == 47:
    value = “‘” + value + “‘”
    #pkid
    elif cnter == 48:
    value = “‘” + value + “‘”
    #originalCalledPartyNumberPartition
    elif cnter == 49:
    value = “‘” + value + “‘”
    #callingPartyNumberPartition
    elif cnter == 50:
    value = “‘” + value + “‘”
    #finalCalledPartyNumberPartition
    elif cnter == 51:
    value = “‘” + value + “‘”
    #lastRedirectDnPartition
    elif cnter == 52:
    value = value
    #duration
    elif cnter == 53:
    value = “‘” + value + “‘”
    #origDeviceName
    elif cnter == 54:
    value = “‘” + value + “‘”
    #destDeviceName
    elif cnter == 55:
    value = value
    #origCallTerminationOnBehalfOf
    elif cnter == 56:
    value = value
    #destCallTerminationOnBehalfOf
    elif cnter == 57:
    value = value
    #origCalledPartyRedirectOnBehalfOf
    elif cnter == 58:
    value = value
    #lastRedirectRedirectOnBehalfOf
    elif cnter == 59:
    value = value
    #origCalledPartyRedirectReason
    elif cnter == 60:
    value = value
    #lastRedirectRedirectReason
    elif cnter == 61:
    value = value
    #destConversationId
    elif cnter == 62:
    value = “‘” + value + “‘”
    #globalCallId_ClusterID
    elif cnter == 63:
    value = value
    #joinOnBehalfOf
    elif cnter == 64:
    value = “‘” + value + “‘”
    #comment
    elif cnter == 65:
    value = “‘” + value + “‘”
    #authCodeDescription
    elif cnter == 66:
    value = value
    #authorizationLevel
    elif cnter == 67:
    value = “‘” + value + “‘”
    #clientMatterCode
    elif cnter == 68:
    value = value
    #callSecuredStatus
    elif cnter == 69:
    value = value
    #origConversationId
    else:
    print “There is a problem”
    cnter+=1
    newValue=value.replace(‘”‘,”‘”)
    values+=newValue+’,’

    #Remove the trailing comma at the end
    cleanedValues=values.rstrip(‘,’)
    cleanedValues+=”);”
    logging.debug(“Value data='” + cleanedValues+”‘”)
    logging.debug(“Building full SQL statement”)
    fullSQL=startSQL+cleanedColumns+” VALUES (” + cleanedValues
    #fullSQL=startSQL+” “+” VALUES (” + cleanedValues
    logging.debug(“Generated SQL is: ” + fullSQL)

    cursor = conn.cursor()

    logging.debug(“Inserting to DB”)
    try:
    cursor.execute(fullSQL)
    conn.commit()
    except:
    logging.critical(“Unable to insert the following SQL: ” + fullSQL)
    logging.error(traceback.print_exc(file=sys.stdout))

    except:
    logging.warning(“Could not enumerate files”)
    cdrFile.close()
    logging.error(traceback.print_exc(file=sys.stdout))
    except:
    logging.critical(“Could not open File”)
    fileCount+=1
    totalRecordCount+=fileRecordCount
    print (totalRecordCount)
    logging.info(“Inserted %d records for file %d of %d: %s” % (fileRecordCount,fileCount,len(cdrListing),file))
    logging.debug(“closing file: %s” % (file))
    cdrFile.close()
    logging.info(“Adding file to zip archive”)
    #addFileToZip(file)
    conn.close()
    logging.info(“Inserted %d records total” % (totalRecordCount))

    def listCallFiles(dir,type):
    fileListing=[]
    FILES = [“cdr.txt”]
    for file in FILES:
    if file.startswith(type):
    fileListing.append(file)
    fileListing=[“C:\Program Files (x86)\Python2.5\cdr”]
    return fileListing

    def addFileToZip(file):
    logging.debug(“Adding file %s to archive %s” % (file,fileArchive))

    #If the zip file exists, add to it, otherwise, create it.
    if os.path.exists(fileArchive):
    zippedFile=zipfile.ZipFile(fileArchive,’a’)
    else:
    zippedFile=zipfile.ZipFile(fileArchive,’w’)
    try:
    zippedFile.write(cdr_path+’/’+file,file)
    logging.debug(“File: %s was successfully added to archive” % (file))
    zippedFile.close()
    os.remove(cdr_path+’/’+file)
    except:
    logging.error(“Could not write to zip archive”)
    zippedFile.close()

    def errhandler ():
    print “Your input has not been recognised”

    ######### Enable Logging ###############
    logging.basicConfig(level=loggingLevel,
    format=’%(asctime)s %(levelname)-8s %(message)s’,
    datefmt=’%a, %d %b %Y %H:%M:%S’,
    filename=logFilePath,
    filemode=’a’)

    #logging.info(“Starting call-data import..”)

    ########## Try to connect to the Database #############
    try:
    conn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=pswrd, db=database)
    except:
    # Ooops… couldn’t connect
    logging.critical(“Unable to connect to the database. Terminating..”)
    logging.error(traceback.print_exc(file=sys.stdout))
    sys.exit(2)

    ############ Insert SQL ####################################

    createCdrSQL()

  15. Troy Fisher May 1, 2010 at 2:03 pm

    If you would like to search the CDR records for a range of dates you can use something like this. You can search a little bit and figure out how to factor for timezones. Just don’t forget that the Cisco DateTimeOrigination is based on GMT.

    select callingPartyNumber, originalCalledPartyNumber, sec_to_time(duration) AS ‘Duration’, CONVERT_TZ(FROM_UNIXTIME(DateTimeOrigination),’-5:00′, ‘+0:00’) AS DateTime, DateTimeOrigination FROM calldetails WHERE DateTimeOrigination unix_timestamp(CONVERT_TZ(‘2010-04-15 14:00:00′,’+0:00’, ‘-5:00’)) ORDER BY DateTimeOrigination DESC

  16. Robert Davis II - Flair Data Systems January 6, 2011 at 11:25 am

    Guys, I have been working on this type of application for
    some time now and let me share some pitfalls you will run into with
    this method. 1. As features are turned on and off in callmanager
    new fields will show up in the cdr records that may not exist in
    your database and cause your database insert to fail. 2. Cisco has
    a nasty habit of changing existing field definitions and types. For
    example i believe it was going from v5 to v6 (dont qoute me on this
    im remembering a couple years back) the pkid field changed from an
    int to a uniqueidentifier or something to that effect. My point
    here is just to highlight some things you may need to work around
    and give some ideas where to look when the inserts quit working. My
    current iteration of a cdr file importer is running in .NET (C#)
    and basically just sucks up the file tries the insert and if i get
    a 207 or 208 sql error code back i know its from a bad column
    definition in my insert. I then connect to the master and pull all
    the columns from my table and compare them to the file i’m
    importing and adjust the table to match. To do this you must make
    all fields nullable and never delete a field if it dissapears just
    leave it null in all future inserts. Hope that helps someone….
    Robert Davis

    • Aaron Paxson January 6, 2011 at 12:43 pm

      Which version of Call Manager are you using?

      Am I to understand, that instead of exporting all fields and giving them null values, CM just exports currently used fields?

      Way to go Cisco. That is really really poor.

      Thanks for the comment!

  17. Simon March 14, 2011 at 6:29 am

    Well, I am having some really funny problems here. I am
    running Python 2.6 on Windows7, and MySQL. I have the same problem
    as someone else had earlier up the posting. i.e. the program rums
    fine, and says everything worked, yet if I go to MySQL and select 8
    from calldetails, it tells me the table is empty! So, I expanded
    the logging so the full SQL query was logged, which shoed the query
    and it looked fine. I then copid it from the log file and pasted
    inot the MySQL command line interface, and lo and behold the data
    was inserted into the table perfectly! So, the CLI will insert the
    data ok (using an identical SQL query), but when I run the python
    code, it doesent work, but it doesent raise an error!! Anyone able
    to offer some help here? …….

    • Simon March 15, 2011 at 3:47 am

      Ahh…goddit. I did not do a conn.commit() The original
      posting left this out, but I see later on some one included it in
      theirs. Now mine work fine too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: