Teneo !!!

Aaron’s blog on Networking, and Enterprise Technology

Parsing Cisco CDR for CallManager 7.1 using Java

This came from Rob Newton in an email regarding my original post on parsing Call Manager 5 using Python.  What a fantastic contribution!  Thanks Rob!  I totally prefer Java, but my Python solution was made because I was in a  hurry, and Python is great for prototyping.  I just may have to put this one into production for me!

———————————————

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.

For our receiving server we decided to use the Java based Apache FTP Server as our FTP server in order to make use of the FTPLET functionality. So in the process, I went ahead and ported Aaron’s Python script to Java as an FTPLET that gets triggered when a new file is uploaded to the server.

I chose to override the onUploadEnd event to process the CDR files as they are uploaded. This prevents the need to scrape a directory because instead, when a new file is uploaded to the FTP server, it gets processed, archived, then deleted immediately. Anything that fails to process will go into a failed imports directory.

The archiving is a bit more robust now with daily archive zip files in a dedicated archives directory. All of the paths are configurable by changing the the vars in the top of the Java source, just like in Aaron’s Python script.

We use MySQL, so the example shows that, however changing the database connection is as simple as getting the correct JDBC driver for what you want to connect to, add it to the class path of the FTP server, then change the connection string to use it.

I have added more to the logging output than what Arron had originally. It’s pretty verbose, so just knock out whatever you don’t need then recompile as a JAR file. Deployment instructions for the FTPLET can be found on their site.

Here is some example output. The Apache FTP Server directs the output to log files that are configurable. All the messages from this parser go to the out.log file which accepts System.out messages.

I have shortened the SQL insert statements due to size, but the full SQL insert is included in the log by default.

 
FTP_USERNAME_HERE just finished uploading CDR file: cdr_StandAloneCluster_02_201003312101_13 
Initiating CDR Parsing FTPLET - 2010-04-05 13:09:59 
======================================================= 
    Finished parsing the columns. 
    Connecting to the database using: jdbc:mysql://localhost:3306/databaseName as mysql_username 
    ----- Parsing row 1---------- 
        Finished parsing the record values. 
        Finished building the insert statement. 
            insert into cisco_calldetail [... removed for size ...] 
        Successfully inserted the CDR record into the database. 
    ----- Parsing row 2---------- 
        Finished parsing the record values. 
        Finished building the insert statement. 
            insert into cisco_calldetail [... removed for size ...] 
        Successfully inserted the CDR record into the database. 
    ----- Parsing row 3---------- 
        Finished parsing the record values. 
        Finished building the insert statement. 
            insert into cisco_calldetail [... removed for size ...] 
        Successfully inserted the CDR record into the database. 
    ----------------------------- 
    Inserted 3 records in total. 
    Added cdr_StandAloneCluster_02_201003312101_13 to CDR_Files_2010-04-05.zip in the archive directory. 
    Deleted cdr_StandAloneCluster_02_201003312101_13 
======================================================= 
Done - cdr_StandAloneCluster_02_201003312101_13 

Here is the java code for the FTPLET. Hope it helps someone, this blog post helped us a ton, thanks Aaron!

 
package com.contactvantage.ftplet; 

import java.io.BufferedReader; 
import java.io.DataInputStream; 
import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.InputStreamReader; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.text.DateFormat; 
import java.text.SimpleDateFormat; 
import java.util.Date; 
import java.util.logging.Logger; 
import java.util.zip.ZipEntry; 
import java.util.zip.ZipInputStream; 
import java.util.zip.ZipOutputStream; 

import org.apache.ftpserver.ftplet.*; 

public class CustomizedFtplet extends org.apache.ftpserver.ftplet.DefaultFtplet { 
        static Logger log = null; 

        // Path to CDR files 
        private String cdr_path = "C:\\Call_Logs\\"; 

        // Database server 
        private String dbhost = "localhost:3306"; 

        // Database name to export data into 
        private String database = "DATABASE_NAME_HERE"; 

        // Database table to insert date into 
        private String table = "TABLE_NAME_HERE"; 

        // User to access the database 
        private String dbuser = "SQL_USERNAME_HERE"; 

        // Password to access the database 
        private String dbpass = "SQL_PASSWORD_HERE"; 

        // Class name of the jdbc driver to use 
        private String jdbcDriverClass = "com.mysql.jdbc.Driver"; 

        // JDBC connection string to use when connecting 
        private String jdbcConnectionString = "jdbc:mysql://"+dbhost+"/"+database; 
        
        // Get the current date for the archive file name 
        private String todaysDateStr = getCurrentDateTime("yyyy-MM-dd"); 

        // Zip file to archive data to 
        private String archiveFilePath = cdr_path + "Archives\\CDR_Files_" + todaysDateStr + ".zip"; 

        // Path to a directory that should hold failed import CDR files 
        private String failedImportsPath = cdr_path + "Failed_Imports\\"; 

        public FtpletResult onUploadEnd(FtpSession session, FtpRequest request) 
        throws FtpException, IOException { 
                String strLine; 
                String cleanedColumns = ""; 
                String cleanedValues = ""; 
                String fullSQL = ""; 
                Connection conn = null; 
                Statement stmt = null; 
                int parsedRows = 0; 
                int insertedRows = 0; 
                
                // Create a file object for out CDR file 
                File cdrFile = new File(cdr_path + request.getArgument()); 

                // Open the uploaded CDR file 
                FileInputStream fstream = new FileInputStream(cdrFile.getAbsolutePath()); 

                System.out.println(session.getUser().getName() + " just finished uploading CDR file: " + cdrFile.getName()); 
                System.out.println("Initiating CDR Parsing FTPLET - " + getCurrentDateTime("yyyy-MM-dd HH:mm:ss")); 
                System.out.println("======================================================="); 
                
                // Get the object of DataInputStream 
                DataInputStream in = new DataInputStream(fstream); 
                BufferedReader br = new BufferedReader(new InputStreamReader(in)); 
                
                strLine = br.readLine(); 
                if (strLine.startsWith("\"cdrRecordType\"")) { 
                        String[] columns = strLine.split(",\\s*"); 
                        cleanedColumns = "("; 
                        for(String column : columns) { 
                                column = column.replace("\"", ""); 
                                cleanedColumns += column + ", "; 
                        } 
                        cleanedColumns = cleanedColumns.substring( 0, cleanedColumns.length() - 2 ) + ")"; 
                        System.out.println("    Finished parsing the columns."); 
                }else{ 
                        System.out.println("    INVALID FORMAT!!"); 
                        in.close(); 
                        
                        // Delete the invalid file 
                        try { 
                                cdrFile.delete(); 
                                System.out.println("    Deleted " + cdrFile.getName()); 
                        } catch(SecurityException e) { 
                                System.out.println("    Error deleting the uploaded file: " + e.getMessage()); 
                        } 
                        return FtpletResult.DEFAULT; 
                } 
                
                
                System.out.println("    Connecting to the database using: " + jdbcConnectionString + " as " + dbuser); 
                
                try 
                { 
                        Class.forName(jdbcDriverClass).newInstance(); 
                        conn = DriverManager.getConnection(jdbcConnectionString,dbuser,dbpass);   
                } 
                catch (ClassNotFoundException e) { 
                        System.out.println("    Failed to connect to the database: " + e.getMessage()); 
                        e.printStackTrace(); 
                } 
                catch (IllegalAccessException e) { 
                        System.out.println("    Failed to connect to the database: " + e.getMessage()); 
                        e.printStackTrace(); 
                } 
                catch (InstantiationException e) { 
                        System.out.println("    Failed to connect to the database: " + e.getMessage()); 
                        e.printStackTrace(); 
                } 
                catch (SQLException e)           { 
                        System.out.println("    Failed to connect to the database: " + e.getMessage()); 
                        e.printStackTrace(); 
                } 

                if (conn != null) 
                { 
                        // Process each additional data row in the CDR file 
                        while ((strLine = br.readLine()) != null)   { 
                                if (!strLine.startsWith("INTEGER")) { 
                                        parsedRows++; 
                                        System.out.println("    ----- Parsing row " + parsedRows + "----------"); 
                                        String[] values = strLine.split(",\\s*"); 
                                        cleanedValues = "("; 
                                        for(String value : values) { 
                                                value = value.replace("\"\"", "null"); 
                                                value = value.replace("\"", ""); 
                                                value = value.replace("'", ""); 
                                                if (!value.equalsIgnoreCase("null")) { 
                                                        value = "\"" + value + "\""; 
                                                } 
                                                cleanedValues += value + ", "; 
                                        } 
                                        cleanedValues = cleanedValues.substring( 0, cleanedValues.length() - 2 ) + ")"; 
                                        System.out.println("        Finished parsing the record values."); 
                                        
                                        // Build the insert statement from the columns and values 
                                        fullSQL = "insert into " + table + " " + cleanedColumns + " values " + cleanedValues; 
                                        System.out.println("        Finished building the insert statement."); 
                                        System.out.println("            " + fullSQL); 

                                        // Try to insert the record (execute the built insert statement) 
                                        try{ 
                                                stmt = conn.createStatement(); 
                                                insertedRows += stmt.executeUpdate(fullSQL); 
                                                System.out.println("        Successfully inserted the CDR record into the database."); 
                                        } 
                                        catch (Exception e){ 
                                                System.out.println("        Failed to execute the insert statement: " + e.getMessage()); 
                                        } 
                                } 
                        } 
                        
                        System.out.println("    -----------------------------"); 
                        
                        // Close the input stream 
                        try { 
                                in.close(); 
                        } 
                        catch(Exception e){ 
                                System.out.println("    Failed to close the CDR file: " + e.getMessage()); 
                        } 

                        // Close the connection to the database 
                        try { 
                                conn.close(); 
                        } 
                        catch(Exception e){ 
                                System.out.println("    Failed to close the database connection: " + e.getMessage()); 
                        } 

                        // Write how many rows were written to the database 
                        System.out.println("    Inserted " + insertedRows + " records in total."); 
                        
                        // Check to see if a daily archive has been created yet 
                        File archiveZipFile = new File(archiveFilePath); 
                        try { 
                                // Create the file if it doesn't already exist 
                                archiveZipFile.createNewFile(); 
                        }catch(Exception e) { 
                                System.out.println("    Error creating the archive zip ("+archiveZipFile.getAbsolutePath()+"): " + e.getMessage()); 
                        } 

                        // Add the CDR file we just processed to the archive zip file 
                        try { 
                                addFileToExistingZip(archiveZipFile, cdrFile); 
                                System.out.println("    Added " + cdrFile.getName() + " to " + archiveZipFile.getName() + " in the archive directory."); 

                                // Delete the originally uploaded CDR file now that it has been archived 
                                try { 
                                        cdrFile.delete(); 
                                        System.out.println("    Deleted " + cdrFile.getName()); 
                                } catch(SecurityException e) { 
                                        System.out.println("    Error deleting the uploaded CDR file after archiving it: " + e.getMessage()); 
                                } 
                        } catch(IOException e) { 
                                System.out.println("    Error adding the CDR file to the archive zip: " + e.getMessage()); 
                        } 
                } else { 
                        System.out.println("    All steps skipped due to database connection failure."); 
                        
                        // The import failed so lets move the CDR to the failure directory 
                    File failedImportsDir = new File(failedImportsPath); 
                    
                    // Move file to new directory 
                    if (cdrFile.renameTo(new File(failedImportsDir, cdrFile.getName()))) { 
                            System.out.println("    CDR file moved to the import failures directory: " + failedImportsPath); 
                    }else{ 
                            System.out.println("    Error moving the CDR file to the failed imports directory."); 
                    } 
                } 

                System.out.println("======================================================="); 
            System.out.println("Done - " + cdrFile.getName() + "\r\n\r\n"); 

                return FtpletResult.DEFAULT; 
        } 
        
