14
« on: December 26, 2019, 06:57:59 PM »
So, this one is waaaaaaaaaaay out in left field, but I'm wondering if anybody can help me out. Several years ago I wrote an application in Python that listens to all three of our 3300's SMDR feeds, parses out the data, and dumps it into a SQL database. Handy stuff.
The trouble is that after the upgrade to 9.0 sp1, I can connect to the telnet streams manually from a regular old cmd or powershell window, but for some reason all three PBX's actively refuse connections from the Python telnetlib.
I've tried telnetlib3, and it looks like it can connect, but the syntax and usage is so different that I can't seem to get it working. Anybody familair with telnetlib3 or with getting the old telnetlib to connect to the latest 3300 software?
My complete code, unchanged except for redacting some IP addresses and hostnames, is below:
import telnetlib, datetime, pymssql
from multiprocessing import Process
# This is an example call record:
# -05/29 05:48A 0000:05:22 X9999 016 9715553235 29913721 8000 8015 002 9715553235 3721
#
# This is the definition of the SMDR record:
# zmm/dd hh:mmp hhhh:mm:ss ppppppp fttt xxxxxxxxxxxxxxxxxxxxxmmmmmhsqqqqqqqk rrrrrrr aaaaaaaaaaaasiii_ aaaaaaaaaa ddddddd
#
# *the definition is 3 char longer than the example because mitel doesn't pad trailing spaces for the last field, so
# the full record could be anything from 113 to 120 char.
#
# *not all fields are separated, so parsing chars seems easier than regular expressions.
#
# *lumping meter pulses (mmmmm) in with 'digits dialed on trunk' (xxxxxxxxxxxxxxxxxxxxx) because ddot frequently
# overwrites meter pulses anyway.
############################################# Stuff to edit starts here #############################################
SQLsrvr = 'serverhostname' # SQL server hostname or IP. Redacted my real hostname.
SQLdb = 'SMDR' # Database name.
# One uniquely named function for each PBX, calling the Collect function with the corresponding PBX's IP. I've redacted my real IP addresses.
def Proc1():
Collect( '111.222.333.444' )
def Proc2():
Collect( '222.333.444.555' )
def Proc3():
Collect( '333.444.555.666' )
# One process start for each process function.
if __name__ == '__main__':
p1 = Process( target=Proc1 )
p1.start()
p2 = Process( target=Proc2 )
p2.start()
p3 = Process( target=Proc3 )
p3.start()
############################################# Stuff to edit stops here #############################################
def Collect(pbxip):
global ident, ident
tn = telnetlib.Telnet( pbxip )
tn.open( pbxip, 1752 )
conn = pymssql.connect( server=SQLsrvr, database =SQLdb )
cursor = conn.cursor()
while True:
try:
# read one line and encode it utf-8 so the /r/n at the end can be removed
callrec = tn.read_until('\n'.encode('utf-8'))
# grab the current time before there's any more processing.
collectDT = datetime.datetime.now()
# trim off the line terminations and then decode the call record back to a raw string value.
callrec = callrec[:-4]
callrec = callrec.decode('utf-8')
try:
lcode = callrec[0:1] # code to classify length of call
# replacing % and space to make sql queries easier later
if lcode == '%':
lcode = 'm'
if lcode == ' ':
lcode = '.'
# cstart - the datetime that the call started.
try:
# make sure that a call starting NYE and ending NYD isn't recorded with the wrong year.
if datetime.datetime.now().month == 1 and callrec[1:3] == '12':
callyear = str(datetime.datetime.now().year - 1)
else:
callyear = str(datetime.datetime.now().year)
# combine the year we just determined with the strings from callrec and create a datetime.
cstart = datetime.datetime.combine(
datetime.datetime.strptime( callrec[1:6] + "/" + callyear, '%m/%d/%Y' ).date(),
datetime.datetime.strptime( callrec[7:12] + " " + callrec[12:13] + "M", "%I:%M %p" ).time()
)
except:
cstart = ""
pass
# duration - the length of the call in seconds
try:
duration = (int( callrec[14:18] ) * 3600) \
+ (int( callrec[19:21] ) * 60) \
+ int( callrec[22:24] )
except:
duration = ""
pass
# strip() removes leading and trailing spaces. replace(" ","") removes all spaces.
caller = (callrec[25:32].strip()) # calling party
attnd = (callrec[33:34]).replace(" ","") # attendant on call
tta = (callrec[34:37]).replace(" ","") # time to answer
ddot = (callrec[38:64].strip()) # digits dialed on trunk
ccs = (callrec[64:65]).replace(" ","") # call completion status
spdcall = (callrec[65:66]).replace(" ","") # speedcall/callfwd flag
called = (callrec[66:73]).replace(" ","") # called party
trnsconf = (callrec[73:74]).replace(" ","") # transfer/conferende
thrdpty = (callrec[75:82]).replace(" ","") # third party
acct = (callrec[83:95]).replace(" ","") # account code/tag call identifier
rof = (callrec[95:96]).replace(" ","") # route optimization flag
sysid = (callrec[96:99].strip()) # system identifier
mlpp = (callrec[100:101]).replace(" ","") # mlpp precedence level
ani = (callrec[102:112].strip()) # ANI (caller ID)
dnis = (callrec[113:120].strip()) #DNIS (last 4 received by pbx)
except:
# If there's a general failure, we want to set everything except the original call record and collection
# time to ''. cstart and duration gor their own because they could fail to parse on valid records.
lcode = cstart = duration = caller = attnd = tta = ddot = ccs = spdcall = called = \
trnsconf = thrdpty = acct = rof = sysid = mlpp = ani = dnis = ''
pass
# Push to SQL
cursor.execute(
"INSERT INTO dbo.RAWRECORDS (callrec, collectDT) VALUES (%d,%d)",
(callrec, collectDT)
)
conn.commit()
ident = int(cursor.lastrowid)
cursor.execute(
"INSERT INTO dbo.CALLRECORDS (id, lcode, cstart, duration, caller, attnd, tta, ddot, ccs, spdcall, \
called, trnsconf, thrdpty, acct, rof, sysid, mlpp, ani, dnis) VALUES (%d,%d,%d,%d,%d,%d,%d,%d,%d,%d,\
%d,%d,%d,%d,%d,%d,%d,%d,%d)",
(ident, lcode, cstart, duration, caller, attnd, tta, ddot, ccs, spdcall, called, trnsconf,
thrdpty, acct, rof, sysid, mlpp, ani, dnis)
)
conn.commit()
except:
pass
This old version, which worked great before MiVB 9.0, requires Python 3, telnetlib, datetime, pymssql, and multiprocessing. It also assumes you have a database the right shape and similar enough SMDR options that your SMDR records parse the same.
Show Posts