Home Cluster Check via Sql

There may be many reasons why you would need to check which users have the home cluster enabled on a given CUCM cluster. In my personal experience, the most common reason is to troubleshoot an issue with people being unable to log into Cisco Jabber due to a “Unable to Communicate with Server” error message. Now, many things could cause issues with Jabber’s service discovery and authentication process (e.g. issues with DNS, missing discovery host records, firewalls blocking traffic, issues with the LDAP authorization, etc.). However, in my personal experience, it usually tends to be an issue with the home cluster being enabled on multiple clusters at once or the home cluster not being enabled on the target cluster.

Luckily, it’s very straightforward to get the status of the home cluster setting (islocaluser) from the enduser table. In this example, we will also include the service profile’s name in the result. To make sure we can uniquely identify a user, we will include the username, first name, and last name, too.

Home cluster for all users #

Relevant data, attributes, tables #

Tables #

Attributes #

Conditions #

Query #

Use the following query to get a report of all users who have the home cluster setting enabled.

SELECT 
  eu.userid AS id, 
  eu.firstname AS first_name, 
  eu.lastname AS last_name, 
  eu.islocaluser AS homecluster, 
  ucp.name AS serviceprofile 
FROM 
  enduser AS eu 
  LEFT JOIN ucserviceprofile AS ucp ON ucp.pkid = eu.fkucserviceprofile 
WHERE 
  eu.islocaluser = 't' 
ORDER BY 
  eu.userid

Return #

id 	      first_name last_name homecluster serviceprofile 
========= ========== ========= =========== ==============
jdoe1     Jane       Doe       t           UCSP_STANDARD 
jdoe2     John       Doe       t           UCSP_STANDARD 
HBarbossa Hector     Barbossa  t           UCSP_SPECIAL
...

Home cluster for a specific user #

I modified the first query by adding the and eu.userid=’user1′ condition to limit our output to just the one user we want. We don’t want to pull a full report for all users; we just want a quick and easy way to verify the setting against a single problematic user.

Relevant data, attributes, tables #

Tables #

Attributes #

Conditions #

Query #

Use the following query to get a report of all users who have the home cluster setting enabled.

SELECT 
  eu.userid AS id, 
  eu.firstname AS first_name, 
  eu.lastname AS last_name, 
  eu.islocaluser AS homecluster, 
  ucp.name AS serviceprofile 
FROM 
  enduser AS eu 
  LEFT JOIN ucserviceprofile AS ucp ON ucp.pkid = eu.fkucserviceprofile 
WHERE 
  eu.islocaluser = 't' 
  AND eu.userid = 'jdoe1'
ORDER BY 
  eu.userid

Return #

id 	    first_name last_name homecluster serviceprofile 
======= ========== ========= =========== ==============
jdoe1   Jane       Doe       t           UCSP_STANDARD 
...

Case-insensitive query #

An easy way to avoid the case sensitivity issue is to turn the userid and the search string into uppercase or lowercase before comparing them.

SELECT 
  eu.userid AS id, 
  eu.firstname AS first_name, 
  eu.lastname AS last_name, 
  eu.islocaluser AS homecluster, 
  ucp.name AS serviceprofile 
FROM 
  enduser AS eu 
  LEFT JOIN ucserviceprofile AS ucp ON ucp.pkid = eu.fkucserviceprofile 
WHERE 
  eu.islocaluser = 't' 
  AND UPPER(eu.userid) = UPPER('hbarbossa')
ORDER BY 
  eu.userid

Return #

id 	      first_name last_name homecluster serviceprofile 
========= ========== ========= =========== ==============
HBarbossa Hector     Barbossa  t           UCSP_SPECIAL
...