Log DID activity

[Q6600]

Is there a free way for me to do very basic extension (DID) logging on a CS1000 system?

Example: We have 5 ee's in one dept. We want to record (and then somehow report, I have some moderate excel skills) the number of incoming and outgoing calls on each of these 5 phones for a given time, say a week.

I dont need to backtrack, its okay if I have to start fresh.

Thanks for any help!

 

[bignose21]

I guess you could capture the CDR on a spare TTY port to a PC then import it into Excel, I think there is alrady a CDR to Excel document on GHTROUT website

http://www.ghtrout.com/

 

[DWheater]

It really depends on what you need, if it's a one off or occasional report then you could use something whatever terminal program you use to access your CS1K and set it to save the output to a file, connect the serial port to your Call Logging port and leave it to record for however long. I have a VB Script that will read one or more log files in a folder and parse them into an Excel Spreadsheet. You must have MS Excel installed on the PC for this to work.

'===========================================================================
'Name:		CallLogger.vbs
'Author		David Wheater
'Date		30/08/2013
'Purpose	Parses output from Meridian Call Logger
'---------------------------------------------------------------------------
'30/08/2013	Version 1.0	Initial Build
'===========================================================================
'Create Objects
Set objShell		= CreateObject("Wscript.Shell")
Set objFSO		= CreateObject("Scripting.FileSystemObject")
Set objNetwork		= CreateObject("Wscript.Network")

Const ForReading	= 1
Const ForWriting	= 2
Const ForAppending	= 8

'===========================================================================
' Variables
'===========================================================================

strLogFile		= "details.txt"
strCallsPath	= "Y:\"		'<< Change to wherever your Call Log files are.


'===========================================================================
' Start of Script
'===========================================================================


Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.add
objExcel.Visible = True

objExcel.Range("A1").Value = "Type"
objExcel.Columns("A").NumberFormat  = "@"
objExcel.Range("A1").ColumnWidth = 4.57

objExcel.Range("B1").Value = "From"
objExcel.Columns("B").NumberFormat  = "@"
objExcel.Range("B1").ColumnWidth = 9.3

objExcel.Range("C1").Value = "To"
objExcel.Columns("C").NumberFormat  = "@"
objExcel.Range("C1").ColumnWidth = 9.3

objExcel.Range("D1").Value = "Dialled"
objExcel.Columns("D").NumberFormat  = "@"
objExcel.Range("D1").ColumnWidth = 14

objExcel.Range("E1").Value = "Date"
objExcel.Range("e1").ColumnWidth = 11

objExcel.Range("F1").Value = "Time"
objExcel.Range("F1").ColumnWidth = 9

objExcel.Range("G1").Value = "Length"
objExcel.Range("G1").ColumnWidth = 9

objExcel.Range("H1").Value = "CLID"
objExcel.Columns("H").NumberFormat  = "@"
objExcel.Range("H1").ColumnWidth = 14

objExcel.Range("I1").Value = "OrigTN"
objExcel.Columns("I").NumberFormat  = "@"

objExcel.Range("J1").Value = "TermTN"
objExcel.Columns("J").NumberFormat  = "@"

objExcel.Range("K1").Value = "TTA"
objExcel.Columns("K").NumberFormat  = "@"
objExcel.Range("K1").ColumnWidth = 8

objExcel.Range("L1").Value = "ReDir"
objExcel.Columns("L").NumberFormat  = "@"

objExcel.Range("M1").Value = "TWT"
objExcel.Columns("M").NumberFormat  = "@"

objExcel.Range("N1").Value = "100 Hour"


intRow = 2
strRecType = ""

Set objFolder = objFSO.GetFolder (strCallsPath)
For Each objFile in objFolder.Files
	'Wscript.Echo "Reading... " & objFile.path
	ReadFile objFile.path
Next




Sub ReadFile(strCallsFile)

	Set objCalls = objFSO.OpenTextFile(strCallsFile,ForReading)
	Do Until objCalls.AtEndOfStream
		strLine = objCalls.ReadLine
		'Strip out Null characters
		strLine = Replace(strLine,Chr(0),"")
		strLine = Replace(strLine,Chr(0),"")
		strLine = Replace(strLine,Chr(0),"")
		strLine = Replace(strLine,Chr(0),"")

		If Len(strLine) >= 88 Then
			strRecType = Left(strLine,1)
			strFrom = strip(mid(strLine,10,7))
			strTo = strip(mid(strLine,18,7))
			strDialled = Strip(Mid(strLine,52,22))
			strDate =  Strip(Mid(strLine,26,5)) & "/" & Year(now())
			strtime =  Strip(Mid(strLine,32,8))
			strLength = Strip(Mid(strLine,41,8))

		ElseIf Len(strLine) = 87 Then
			strCLID = Strip(Mid(strLine,3,16))
			strOrigTN = Strip(Mid(strLine,54,11))
			strTermTN = Strip(Mid(strLine,66,11))

		ElseIf Len(strLine) = 52 Then
			strTTA = Strip(Mid(strLine,3,5))
			strReDir = Strip(Mid(strLine,8,1))
			strTWT = Strip(Mid(strLine,9,5))
			str100 = Mid(strLine,40,3)

		ElseIf Len(strLine) = 1 Then
			If strRecType >= "A" and strRecType =< "Z" Then
				objExcel.Cells(intRow,1).Select
				objExcel.Cells(intRow,1).Value = strRecType 
				objExcel.Cells(intRow,2).Value = strFrom
				objExcel.Cells(intRow,3).Value = strTo
				objExcel.Cells(intRow,4).Value = strDialled 
				objExcel.Cells(intRow,5).Value = strDate
				objExcel.Cells(intRow,5).NumberFormat  = "dd/mm/yyyy"
				objExcel.Cells(intRow,6).Value = strTime
				objExcel.Cells(intRow,6).NumberFormat  = "hh:mm:ss"
				objExcel.Cells(intRow,7).Value = strLength
				objExcel.Cells(intRow,6).NumberFormat  = "hhh:mm:ss"
				objExcel.Cells(intRow,8).Value = strCLID
				objExcel.Cells(intRow,9).Value = strOrigTN
				objExcel.Cells(intRow,10).Value = strTermTN
				objExcel.Cells(intRow,11).Value = strTTA
				objExcel.Cells(intRow,12).Value = strReDir
				objExcel.Cells(intRow,13).Value = strTWT
				objExcel.Cells(intRow,14).Value = str100

				intRow = intRow +1
			End If

			strRecType = ""
			strFrom = ""
			strTo = ""
			strDialled = ""
			strDate = ""
			strTime = ""
			strLength = ""
			strCLID = ""
			strTTA = ""

		Else
			LogToFile Len(strLine)
		End If

	Loop

End Sub


'Finish off spreadsheet and show
objExcel.Range("A:N").Autofilter
objExcel.Range("A2").Select 
objExcel.ActiveWindow.FreezePanes = True
objExcel.Visible = True

objShell.Popup "Done.", 10









'===========================================================================
' Support Subroutines
'===========================================================================

Sub LogToFile(strMsg)
	If objFSO.FileExists(strLogFile) Then
		Set objLogFile = objFSO.OpenTextFile(strLogFile,ForAppending)
	Else
		Set objLogFile = objFSO.CreateTextFile(strLogFile)
	End If

	objLogFile.WriteLine strMsg
	'Wscript.Echo strMsg
	objLogFile.Close

End Sub



Function Strip(strString)
	tmp = Trim(strString)
	tmp = Replace(tmp,Chr(0),"")
	tmp = Replace(tmp,"X","")
	tmp = Replace(tmp," ","")

	tmp = Replace(tmp,"A","")

	Strip = tmp
End Function

 

[DWheater]

If you want a more permanent option then you'll need some sort of Serial Port recording software (the company that wrote the one I use has gone out of business) so I can't recommend one but Google it and you'll find several. You'll then want a SQL database of some sort. I'm using MS SQL Express 2008 R2, but mine is currently a Demo system for the customer, if they decide to go for it then it will be upgraded to a full version of MS SQL.

To get the data from the raw text files you'll need something to read through them and add the data into the database. I adapted the above script to add the call records into a database table once per day using the Windows Task Scheduler. If you are interested I can send you a copy of that script, though it may need adjusting to suit your setup. You'll also need to setup tables for a directory so that you can reference names in your reports.

Let me know if you are interested. Though try the Excel spreadsheet first, that might give you what you need.

 

[Q6600]

Thank you so much for the replies! I would like to get started on capturing the CDR...

I was wondering if someone could... help me with that? I currently connect with PuTTY to the switches IP address. There are built in settings for saving a log file which I have used before on Dave Higham's DNoBer.xls after doing a prt dnb in ld 20.

What are the commands to start capturing a CDR?

Thanks again.