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 serverThe 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:

   
   [ODBC]
   DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)}
   UID=uccxhruser
   PWD=EP 81 32 10 12 12 56 86 65 29384  0  0  0  0  0  0  0  0
   DATABASE=db_cra
   HOST=ccx.lab.local
   SERVER=ccx_uccx
   SERVICE=1504
   PROTOCOL=onsoctcp
   CLIENT_LOCALE=en_US.CP1252
   DB_LOCALE=en_US.57372
   
   

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.

print(results)
 
[{'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).