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.

image

This gives an output like this;

image

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

image

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.

5 Thoughts on “CS1000 Capturing CDR data

  1. Greetings sirWould you share the methodology used to do the sql injection? We have a very similiar requirement as to what you've solved above. However, our requirement is to inject the data into sql on a near real time basis.. thanks so much for your contribution.

  2. This seem like a very good inhouse solution, at the moment our company relies telewest to collate our call data, it would be nice to incorporate this into our systems and provide realtime statistics via an intranet page, please could you provide your sql methodology as that would be the icing on the Nicey Cake.

  3. Dave on June 28, 2013 at 5:14 pm said:

    Hi Kevin,

    Fantastic Script!
    I have been looking like a crazy for a in house solution like this. The company I work for it’s not interesting in investing for a Call Accounting solution but at the same time they are expecting I present phone call reports.
    Would you share the SQL Injection with me as well?
    I’d really appreciate.
    Regards.

    Dave.

  4. Thanks! This was brilliant.

  5. fairozkhan on November 24, 2014 at 11:23 am said:

    where do i connect my serial cable SDI or AUX. please let me know. thnx

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation