Category: Contact Center

UCCX Basic SQL Query

Most of the things that matter daily in UCCX 8.x, 9.x and perhaps 10.x are mostly done in CCX Administration (appadmin) and some troubleshooting done CCX Editor. There is one thing that Report Summary and User View did not do for me: List all users, along with their login ID‘s, and IPCC extensions so I can inform the Windows server team which users belong in the call center.

My solution was to do a SQL query. For those of you who are familiar with MySQL syntax, you will like this or roll your eyes on how easy it is and how unnecessary this post would be. My problem was that I did not know the name of the database and tables the agent info are stored.

See that * in the query below? Don’t do that if you have a lot of agents.


run uccx sql db_cra select * from resource

Or do it anyway to find out the names of the table columns so you can narrow down your search; but do it for one user.


run uccx sql db_cra select * from resource where resourceloginid like 'elton'

Be specific with the table columns instead.


run uccx sql db_cra select resourceloginid,extension,resourcename from resource where active!='f'

I use PuTTY and automatically log all session output so I don’t have to selectively highlight text to copy. Open the directory where you store the logs, open in Notepad++, copy, paste in Excel or Calc.

This is based on the UCCX 9.0 CLI Reference Guide:
http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/crs/express_9_0/configuration/guide/UCCX_BK_C940F879_00_cli-reference-guide-for-uccx/UCCX_BK_C940F879_00_cli-reference-guide-for-uccx_chapter_00.html#UCCX_TP_CFB67B78_00

http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cust_contact/contact_center/crs/express_9_02/configuration/guide/UCCX_BK_C007B99C_00_cli-reference-guide-release-902/UCCX_BK_C54B77AG_00_cli-reference-guide-902_chapter_0100.html#UCCX_TP_R1EEC0AE_00

As for SQL queries in Call Manager, check these out:
http://www.cisco.com/c/en/us/support/docs/unified-communications/unified-communications-manager-callmanager/117726-technote-cucm-00.html

Here’s a SQL user lookup example equivalent to viewing info in User Management > End Users


run sql select* from enduser where userid='elton'

William Bell has excellent CUCM SQL query tutorials as well. Don’t forget to check out the comment threads.
http://www.ucguerrilla.com/search/label/Query_Series

Basically, still using standard SQL syntax (yeah, that sounds redundant) prepended by “run sql” but it is just a matter of finding out the table and column names.

Here’s a SQL query to view them.


run sql select x.tabname, y.colname from systables x, syscolumns y where x.tabid=y.tabid order by x.tabname

Cisco Supervisor Desktop “No Service”

Symptoms:
CUCM 9.1
UCCX 9.0.2

A user called in to report that after logging in to Cisco Supervisor Desktop, selected a Team from the Team dropdown selection, it took a long time to load. It continues to state “No Service” in the bottom of the window after nothing has loaded and Team dropdown selection is greyed out.

If you download the Cisco Desktop Call/Chat Service trace logs, you will notice messages like:


2015-07-24 03:30:36:440 INFO STD0005 Client  disconnected from service at <10.0.11.39>.
2015-07-24 03:30:36:443 WARN LC0001 Error occurred while performing an LDAP operation.
2015-07-24 03:30:36:444 WARN LC0001 Error occurred while performing an LDAP operation.
2015-07-25 12:47:19:520 WARN FCCS3008 Network communication error  sending message to application .  The application will be logged out.
2015-07-27 08:44:34:074 INFO LC0007 Invalid Value.
2015-07-27 08:44:34:082 INFO LC0007 Invalid Value.


Solution:

  • Restart Cisco Desktop Call/Chat Service. This does not drop calls because once a call is answered, the CCX port is no longer used in that call. RTP media is now between endpoints. This does disrupt and refresh screens on agents who are exchanging chat messages but that shouldn’t be an issue, in my opinion.
  • Log out and back in.
  • If restarting the Cisco Desktop Call/Chat Service, did not work for you, restart Cisco Desktop Sync Service and Cisco Desktop Browser and IP Phone Agent Service.
  • Issue confirmed fixed. Notice, after selecting a team and while it is loading, the first thing that populates are the team’s agents and supervisors.

    What you want to see in the logs will be something like this

    
    2015-07-27 08:44:52:057 INFO FCCS0027 Service going into active mode.  Incoming requests will be accepted.
    2015-07-27 08:44:52:074 INFO LRMS0004 LRMClient is connected to the service at <10.0.11.39>.