Query Cisco Unity Databases from Linux.

[Juan Gerardo Hernandez]

Good morning Developer friends!!

Here is a newbie for DevNet, I respect your work very much and I hope I can be a qualified Cisco Developer.

Right now I am working into Collaboration Stuff.

Could you please help me with something?

Right now I am using Cisco Unity Connection Tools to query a database in unity connection.

in the query builder (GUI) I am putting this.

select count(*) from tbl_notifyq

to get the count,

but I would like to do this automatically in several clusters using a Linux computer.

Which would be the best way to accomplish the task,?

Note: you don;t have to be very specific, I know "Google is your best fried", but I hope you can give me some over-simplified instructions like.

Get this,

download this

use this

be happy!

What do you think?

Thank you very much for your time, and have a nice day.

[dstaudt · Accepted solution]

I got the below working on Ubuntu 19.10 using the Python IfxPy library:

import IfxPy

connStr = 'CLIENT_LOCALE=en_US.57372;DB_LOCALE=en_US.57372;SERVER=ciscounity;DATABASE=unitydyndb;uid=Administrator;pwd=password'
conn = IfxPy.connect(connStr, '', '')

sql = 'select count(*) from tbl_notifyq'
stmt = IfxPy.exec_immediate(conn, sql)
dictionary = IfxPy.fetch_assoc(stmt)

print('tbl_notifyq: ',  dictionary[''])
 
IfxPy.free_result(stmt)
IfxPy.free_stmt(stmt)

IfxPy.close(conn)

- Download Informix SDK: https://www.ibm.com/products/informix/developer-tools?mhsrc=ibmsearch_a&mhq=informix
- Install SDK:

- On Ubuntu 19.10 I had to install libncurses5: sudo apt install libncurses5
- I selected the ODBC Driver, Common Database Utilities and Global Language Support (GLS) only
- I installed to /home/dstaudt/opt/IBM/Informix_Client-SDK (hereafter $INFORMIXDIR)
- Create sqlhosts file in $INFORMIXDIR/etc (important to use 'ciscounity' as the dbservername:)

#dbservername nettype hostname servicename options
ciscounity onsoctcp ds-cuc115.cisco.com 20532

- Install IfxPy: pip3 install ifxpy
- Project homepage: https://github.com/OpenInformix/IfxPy
- Setup your environment so that the following variables are active when the Python script runs (in VS Code I used launch.json):

LD_LIBRARY_PATH: /home/dstaudt/opt/IBM/Informix_Client-SDK/lib:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/esql:/home/dstaudt/opt/IBM/Informix_Client-SDK/lib/cli
INFORMIXDIR: /home/dstaudt/opt/IBM/Informix_Client-SDK

- I was stuck for a long time trying to get client/DB locales correct (weird locale used: en_US.57372 apparently: utf-8). Eventually I used the dbaccess tool (part of the SDK installed above), which might come in handy for CLI access to the database: https://www.ibm.com/support/pages/how-run-informix-sql-commands-your-rational-synergy-database-unix-or-linux-environment
- The key IfxPy sample that showed how to use the locale options: https://github.com/OpenInformix/IfxPy/blob/58edef9321979b82df177f7ba2e9c49bc71544eb/Examples/Sample1.ipynb
- Key info on how to determine the locale of a database: https://www.ibm.com/support/pages/how-fix-error-message-sql1822-informix-error-code-23197
- Info on how to enable necessary roles (System Administrator/Remote Access) and services for the Unity Connection database proxy: http://www.ciscounitytools.com/Applications/CxN/MessageHunter/Help/MessageHunter.htm#_Toc504638754
- Some info re Unity and Informix ODBC: https://ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html
- Cisco Unity Tools: https://ciscounitytools.com/index.html
-IfxPy usage examples: https://github.com/OpenInformix/IfxPy

Full VS Code project attached

[Juan Gerardo Hernandez]

Thank you!

5 Stars!

Juan

[Juan Gerardo Hernandez]

Took a while to tested I Struggled a lot to make it work in Widows, at the end I could because version of python and win64 drivers and Ifxpy.

If you are interesting in what exactly I am struggling on, please take a look at.

https://github.com/OpenInformix/IfxPy/issues/50

Then I exactly followed your instructions in Linux and I got ti working,

I got this error:

juan@ubuntu:~/py_works$ python3 cuc_sql.py 
Traceback (most recent call last):
  File "cuc_sql.py", line 4, in <module>
    conn = IfxPy.connect(connStr, "", "")
Exception: [Informix][Informix ODBC Driver][Informix]Network connection is broken. SQLCODE=-25582

That was because the user needed to have the "Remote Administrator" Role assigned.

Another disadvantage, the password is sent in clear text.

This is the partial output of a wireshark capture.

sqlexec administrator -pciscopsdt 4.50.FC2 serial -dunitydyndb -fIEEEI DBPATH=//ciscounity DBMONEY=$. CLIENT_LOCALE=en_US.57372 NODEFDAC=no CLNT_PAM_CAPABLE=1 DB_LOCALE=en_US.57372 :.....=...d.e...=..IEEEI..lsrvinfx......+Informix Dynamic Server Version 9.56.UC9W1..#Software Serial Number AAA#...ciscounity_pub....<..............on.........=soctcp.......f...............k......Nt......hq-cuc...../..n.......t./.........%/usr/local/cm/db/informix/bin/oninit....~......}.b....~.	.............Q.......x..DBTEMP../tmp..SHELL..	/bin/bash...SUBQCACHESZ...10..PATH.x/home/juan/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin..NODEFDAC..no..OPTOFC..0..........#...c.............c............... select count(*) from tbl_notifyq...1.............	....................(count(*))...................7..................sql_cur2...........	...............	...........................7...............
...............8.8

Do you know how this can be encrypted?

[dwasserzug1]

I was able to get this to work on RHEL 7.

I'll put my steps if it helps anyone.

Prerequisites:

• Using RHEL 7 x86_64.
• Python is already installed.
• Ifxpy is already installed.
• ncurses libraries are already installed.

• "Connection Database Proxy" is activated in "Cisco Unity Connection Serviceability -> Service Management".

• Remote Administrator role is associated to your user.

1. https://www.ibm.com/products/informix/developer-tools?mhsrc=ibmsearch_a&mhq=informix

Eventually found the file I needed for x86_64 Linux (ibm.csdk.4.50.FC5.LNX.tar)

2. Uploaded tar to server using SFTP.

3. Elevated to root.

sudo -i -u root

4. unzip the tar file from step 1 and 2.

tar xvf ibm.csdk.4.50.FC5.LNX.tar

5. Ran installer (selected default options for all).

./installclientsdk

6. Created static environment paths file.

vi /etc/profile.d/informix.sh

  7. Added path information to the file.

#!/bin/bash
export LD_LIBRARY_PATH=/opt/IBM/Informix_Client-SDK/lib:/opt/IBM/Informix_Client-SDK/lib/esql:/opt/IBM/Informix_Client-SDK/lib/cli
export INFORMIXDIR=/opt/IBM/Informix_Client-SDK

 8. Created the sqlhosts file.

vi /opt/IBM/Informix_Client-SDK/etc/sqlhosts

 9. Entered in the hostname for the test (replace unityloab01 with the real name).

ciscounity onsoctcp unitylab01 20532

10. I ran dstaudt's script and it worked fine. I was also able to write my own script using the same and it worked fine.