CS1000 Capturing CDR data

At our site we have a Nortel CS1000 handling all our telephony. We were looking into a solution for calculating call costs for billing purposes.

3rd Party solutions are expensive.

So I decided to look at an in-house solution.

The first issue was how to capture the data.

We have a dedicated PC which runs our TM software (and also does our FreeWMI – see previous blogs!).

Connecting this pc’s serial port to the 25 pin connector at the back of the CS1k (Com RS232 port) labelled port 2 should capture CDR data.

This will pump out raw CDR data at 9600 7/N/1 (Note the 7 Data Bits!)

To capture this data on the PC I used PuTTY (Serial Port Version).

Available here http://www.putty.org/

Here is a screenshot of the config.

This gives an output like this;

I now needed to log this to a file, again this was a setting within PuTTY…

This basically meant all CDR data was now being captured to the cdr.log file.

The next step was to decipher this raw data into something meaningful.

Upon investigation I found that the relevant data ie outgoing calls, was in data lines starting with a “D”.

A typical data line was….

D 013 00 22XX    A001012 02/03 13:31:48 00:03:44.0  9077852XXXX

the 4th section of this line was the Extension Number

the 6th section was the Date, “mm/dd” excluding year interestingly enough.

the 7th section was Time “hh:mm:ss”

The 8th was duration in “hh:mm:ss.s”

The 9th section was number dialled in our case with a leading 9 for outside line.

I then wrote the following script to get meaningful info from the cdr.log into a csv file.

Obviously there are settings specific to my system in here but this can be adapted to suit.

'##### SCRIPT FILE FOR Processing CDR Data :  31/01/11 by Kevvo###### Set Shell = WScript.CreateObject("WScript.Shell") Set FileSys = CreateObject("Scripting.FileSystemObject") Set Network = WScript.CreateObject("WScript.Network") 'On Error Resume Next SetLocale(2057) CDRFileName = "cdr.log" CDRLocalRate = 2.5 CDRNationalRate = 2.5 CDRInterRate = 15 CDRMobileRate = 9.9 CDRPremiumRate = 6.39 Set OpenLogFile = FileSys.OpenTextFile(CDRFileName,1,True)          Do While OpenLogFile.AtEndOfStream <> True sLine = OpenLogFile.ReadLine LeftChar = left(sline,1) if left(sline,1)="D" then   ExtTest = mid (sline,10,1)     if mid (sline,10,1)<>"T" and mid (sline,10,1)<>"A" then      '###### SPLIT LINE INTO AN ARRAY ######      aLine = split(sline, " ",-1,1)      '######  CHECK FOR HEADER ######       if left(aline(0),1)="D" then         if mid(sLine,55,1) <> "7" then           '######  GET VARIABLES ######         reccount = reccount+1                 LineLength = len(sLine)         CDRext2 = mid(sLine,10,4)         CDRdate2 = mid(sLine,27,5)         CDRDay = mid(sLine,30,2)         CDRMonth = mid(sLine,27,2)         CDRYear = "11"         CDRdate2 = CDRDay &"-"& CDRMonth &"-"& CDRYear         CDRtime2 = mid(sLine,33,8)         CDRduration2 = mid(sLine,42,8)         CDRHrs = left(CDRduration2,2)         CDRMins = mid (CDRduration2,4,2)         CDRSecs = right (CDRduration2,2)         CDRHrs2 = left(CDRtime2,2)         CDRMins2 = mid (CDRtime2,4,2)         CDRSecs2 = right (CDRtime2,2)         CDRDateTime = "20"&CDRYear&"-"&CDRMonth&"-"& CDRDay &" "& CDRHrs2 &":"& CDRMins2 &":"& CDRSecs2         AddCDRmins = 0         if CDRSecs > 0 then           CDRSecs = CDRSecs / 100           AddCDRmins = Int(CDRSecs + 1)         end if             CDRMinCount = (CDRHrs*60)+CDRMins+AddCDRmins             CDRdialled2 = mid(sLine,56,(LineLength-56))         CDRdialled2 = Replace(CDRdialled2," ","")         CDRDialledL3 = left(CDRdialled2,3)         CDRDialledL4 = left(CDRdialled2,4)         if len(CDRdialled2) > 4 then             if CDRDialledL3 <> "111" or  CDRDialledL3 <> "112" or CDRDialledL3 <> "116" or CDRDialledL4 <> "0800" or CDRDialledL4 <> "0808" or CDRDialledL4 <> "0500" then                     CDRStartDigit1 = left(CDRdialled2,1)             CDRStartDigit2 = left(CDRdialled2,2)                         if CDRStartDigit1 <> "0" then              CDRCost = CDRMinCount * CDRLocalRate              Else                if CDRStartDigit2 = "07" then                   CDRCost = CDRMinCount * CDRMobileRate                else                      if CDRStartDigit2 = "09" then                        CDRCost = CDRMinCount * CDRPremiumRate                      else                        CDRCost = CDRMinCount * CDRNationalRate                      end if                end if             end if             CDRCost = FormatNumber(CDRCost / 100,2)                         LogFileName="cdrcalc.csv"             Set FileSys = CreateObject("Scripting.FileSystemObject")             if FileSys.FileExists(LogFileName) then                 set LogFile = FileSys.OpenTextFile(LogFileName, 8)                 LogFile.WriteLine CDRext2 &",'"&CDRdate2&","&CDRtime2&","&CDRduration2&","& CDRMinCount&",'"&CDRdialled2&","&CDRCost                                 LogFile.Close             Else                 set LogFile = FileSys.CreateTextFile(LogFileName, true)                 LogFile.Close                 set LogFile = FileSys.OpenTextFile(LogFileName, 8)                 LogFile.WriteLine CDRext2 &",'"&CDRdate2&","&CDRtime2&","&CDRduration2&","& CDRMinCount&",'"&CDRdialled2&","&CDRCost                 LogFile.Close             end if             end if         end if        end if     end if end if end if Loop   OpenLogFile.Close StrNow = Day(Date)&"-"&Month(Date)&"-"&Year(Date)&"--"&Hour(Now)&"-"&Minute(Now)&"-"&Second(Now) FileSys.MoveFile CDRFileName, StrNow&"-"&CDRFileName wscript.echo "All Records Processed : "&reccount

I have also adapted this script further to inject the data into a MYSQL database and then the information was presented on a per user basis within our corporate intranet.