12-12-2017 06:37 AM - edited 03-17-2019 11:46 AM
Looking for SQL query to find Jabber device which are not associated with end user
12-12-2017 10:24 AM - edited 12-12-2017 10:25 AM
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).
12-12-2017 11:41 PM
Thanks....however it show me CSF profile associated with end user, i am looking for CSF profile which are not associated with end user.,
12-12-2017 11:48 PM
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.
12-13-2017 12:33 AM
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.
12-13-2017 12:37 AM
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).
12-13-2017 05:41 AM
On Following web link
Which give information on
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide