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” Set OpenLogFile = FileSys.OpenTextFile(CDRFileName,1,True) ‘###### SPLIT LINE INTO AN ARRAY ###### ‘###### GET VARIABLES ###### LineLength = len(sLine) if CDRSecs > 0 then CDRMinCount = (CDRHrs*60)+CDRMins+AddCDRmins CDRdialled2 = mid(sLine,56,(LineLength-56)) CDRDialledL3 = left(CDRdialled2,3) CDRStartDigit1 = left(CDRdialled2,1) if CDRStartDigit1 <> “0” then LogFileName=”cdrcalc.csv” LogFile.Close end if |
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.
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.
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.
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.
Thanks! This was brilliant.
where do i connect my serial cable SDI or AUX. please let me know. thnx