UCCX – Queries using Python Script

Hey guys,

As promised in my post about ODBC Connection, (you can read it HERE), I’m going to show you how to create a basic Script using Python to query some information from UCCX, which can be useful to create some personalized Dashboards.

Even though we have many types of reports on CUIC, sometime they don’t meet our expectations by having too much unnecessary information or by lack of information.

I’ have decided to use Python, along with HTML, to create my own Dashboard. So I can have only information I know is 100% useful.

First of all, you have to create the ODBC connection to the server where you are going to place the script.
Again, you can use THIS POST to help you out.

Once you have the ODBC Connection working, it’s time to work on your script.

To be able to connect your script to your ODBC, you need to have a PYODBC python Library installed. To be able to better manipulate date and time, I’m also using datetime library.

The first part of the script is used to establish a connection to your ODBC. So you need to fill all its information in the connection strings. It’s important to mention that pyodbc does not even look at the connection string. It is passed directly to the database driver.

To start off my code, I’ll call the libraries and use the command conn = pyodbc.connect to connect to my ODBC.

image

Connection is now ready!
Now it’s time to choose a query to be sent. That query is sent using SQL commands.
This means you can use your SQL skills to play with queries and create interesting reports

Smile

In the below example, I wanted to know how many licenses are being consumed daily.
To do that, I’ll use the SQL command: ” {call sp_license_utilization(‘2021-05-05 00:00:01′,’2021-05-05 23:00:01′,’0′,’1’)}”. The line in the script will be like that:

cursor.execute(” {call sp_license_utilization(‘2021-05-05 00:00:01′,’2021-05-05 23:00:01′,’0′,’1’)}”)

If you print the result, you will see something like that:

[(datetime.datetime(2021, 4, 16, 0, 0, 1), 1, 0, 3), (datetime.datetime(2021, 4, 16, 1, 0, 1), 0, 0, 4), (datetime.datetime(2021, 4, 16, 2, 0, 1), 0, 0, 4), (datetime.datetime(2021, 4, 16, 3, 0, 1), 0, 0, 4), (datetime.datetime(2021, 4, 16, 4, 0, 1), 1, 0, 6), (datetime.datetime(2021, 4, 16, 5, 0, 1), 0, 0, 18), (datetime.datetime(2021, 4, 16, 6, 0, 1), 2, 0, 43), (datetime.datetime(2021, 4, 16, 7, 0, 1), 4, 0, 58), (datetime.datetime(2021, 4, 16, 8, 0, 1), 9, 0, 63), (datetime.datetime(2021, 4, 16, 9, 0, 1), 6, 0, 64), (datetime.datetime(2021, 4, 16, 10, 0, 1), 5, 0, 62), (datetime.datetime(2021, 4, 16, 11, 0, 1), 4, 0, 51), (datetime.datetime(2021, 4, 16, 12, 0, 1), 5, 0, 51), (datetime.datetime(2021, 4, 16, 13, 0, 1), 4, 0, 49), (datetime.datetime(2021, 4, 16, 14, 0, 1), 4, 0, 39), (datetime.datetime(2021, 4, 16, 15, 0, 1), 3, 0, 27), (datetime.datetime(2021, 4, 16, 16, 0, 1), 2, 0, 15), (datetime.datetime(2021, 4, 16, 17, 0, 1), 0, 0, 10), (datetime.datetime(2021, 4, 16, 18, 0, 1), 1, 0, 8), (datetime.datetime(2021, 4, 16, 19, 0, 1), 0, 0, 6), (datetime.datetime(2021, 4, 16, 20, 0, 1), 0, 0, 6), (datetime.datetime(2021, 4, 16, 21, 0, 1), 0, 0, 6), (datetime.datetime(2021, 4, 16, 22, 0, 1), 0, 0, 5), (datetime.datetime(2021, 4, 16, 23, 0, 1), 0, 0, 5)]

Then, use Python to manipulate the results according to your needs. In my case, I’m using the datetime to get today’s date. I also created a list to save the values, as this code will check the license each hour, and give me the maximum as a final result.

The full code for this sample is:

image

 

import pyodbc
from datetime import datetime