        public static void addFileToExistingZip(File zipFile, File file) throws IOException { 
                // Get a temp file 
                File tempFile = File.createTempFile(zipFile.getName(), null); 
                
        // Delete it, otherwise you cannot rename your existing zip to it. 
                tempFile.delete(); 

                boolean renameOk=zipFile.renameTo(tempFile); 
                if (!renameOk) 
                { 
                        throw new RuntimeException("could not rename the file "+zipFile.getAbsolutePath()+" to "+tempFile.getAbsolutePath()); 
                } 
                byte[] buf = new byte[1024]; 
                
                ZipInputStream zin = new ZipInputStream(new FileInputStream(tempFile)); 
                ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipFile)); 
                
                ZipEntry entry = zin.getNextEntry(); 
                while (entry != null) { 
                        String name = entry.getName(); 
                        boolean notInFiles = true; 
                        if (file.getName().equals(name)) { 
                                notInFiles = false; 
                                break; 
                        } 
                        if (notInFiles) { 
                                // Add ZIP entry to output stream. 
                                out.putNextEntry(new ZipEntry(name)); 
                                
                                // Transfer bytes from the ZIP file to the output file 
                                int len; 
                                while ((len = zin.read(buf)) > 0) { 
                                        out.write(buf, 0, len); 
                                } 
                        } 
                        entry = zin.getNextEntry(); 
                } 
                
                // Close the streams                 
                zin.close(); 
                
                // Compress the file 
                InputStream in = new FileInputStream(file); 
                
                // Add ZIP entry to output stream. 
                out.putNextEntry(new ZipEntry(file.getName())); 
                
                // Transfer bytes from the file to the ZIP file 
                int len; 
                while ((len = in.read(buf)) > 0) { 
                        out.write(buf, 0, len); 
                } 
                
                // Complete the entry 
                out.closeEntry(); 
                in.close(); 
                        
                // Complete the ZIP file 
                out.close(); 
                tempFile.delete(); 
        } 

    private String getCurrentDateTime(String format) { 
        DateFormat dateFormat = new SimpleDateFormat(format); 
        Date date = new Date(); 
        return dateFormat.format(date); 
    } 
} 

==============================

Advertisements

2 responses to “Parsing Cisco CDR for CallManager 7.1 using Java

  1. Rob Newton April 7, 2010 at 10:16 am

    Thanks for getting it on the site for me, Aaron. I really hope it helps someone else like your original post helped us.

  2. Carlos Ortiz August 26, 2010 at 6:14 pm

    Guys,

    This is a great post and something I would love to try. Can one of you expand a bit on what OS’s are running here and what applications are running where. From above you are running Java, MySQL, Apache and the FTPlet. How many different servers are we talking and is this all Unix or anything running in Windows?

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: