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.
