SQL query to find associated Jabber device with end user

[dhirajachhra]

Looking for SQL query to find Jabber device which are not associated  with end user

[Georgios Fotiadis]

Run this on CUCM CLI:

run sql select eu.userid, d.name, d.tkmodel as DN, rp.name as partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join enduserdevicemap as eudm on eudm.fkdevice=d.pkid inner join enduser as eu on eudm.fkenduser=eu.pkid inner join numplan as n on dnpm.fknumplan = n.pkid inner join routepartition as rp on n.fkroutepartition=rp.pkid and d.tkclass = 1

tk.model 503 is Jabber Devices (Cisco Unified Client Services Framework).

[dhirajachhra]

Thanks....however it show me CSF profile associated with end user, i am looking for CSF profile which are not associated with end user., 

[Georgios Fotiadis]

This will show you the complete list of configured devices and associated DNs:

run sql select d.name, d.description, n.dnorpattern as DN from device as d, numplan as n, devicenumplanmap as dnpm where dnpm.fkdevice = d.pkid and dnpm.fknumplan = n.pkid and d.tkclass = 1

or even if you just need configured devices (Regardless of associated DNs):

run sql select name from device

Use some tool to compare and find the ones not assigned to any user.

[dhirajachhra]

Intention of the requirement is to remove Jabber phone which are not in use.

Since we have LDAP synch on CUCM, once user is deleted, CSF profile will remain

Hence need SQL query to find jabber phone which are not associated with end user.

[Georgios Fotiadis]

But I have answered to you. Get the list of devices which are associated to some user, then get the complete list of devices and compare them (using the MAC address).

[dhirajachhra]

On Following web link 

https://www.cisco.com/c/en/us/support/docs/unified-communications/unified-communications-manager-callmanager/117726-technote-cucm-00.html#anc10

Which give information on 

Find Phones that Do Not have a Line-Level User Association

But SQL query is not giving desired result, thought might be something i can get help from experts :-)

run sql select d.name, d.description, n.dnorpattern as DN, rp.name as partition
from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid
inner join devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap!=
dnpm.pkid inner join numplan as n on dnpm.fknumplan = n.pkid inner join
routepartition as rp on n.fkroutepartition=rp.pkid and d.tkclass = 1