Paul Tursan

UC & Network Consultant

Querying Cisco Unified Contact Center Express (UCCX) Historical Database with Python pyodbc and Informix ODBC Driver

Often it's kind of frustrating when you get specific reporting requirements for a customer, and none of the built in reports can provide the information, or they give perhaps too much information that is hard to filter.

This had me curious to different ways of querying the database directly. I know with Standalone Cisco Unified Intelligence Center, you can create custom reports, but I wanted to see how easily it could be done without having to install another server, and the associated licencing.

Getting Started

First there's a few prerequisites:
  1. Download the Informix ODBC Driver. Easy enough to find on Google and you can choose for which platform. I used "Informix Downloads (Informix Client SDK Developer Edition for Windows x86_64, 64-bit)"
  2. Install the Client SDK. This includes the ODBC Driver. There's another standalone executable, but this wouldn't install for me and had just a cryptic error message that was very unhelpful. Installing the whole SDK worked fine though.
  3. Create a DSN for CCX. Here I followed the guide Create an ODBC Connection to connect to Cisco UCCX server

    The built in user account is "uccxhruser", and the password for this can be set under CCX Admin > Tools > Password Management.

    To build my File string, I went to File DSN > Add and then specified the filename, and followed the guide above, and finally saved the result.

    You then get a file that looks something like this:
    PWD=EP 81 32 10 12 12 56 86 65 29384  0  0  0  0  0  0  0  0

  4.  Build the Configuration String to use in Python. Here you just want to take each line, and place it all on one line separated by semicolons ; and update the password to be in cleartext. Also it's important to note that anything with spaces in it needs to be included in {curly braces}. For me, my string then became:

    DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};UID=uccxhruser;PWD=Cisco123;DATABASE=db_cra;HOST=ccx.lab.local;SERVER=ccx_uccx;SERVICE=1504;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.CP1252;DB_LOCALE=en_US.57372

    Save this string as you'll need it later for the connection.

Connect to the Database and run Queries

Here I ran some standard boilerplate code I found for connecting to Informix Databases using the Python library pyodbc. This establishes a connection with the Database, and returns a cursor that you can then execute queries against. I also import datetime because I plan on using it in later queries.

On the connect command, completely in quotes, you'll enter in the Connection String you generated above.

import pyodbc
import datetime
conn = pyodbc.connect('DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};UID=uccxhruser;PWD=Cisco123;DATABASE=db_cra;HOST=ccx.lab.local;SERVER=ccx_uccx;SERVICE=1504;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.CP1252;DB_LOCALE=en_US.57372')
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
conn.setencoding(str, encoding='utf-8')
conn.setencoding(unicode, encoding='utf-8', ctype=pyodbc.SQL_CHAR)
cursor = conn.cursor()

Next as a sample query, I just wanted to see how many total calls were received within Business Hours. There's definitely a cleaner way to iterate over dates in Python (such as creating a generator and iterating over them). For simplicity I just did a simple for loop, incrementing the day in each iteration and saved the results.

Tip: Encase the SQL Queries in double quotes, and within the query use single quotes to avoid any syntax issues, and use the string format method so you're not breaking up the string with + variable1 +  etc throughout.

# Query all calls received during business hours for a particular month
start = datetime.datetime(2017, 3, 1)
# Initialise the Results list that will contain a dictionary with the day, followed by the queried results
results = []
for i in range(30):
    # Check if current date is a Weekday. 0-4 is Mon-Fri, 5-6 is Sat-Sun
    if start.weekday() < 5:
        # Set Start and End Times for SQL Query
        startTime = start.strftime("%Y-%m-%d 08:00:00")
        endTime = start.strftime("%Y-%m-%d 18:00:00")
        # Query SQL Database
        cursor.execute("select contactType, applicationName from ContactCallDetail where ContactCallDetail.startDateTime >= '{}' AND ContactCallDetail.endDateTime <= '{}'".format(startTime, endTime))
        rows = cursor.fetchall()
        # Append Results to list
        results.append({'Date' : startTime[:10], 'Calls' : rows})
    # Finally increment the day
    start += datetime.timedelta(days=1)

Printing the results, we can see just a simple date and number of calls, and these will only be for the business hours (8:00 to 18:00) specified in the query. As this is just in my test lab, there were many days with no calls at all.

[{'Date': '2017-03-01', 'Total Calls': 0},
 {'Date': '2017-03-02', 'Total Calls': 0},
 {'Date': '2017-03-03', 'Total Calls': 0},
 {'Date': '2017-03-06', 'Total Calls': 0},
 {'Date': '2017-03-07', 'Total Calls': 0},
 {'Date': '2017-03-08', 'Total Calls': 0},
 {'Date': '2017-03-09', 'Total Calls': 0},
 {'Date': '2017-03-10', 'Total Calls': 0},
 {'Date': '2017-03-13', 'Total Calls': 0},
 {'Date': '2017-03-14', 'Total Calls': 0},
 {'Date': '2017-03-15', 'Total Calls': 0},
 {'Date': '2017-03-16', 'Total Calls': 0},
 {'Date': '2017-03-17', 'Total Calls': 31},
 {'Date': '2017-03-20', 'Total Calls': 4},
 {'Date': '2017-03-21', 'Total Calls': 7},
 {'Date': '2017-03-22', 'Total Calls': 0},
 {'Date': '2017-03-23', 'Total Calls': 0},
 {'Date': '2017-03-24', 'Total Calls': 0},
 {'Date': '2017-03-27', 'Total Calls': 0},
 {'Date': '2017-03-28', 'Total Calls': 0},
 {'Date': '2017-03-29', 'Total Calls': 0},
 {'Date': '2017-03-30', 'Total Calls': 0}]

So that finishes up just a simple introduction. The queries can be as simple or as complicated as you need. To get a description of all the tables and fields available, you want to look for the Unified CCX DB Schema Guide for the corresponding version of UCCX. I'd also recommend doing some reading up on SQL commands, so that you can get more meaningful results (i.e. doing table joins between ContactCallDetail and Resource tables to get the "resourceID" reading the name or userID of the Agent answering calls, etc).

Previous Post Older Post Home


Post a Comment