This is a bit of a evolution from my last post around dumping the CUCM database into a SQLite DB for offline parsing. Now that I have all of the data in a raw format from CM we can start playing with joining other stuff to it. I built a quick CDR script that parses out a CDR export into a SQLite DB as well and started joining those databases for reports. Next I built out a “crawler” type script that steps through the APIs on UCCX and Unity and pushes those results into a DB structure as well. Let the joining begin.
CDR is really only one part of the story though. Being able to pull reports out of Unity and UCCX would let me tie CDR even better, maybe. But at least it would be another data point. Unfortunately my attempt to pull reports from Unity v12.5 was aweful. Exporting as a CSV was the ugliest CSV I’ve ever seen. Commas all over the place that seem to be there to format it to look like something rather than just being data like you’d expect. So next step is to try and get at those raw reports and hopefully connecting to the DB will be helpful. The first step of doing that takes me down the “easier” more proven path of UCCX historical reporting. Since there are guides out there already that are fairly documented I can prove my ODBC drivers and base python scripts at least work in theory to connect and step through tables.
Now the bad side. People have blogs. I had to use several of them and then make a few guesses at some other stuff, cross my fingers a bunch and hope a lot, but I did it twice now which makes me an expert right? So here is my take on it and I’ll try to be detailed for on what worked for me.
Step1: Download and install the INFORMIX ODBC drivers
Hopefully this direct link will still work later, but if not you’ll need to search and find the INFORMIX SDK for Linux from the IBM site. Yes the Linux version because like the last posts say – Linux is the way and its even becoming the way on MS too so just start doing it already. First we start by installing a dependency and then download and install the SDK itself.
sudo add-apt-repository universe && sudo apt-get update
sudo apt-get install libncurses5
wget https://iwm.dhe.ibm.com/sdfdl/2v2/regs2/mstadm/informix/Xa.2/Xb.YBTN_DlRQltTa6Cy48z9gCQtXFNQQr92eMT0LBfPBIA/Xc.ibm.csdk.4.50.FC4W1.LNX.tar/Xd./Xf.LPr.D1vk/Xg.10978582/Xi.ifxdl/XY.regsrvs/XZ.dE3pe9ngs6_OV2yneD5jaMHSg_g/ibm.csdk.4.50.FC4W1.LNX.tar
untar ibm.csdk.4.50.FC4W1.LNX.tar
sudo ./installclientsdk
Step2: Set some environmental variables that do something that make the stuff work
Don’t ask me why or how or what, but it took me a while to track down everything that needed set and it doesn’t work without it. Assuming you installed the SDK in the default spot. Change it if you didn’t.
export INFORMIXDIR=/opt/IBM/Informix_Client-SDK/
export CSDK_HOME=$INFORMIXDIR
export LIBPATH=${INFORMIXDIR}/lib:${INFORMIXDIR}/lib/cli:${INFORMIXDIR}/lib/esql:${INFORMIXDIR}/lib:${INFORMIXDIR}/bin:${INFORMIXDIR}/etc:${LIBPATH}
export ODBCSYSINI=$INFORMIXDIR/etc
export ODBCINI=$INFORMIXDIR/etc/odbc.ini
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$INFORMIXDIR/lib/cli:$INFORMIXDIR/lib/esql
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
Step3: Setup the Driver
So first kind of crappy part is that the driver has to be setup independent of the application itself. Hopefully I’ll be able to figure out how to make it a bit more portable, but we are just getting it up and running as a POC anyway right?
Under your install folder for the driver we need to create (or modify) 3 files. They all have a lot of the same info and you still have to call out most of it in the python script too, but whatever. Problems for tomorrow. SUCCESS. made it down to just one file – sqlhosts.
YAY!! I got rid of all the files. Options have been added to the connection string in python now.
Step4: Install the Python library
So there’s two methods you can take here. IfxPy or pyodbc. Both should work fine now that the driver is installed and setup. I chose to use IfxPy really for the fact that its easy to get the column names with the results in one step. With pyodbc you’ve got to get the column names separately which sucks for discovery”.
pip3 install ifxpyNow we can test in Python to make sure the drivers and library is working in sync. The library is case sensitive, but if you don’t get any errors then everything is working fine this far and ready for some logic.
$ python3
>>>import IfxPyStep5: Make it do stuff
The first thing that took a bit of trail and error to figure out was the difference between the “host” and the “server”. A lot of guides have awesome DNS and just refer to everything as “uccx” and everything just works”. Not that lucky I guess. So key points – “host” is the IP or FQDN; “server” is the Informix DB name WITH “_uccx” on the back of it. To figure out the name in CCX go to “Tools -> Historical Reporting -> Database Server Configuration” and take a look at the server name. This should then have “_uccx” put on the tail end of it. Informix also doesn’t like hyphens so let’s hope you didn’t do that.
So the next step is to put it in some code. This was largely stolen from other sites and stitched together which I’ll do some linking below for references I ended up using. We are focused on UCCX Historical Reporting right now so the username is uccxhruser and the DB is db_cra.
#! /usr/bin/python3
import IfxPy
# connection parameter
Host='uccx.mycollablab.org'
DB='db_cra'
Port='1504'
User='uccxhruser'
PW='SUPP@secret'
ifxserver='uccx_uccx'
# build connection string
ConStr = f"SERVER={server};DATABASE={db};HOST={host};SERVICE={port};UID={user};PWD={passwd};DB_LOCALE=en_US.utf8;"
# connect to DB
try:
conn = IfxPy.connect( ConStr, "", "")
except Exception as e:
print ('ERROR: Connect failed')
print ( e )
quit()
# Select records
sql = """
select limit 5 contactType, applicationName
from ContactCallDetail
"""
# send query and get results
stmt = IfxPy.exec_immediate(conn, sql)
# get first row of results then loop through the remaining results to build a list
assoc = IfxPy.fetch_assoc(stmt)
ls=[]
while assoc != False:
ls.append(assoc)
assoc = IfxPy.fetch_assoc(stmt)
# print SQL query and results list
print(sql)
print(ls)Step6: Run
js@laptop:~/informix-testing$ ./test.py
select limit 2 contactType, applicationName from ContactCallDetail
[{'contacttype': 1, 'applicationname': 'Prompt Test'}, {'contacttype': 1, 'applicationname': 'Prompt Test'}]
So now that we have the drivers all working and the connections going we can start to explore whats available and hopefully start to parse out some fun info in the process.
https://github.com/netvoip/uccx-stats/blob/master/doc/ODBC_guide.md
https://datageek.blog/en/2019/02/28/connecting-to-an-informix-database-from-jupyter-notebook/
https://github.com/OpenInformix/IfxPy
https://kb.informatica.com/h2l/HowTo%20Library/1/0415_Accessing_Informix_Using_IBM_ODBC_driver.pdf