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:


As for SQL queries in Call Manager, check these out:

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.

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”

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 <>.
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.


  • 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 <>.

    FTP server on Debian

    In this page, I will show you how to setup a basic FTP server in Debian. We will be using Pure-FTPd.

    Before install, check for existing FTP services.
    ps aux | grep ftp

    Check if you have an existing FTP server already installed (and not running?). Look for those lines that begins with “i”; that means the package is already installed. The “p” flag means it is a package that you can install.

    root@ftp-server:~# aptitude search pure-ftp
    p   mysqmail-pure-ftpd-logger                                                                  - real-time logging system in MySQL - Pure-FTPd traffic-logger
    p   pure-ftpd                                                                                  - Secure and efficient FTP server
    p   pure-ftpd-common                                                                           - Pure-FTPd FTP server (Common Files)
    p   pure-ftpd-ldap                                                                             - Secure and efficient FTP server with LDAP user authentication
    p   pure-ftpd-mysql                                                                            - Secure and efficient FTP server with MySQL user authentication
    p   pure-ftpd-postgresql                                                                       - Secure and efficient FTP server with PostgreSQL user authentication

    Install Pure-FTPd server with aptitude install pure-ftpd. The install looks like this.

    root@ftp-server:~# aptitude install pure-ftpd
    The following NEW packages will be installed:
      openbsd-inetd{a} pure-ftpd pure-ftpd-common{a}
    0 packages upgraded, 3 newly installed, 0 to remove and 0 not upgraded.
    Need to get 432 kB of archives. After unpacking 999 kB will be used.
    Do you want to continue? [Y/n/?] Y
    Get: 1 http://ftp.us.debian.org/debian/ wheezy/main openbsd-inetd amd64 0.20091229-2 [38.1 kB]
    Get: 2 http://ftp.us.debian.org/debian/ wheezy/main pure-ftpd-common all 1.0.36-1.1 [185 kB]
    Get: 3 http://ftp.us.debian.org/debian/ wheezy/main pure-ftpd amd64 1.0.36-1.1 [209 kB]
    Fetched 432 kB in 0s (472 kB/s)
    Preconfiguring packages ...
    Selecting previously unselected package openbsd-inetd.
    (Reading database ... 38866 files and directories currently installed.)
    Unpacking openbsd-inetd (from .../openbsd-inetd_0.20091229-2_amd64.deb) ...
    Selecting previously unselected package pure-ftpd-common.
    Unpacking pure-ftpd-common (from .../pure-ftpd-common_1.0.36-1.1_all.deb) ...
    Selecting previously unselected package pure-ftpd.
    Unpacking pure-ftpd (from .../pure-ftpd_1.0.36-1.1_amd64.deb) ...
    Processing triggers for man-db ...
    Setting up openbsd-inetd (0.20091229-2) ...
    [ ok ] Stopping internet superserver: inetd.
    [info] Not starting internet superserver: no services enabled.
    Setting up pure-ftpd-common (1.0.36-1.1) ...
    Setting up pure-ftpd (1.0.36-1.1) ...
    Starting ftp server: Running: /usr/sbin/pure-ftpd -l pam -O clf:/var/log/pure-ftpd/transfer.log -u 1000 -E -8 UTF-8 -B

    Create a new system group for pure-ftpd.

    groupadd ftpgroup

    Create a default FTP user that has no access to home directories and cannot drop into a shell. You will not be prompted to create a password for this user.

    useradd -g ftpgroup -d /dev/null -s /etc ftpuser

    Create FTP users. You will prompted to create a new password for this user as well. The following example is an existing user and having it point to his existing home directory.

    pure-pw useradd elton -u ftpuser -g ftpgroup -d /home/elton

    You can create FTP users with storage limits. For more options check out the pure-pw man page, command is man pure-pw.

    pure-pw useradd bill -u ftpuser -g ftpgroup -d /home/pubftp/remo -N 10

    If you’re like me, you can sometimes create passwords on the fly and right away forget. I mean, it is faster for me to randomly come up with complex passwords and not have to use a password generator. Anyway, If you need to change it-

    pure-pw passwd [username]

    A reminder that pure-pw passwd is only for changing the FTP password. You still need to use passwd [username] to change users password.

    To apply adds and changes with pure-ftpd, don’t forget to issue the command pure-pw mkdb. The version of pure-ftpd that I have, version 1.0.36-1.1 does not need pure-pw mkdb after adding a new user.

    User info are stored in the /etc/pure-ftpd/pureftpd.passwd database file. Instead of checking that file, you can also list users with

    pure-pw list

    If you are looking for info on one specific user, pure-pw show [username]

    Here’s an example.

    root@ftp-server:~# pure-pw show elton
    Login              : elton
    Password           : $1$pVSkjNe0$OVr6W4ArAcFTxsXWa8OGR1
    UID                : 1001 (ftpuser)
    GID                : 1001 (ftpgroup)
    Directory          : /home/elton/./
    Full name          :
    Download bandwidth : 0 Kb (unlimited)
    Upload   bandwidth : 0 Kb (unlimited)
    Max files          : 0 (unlimited)
    Max size           : 0 Mb (unlimited)
    Ratio              : 0:0 (unlimited:unlimited)
    Allowed local  IPs :
    Denied  local  IPs :
    Allowed client IPs :
    Denied  client IPs :
    Time restrictions  : 0000-0000 (unlimited)
    Max sim sessions   : 0 (unlimited)

    Notice /home/elton/./ in the Directory value. The ./ after the directory path means that chroot will prevent this user from going above or outside that directory. This will make sense when you test FTP login using a FTP client.

    To save time, you can test your FTP login and server with


    Better still, get FileZilla FTP Client.

    To start, stop, restart, force-reload, and view status of pure-ftpd, begin your command with /etc/init.d/pure-ftpd

    root@ftp-server:~# /etc/init.d/pure-ftpd
    Usage: /etc/init.d/pure-ftpd {start|stop|restart|force-reload|status}

    Remember that SFTP and FTPS are not the same. SFTP basically is FTP using SSH while FTPS uses TLS. This means that if you’ve enabled SSH on the server (and for the user), then SFTP (port 22) will work as well.

    Firefox 39.0 SSL Error: Weak Ephemeral Diffie-Hellman key

    Recently updated Firefox to version 39.0 and tried to access Call Manager and Contact Center Express. Got the following Firefox error:

    SSL received a weak ephemeral Diffie-Hellman key in Server Key Exchange handshake message. (Error code: ssl_error_weak_server_ephemeral_dh_key)

    Solution documented in https://support.mozilla.org/en-US/questions/1066238

    Explanation: http://arstechnica.com/security/2015/05/https-crippling-attack-threatens-tens-of-thousands-of-web-and-mail-servers/

    1. In Firefox, Enter “about:config” in the URL field and press enter.
    2. Accept the “This might void your warranty!” warning by clicking “I’ll be careful, I promise!” button.
    3. In the search field, enter “security.ssl3.dhe_rsa_aes”.
    4. Double click each result (128 SHA and 256 SHA) to toggle the Value to “false”.

    No need to close and reopen the browser.

    Cisco IOS Configuration Change Logging

    Someone or something caused your router to reboot. You want to log configuration changes to rule that out without using TACACS. Here’s what it looks like.

    router(config)# archive
    router(config-archive)# log config
    router(config-archive-log-cfg)# logging enable
    router(config-archive-log-cfg)# logging size 1000
    router(config-archive-log-cfg)# notify syslog

    To display configuration log entries by record numbers starting with the first recorded command
    show archive log config 1

    To display all configuration log files as they would appear in a configuration file rather than in a tabular format
    show archive log config all provisioning

    To view statistics
    show archive log config statistics

    router# show archive log config statistics
    Config Log Session Info:
            Number of sessions being tracked: 1
            Memory being held: 3909 bytes
            Total memory allocated for session tracking: 187657 bytes
            Total memory freed from session tracking: 183748 bytes
    Config Log log-queue Info:
            Number of entries in the log-queue: 63
            Memory being held by the log-queue: 16356 bytes
            Total memory allocated for log entries: 16356 bytes
            Total memory freed from log entries: 0 bytes

    For more information: http://www.cisco.com/c/en/us/td/docs/ios/fundamentals/configuration/guide/15_1s/cf_15_1s_book/cf_config-logger.html

    Written by Comments Off on Cisco IOS Configuration Change Logging Posted in Cisco, IOS

    Configure SSH v2 in Cisco IOS

    Set the device’s hostname
    hostname hercules

    Set the device’s membership to a domain. Generating an RSA key requires a domain name.
    ip domain-name routers.eltonoverip.com

    Check to see if SSH is already running
    show ip ssh

    Generate an RSA key
    crypto key generate rsa

    You will get something like the following:

    hercules(config)#crypto key generate rsa
    The name for the keys will be hercules.routers.eltonoverip.com
    Choose the size of key modules in the range of 360 to 4096 for your
    General Purpose Keys.  Choosing a key modulus greater than 512 may take a few minutes
    How many bits in the modulus [512]: 2048
    %Generating 2048 bit RSA keys, keys will be non-exportable...
    [OK] (elapsed time was 0 seconds)

    If you skipped the ip domain-name whateverdomain.com, you will get the following:
    % Please define a domain-name first.

    Or you could do a more specific command
    crypto key generate rsa general-keys modulus 2048

    hercules(config)#crypto key generate rsa general-keys modulus 2048
    The name for the keys will be: hercules.eltonoverip.com
    % The key modulus size is 2048 bits
    % Generating 2048 bit RSA keys, keys will be non-exportable...[OK]
    *Apr 12 05:12:36.775: %SSH-5-ENABLED: SSH 2.0 has been enabled

    At this point, when you check the output of show ip ssh and it shows version 1.99, that means that it is supports or run both versions 1 and 2. Note that 1.99 is not an actual version but a method to identify backwards compatibility.


    To run only version 2
    ip ssh version 2

    Set up a local user
    username elton privilege 15 secret cisco

    Few more commands

    line vty 0 4
      login local

    Allow only SSH
    transport input ssh

    Allow both SSH and telnet
    transport input ssh telnet

    Few ways to verify

    Check if the SSH service is running on the device
    show ip ssh

    Check who are logged in

    Check the SSH port
    show control-plane host open-ports

    hercules#show control-plane host open-ports
    Active internet connections (servers and established)
    Prot               Local Address             Foreign Address                  Service    State
     tcp                        *:22                         *:0               SSH-Server   LISTEN
     tcp                        *:23                         *:0                   Telnet   LISTEN

    Check active TCP sessions. These are not TCP traffic through the router but those terminated at this router.
    show tcp

    hercules#show tcp
    tty194, virtual tty from host Achilles
    Connection state is ESTAB, I/O status: 1, unread input bytes: 0
    Connection is ECN Disabled, Mininum incoming TTL 0, Outgoing TTL 255
    Local host:, Local port: 22
    Foreign host:, Foreign port: 2326
    Connection tableid (VRF): 0
    Enqueued packets for retransmit: 0, input: 0  mis-ordered: 0 (0 bytes)
    Event Timers (current time is 0x1838E4):
    Timer          Starts    Wakeups            Next
    Retrans            13          0             0x0
    TimeWait            0          0             0x0
    AckHold             9          0             0x0
    SendWnd             0          0             0x0
    KeepAlive           0          0             0x0
    GiveUp              0          0             0x0
    PmtuAger            0          0             0x0
    DeadWait            0          0             0x0
    Linger              0          0             0x0
    ProcessQ            0          0             0x0
    iss: 1184651233  snduna: 1184653125  sndnxt: 1184653125     sndwnd:  17104
    irs: 3330383746  rcvnxt: 3330385707  rcvwnd:       3800  delrcvwnd:    328
    SRTT: 247 ms, RTTO: 663 ms, RTV: 416 ms, KRTT: 0 ms
    minRTT: 4 ms, maxRTT: 300 ms, ACK hold: 200 ms
    Status Flags: passive open, active open
    Option Flags: 0x1000000
    IP Precedence value : 6
    TCB is waiting for TCP Process (55)
    Datagrams (max data segment is 1460 bytes):
    Rcvd: 20 (out of order: 0), with data: 12, total data bytes: 1960
    Sent: 17 (retransmit: 0, fastretransmit: 0, partialack: 0, Second Congestion: 0), with data: 14, total data bytes: 1891
     Packets received in fast path: 0, fast processed: 0, slow path: 0
     fast lock acquisition failures: 0, slow path: 0
    Written by Comments Off on Configure SSH v2 in Cisco IOS Posted in Cisco, IOS

    Cleaning your Apt

    My 16 GB Ubuntu partition has ran of out space. Disk usage analyzer claims that /var/cache/apt is using 669 MB. Time to clean it up my apt with apt-get clean! Basically, apt-get clean removes .deb packages that apt caches when you install or update programs.

    elton@laptop:/var/cache$ du -hs
     du: cannot read directory `./ldconfig': Permission denied
     du: cannot read directory `./lightdm/dmrc': Permission denied
     743M .
     elton@laptop:/var/cache$ sudo !!
     sudo du -hs
     743M .
     elton@laptop:/var/cache$ sudo apt-get clean
     elton@laptop:/var/cache$ sudo du -hs

    A few other options:

    apt-get autoclean
    to remove partial packages from the system
    apt-get autoremove to remove packages installed as dependencies after the original package is removed

    SNMPd in Ubuntu/Debian

    Install the SNMP daemon.

    aptitude install snmpd

    To check which version of SNMP daemon that was installed, run the following

    aptitude show snmpd

    Make a backup of the original SNMP daemon configuration file.

    cp /etc/snmp/snmpd.conf /etc/snmp/snmpd.conf_ORIGINAL

    Modify the SNMP daemon configuration file

    vim /etc/snmp/snmpd.conf

    The above command should also create the same file, if the configuration file does not exist. Append the following lines. Adjust the values to the SNMP community string that you use. This assumes SNMP version 2

    rocommunity public
    syslocation "Your Location"
    syscontact admin@domain.com

    Modify the /etc/default/snmpd file. Duplicate the the following line then uncomment it (the original line). You always want to make a copy of the original line.

    SNMPDOPTS='-Lsd -Lf /dev/null -u snmp -I -smux -p /var/run/snmpd.pid'

    Append the following to the duplicate line. Basically, point to the SNMP configuration file.

    -c /etc/snmp/snmpd.conf'

    It should look like the following:

    SNMPDOPTS='-Lsd -Lf /dev/null -u snmp -I -smux -p /var/run/snmpd.pid -c /etc/snmp/snmpd.conf'

    Two ways to restart the snmpd service

    service restart snmpd


    /etc/init.d/snmpd restart

    While you poll the machine, run this on the server to check the status

    tcpdump -i eth0 "src or dst [ip address of SNMP polling server]"

    Check loaded Jabber XML configuration

    Simply browse to http://CUCM-IP-address:6970/jabber-config.xml

    Remember that TFTP files are not configuration changes to the configuration database replicated by the publisher to the subscriber. Likely, your publisher or the server you are checking is not a TFTP server. You need to upload jabber-config.xml to all TFTP servers.

    If it does exist when you check in OS Administration > Software Upgrades > TFTP File Management, then you need to restart Cisco Tftp for the change to take effect.

    Check the installation and configuration guide for more information: