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

Filed under: Call Manager, Cisco, Contact Center