conn = pyodbc.connect(‘DRIVER={IBM INFORMIX ODBC DRIVER};’
‘UID=uccxhruser;PWD=123456;’
‘DATABASE=db_cra;’
‘HOST=uccxlab.com;’
‘SERVER=uccxlab_uccx;’
‘SERVICE=1504;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.UTF8;DB_LOCALE=en_US.UTF8’)
cursor = conn.cursor()

listItem = []
listLicCCX = []
timestampStr = datetime.now().strftime(“%Y-%m-%d”)

try:
cursor.execute(” {call sp_license_utilization(‘” + str(timestampStr) + ” 00:00:01′,'” + str(timestampStr) + ” 23:59:59′,’0′,’1’)}”)
rows = cursor.fetchall()
LicenseUsage = rows
for hourly in LicenseUsage:
if hourly[3] != None:
listItem.insert(0, hourly[3])
else:
listItem.insert(0, 0)
except pyodbc.Error as ex:
print(“An exception occurred”)
listItem.insert(0, 0)
listLicCCX.insert(0, (max(listItem)))

print(listLicCCX)

And this is the final result:

image

Remember you can use any SQL Query!

For example, this is the SQL query to get a list of Agents by Team:select s.resourceLoginID,s.resourceFirstName,s.resourceLastName,s.extension, t.teamname from Resource s inner join team t on s.assignedTeamID = t.teamid where s.active = ‘t’ and t.active = ‘t’ and t.teamname = ‘UCCX_TEAM’ order by t.teamname, s.resourceloginid

Using a simple Select * from rtcsqssummary here csqname = ‘<CSQ Name>’ query you can display more information as this query will return the following information.

csqname
loggedinagents
availableagents
unavailableagents
totalcalls
oldestcontact
callshandled
callsabandonded
callsdequeued
avgtalkduration
avgwaitduration
longesttalkduration
longestwaitduration
callswaiting
enddatetime
workingagents
talkingagents
reservedagents
startdatetime
convavgtalkduration
convavgwaitduration
convlongestwaitduration
convlongestwaitduration
convoldestcontact

The sky is the limit!

Smile

Now that you now how to use SQL queries in Python, you can start creating your own script!

Enjoy!

Bruno

Cisco Finesse – Disconnection Problems

Hi everybody,

Today,  I’m going to give you a troubleshooting tip about an issue I’ve been facing, on Cisco Finesse.

Agents started complaining that they suddenly get disconnected, and when you see the reports on CUIC, the reason is Connection Failure.

For this case, we are using Cisco UCCX 11.6.2.

First of all, we have to check the Layer 1. Make sure the phone is not losing connection due a cabling faulty.
If you are using Jabber, make sure you network connection is stable, and if it’s VPN, your internet is stable.
Voice traffic is really sensitive, so any minimum interruption can cause a disconnection.

Another thing Cisco recommends is, if your agent has Deskphone and Jabber configured with the same line (but not using at the same time, as UCCX does not support shared lines), you have to keep only one added to the End user and to the Application user. If you have both, it work, but you will have that disconnection some times as well (yes, I faced that in the past).

Now, the latest I’ve heard from them!

As per this Troubleshooting, these presence driven logouts occur when UCCX does not receive presence available status from the agent PC/browser.  The system logs the agent out after 60 seconds.

So, seen all this points, there are 2 more difficult things to be caught, and I recently came across.

  1. Browser.
    Chrome v88+ and Edge are known to cause these issues.

    For agents logged out with the tab minimized/backgrounded:Disable Automatic Tab Discarding:
    For versions 75 and above: Add chrome extension ‘Disable automatic tab discarding’https://chrome.google.com/webstore/detail/disable-automatic-tab-dis/dnhngfnfolbmhgealdpolmhimnoliiok
  2. IntensiveWakeUpThrottlingEnabled Starting with Chrome 88: Improved resource consumption for background tabs To save on CPU load and prolong battery life, Chrome will limit the power consumption of background tabs. Specifically, Chrome will allow the timers in the background tabs to only run once per minute. If agents are using Chrome v88+, navigate to “chrome://flags” in the agent Chrome browser, search the above flag and ensure it is disabled (default=enabled).

  3. Network LatencyOne of the Finesse requirements is the that the Network Latency cannot be higher than 400ms.
    And that was exactly the problem I found on my network!!!

    But how do we find out that the latency is going over 400ms??

    Here are the instructions to gather the clientlogs from the agent Desktop side,

      *   Clear browser cache
      *   Load the following URL: <protocol>://<ip/host>:<port>/desktop/locallog and select “Sign In With Persistent Logging“.  You will be redirected to login page with the appropriate query parameter url.
      *   Sign into Finesse
      *   Operate Finesse as usual
      *   When you run into the problem open a new window or a tab and reopen with same browser type using the following URL: <protocol>://<ip/host>:<port>/desktop/locallog and select Refresh button
      *   Now you have all the logs in the contents of the console output.

  4. Conclusion


    After analysing the logs, I could find the following:

    Line 384: 2021-03-29T09:28:50.812 +02:00: 39DED1: <a href="http://<http://<<uccx_server>&gt;: Mar 29 2021 09:28:50.728 +0200: Header : Client: 2021-03-29T07:28:50.518Z, Server: 2021-03-29T07:28:50.434Z, Drift: -84ms, Network Latency (round trip): 587ms

    image

    In this Log’s pieces, we can see that the roundtrip latency for the agent that was logged out, spikes above the 400ms threshold allowed by Finesse. This latency means that the server does not receive the “Presence available” notifications from the agent PC.  After 60 seconds without receiving a notification, the system will log the agent out per design.              

  5. So now you have to troubleshoot your network to find the source of that Latency.

    That’s it guys!

    I hope this post can help you out!

    See ya!

    Bruno

    Creating an ODBC connection with UCCX

    Hey everybody,

    Today  I want to show you how to create an ODBC connection with UCCX, which can be mainly used for Wallboards/Dashboards.

    To be able to access and pull information out from its Database, UCCX already provides some users with some specifics rights. They are:

    • uccxwallboard: Has access to real-time database tables that contain snapshots of real-time statistics, more specifically to tables RTCSQsSummary and RTICDStatistics.
    • uccxhruser:  Has access to many configuration and historical tables in the UCCX database. It can only be used for custom historical reporting and Cisco Unified Workforce Management (WFM).
    • uccxworkforce: Has access to the Team, Resource, and Supervisor tables and it’s used for Cisco Unified Quality Management (QM).

    For this connection we are creating now, we are going to use uccxhruser.
    Let’s start off going to UCCX >> Tools >> Password Management to set a new password (in case you don’t know)

    image

    Once done, let’s now focus on the ODCB configuration!

    First of all, you need to download the IBM Informix Client SDK. It will allow the client to stablish a ODBC connection with your Database on UCCX.
    You can download the SDK here.

    • Go to Control Panel >> Administrative Tools >> ODBC Data Sources (64-bit).
    • Chose the tab System DNS and hit Add.

    image

    • You see now a list of available data sources. Select IBM INFORMIX ODBC DRIVER. Click Finish.
    • Time to create a new DNS. In the Tab General, give a name to your connection.

    • Go to the Tab Environment, and fill the information following the rules below:
      • Server Name: Instance name of Informix server of the set up.  Please pay attention because there are tricky rules for entering the name such as:
        1. Name MUST be in lower case (even if your server name are in upper case)
        2. Any hyphens MUST be converted to underscore
        3. If your server name begins with a number, add “i” in front of it.
        4. Add “_uccx” to the end of the hostname.

      For example: If your server name is 1-EMEA-UCCX, you have to enter i1_emea_uccx_uccx

      • Hostname: The actual hostname or IP address of the UCCX
      • Service: 1504
      • Protocol: onsoctcp
      • Options: leave it blank
      • Database name: db_cra
      • UserID: uccxhruser
      • Password: Password of uccxhruser

      image

      • Go now to the Tab Environment.

            Fields Client Locale and Database Locale must be as: en_US.UTF8

      3

      • Go back to the Tab Connection. It’s now time to test if it’s working.

      Click on Apply and Test Connection. You must see that message:

      6

      That’s it for now! I hope this post can be useful !!

      See ya!!