CUCM SQL query for TCT or BOT or CSF with no user Assigned

[simon hester]

Hi

I'm trying to find a an SQL query that will show a list of TCT, BOT and CSF, that do not have an active user assigned to them? Essentially old devices that be deleted

We have in the past used this query which was also found on the forum but this is for Extension Mobility Profiles with no user assigned. 
run sql select d.name from device d where (0=(select count(edm.pkid) from enduserdevicemap edm where edm.fkdevice=d.pkid)) and d.tkclass=254 order by d.name

Could this SQL be updated to cover TCT BOTs or CSF's

I have consider using a device export and excel but according to my customer who needs it it not giving them what they need. 

Thanks

Simon

[Nithin Eluvathingal]

Have a look on below thread. 

https://community.cisco.com/t5/ip-telephony-and-phones/sql-query-to-find-associated-jabber-device-with-end-user/td-p/3230858

[simon hester]

HI I had seen this before and it not quite what i need it would be perfect if you can do a not statement. 

I'm trying to find a list of TCT Bot and CFS device that can be deleted as the user they were linked to has been deleted. 

[Adam Pawlowski]

I use: 

run sql ccm select name from device where fkenduser is null and tkclass = 1 and tkproduct in ("462", "449", "537" , "390")

I cannot guarantee that is valid for your environment, that this will not include templates or other occurrences that don't have some other meaning in your system. You can use the name value if you want to pull data associated with devicenumplanmap or some other table as well.

[simon hester]

This looks promising I have tested and it show the TCT BOT and CSF devices but also shows ones where the device still has an active user under user management. 

Essentially the customer removed a person there AD and this removes the users from CUCM as they sync to AD however it doe snot clean up all the devices that may have been set up for the user. Perhaps there is another way to look at it not sure CUCM SQL is very new to me. 

If there a direct link between an TCT BOT or CSF device and the user under user management? I can't see a direct link the GUI config pages. For example a user who has left still has a BOT device BOTMARKSHAW6345 the device is still there there is also still a DN assigned with his name but there is no longer a user called Mark Shaw under user management. 

[Adam Pawlowski]

Be mindful that fkenduser from the device table is the Owner ID field. End user device assignment is different. The only way for the owner ID to be null but the end user to be assigned is not provisioning completely. In that case, the query would fail. If the owner ID is assigned by your provisioning process, then yes if the account left the end user would be null.
If you don't set this field you should, it will ensure licensing is compliant and you'll make your life easier.

[simon hester]

Adam,

Will a TCT BOT or CSF device work without the User ID set.  Or will it still work if there is end user assigned?

Thanks

Simon 

[Adam Pawlowski]

Yes sir it does, unfortunately. Doing some learning this morning to test what I've told you, and I've learned more. Specifically with the TCT as I didn't test other types:

If the userID is set as the owner ID, or if the device is associated with the end user, it appears discoverable and the client will register.

The simple query I posted lists devices which have no owner ID assigned, which may or may not work properly in your environment I guess based on provisioning. I've also learned some of our devices are not provisioned correctly either.

As far as i know, anything relating to CTI control, which I don't believe TCT/BOT supports anyway, requires end user assignment, but we can see how far my beliefs have taken me so far in this thread

This mess works for me to list devices which do not have an Owner ID and are not associated with an end user:

run sql select d.name, n.dnorpattern as dn, rp.name as partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.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 inner join device as d2 on d.pkid = d2.pkid and d2.fkenduser is null where d.pkid not in (select fkdevice from enduserdevicemap) and d.tkproduct in ('462', '449', '537', '390') 

You can refer to the earlier query to find devices with just no Owner ID, or, remove the d2 join to focus only on the devices that have no end user association, if you want to separate the two things. You would do that if you wanted to ensure that both are configured, and want to find devices that need remediation.

[VON CLAWSON]

If you look a little further in the thread it gives you your answer. Check out the URL

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

In there it states this:

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

Here is a query that is designed to find phones that do not have a line-level user association.

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

name            description                  dn   partition
=============== ============================ ==== ===========
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT
SEP503DE57D7DAC 8501                         8501 Internal_PT
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT

This query only provides information when the DN is in a partition. In order to include the ones in None Partition, enter:

run sql select d.name, d.description, n.dnorpattern as DN 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 and d.tkclass = 1

The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. For IP Phone Model 7945, add  and d.tkmodel='435' to the end of the query.

In order to obtain the tkmodel value for all IP Phone Models, enter:

run sql select name,tkmodel from TypeProduct

[simon hester]

I have tried a few version of this and still get very odd output like below essentially the same user repeated loads of times then another users repeated the same way. When check a few of the devices they are linked to active users that you can find in the user management.  

admin: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 and d.tkmodel='575'
name description dn partition
=============== ====================================== ==== ===========
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP
BOTSUSACOUP6302 Susan Coupland Android 6302 6302 BP-CORP

[Nithin Eluvathingal]

try the below.

run sql ccm select name from device where fkenduser is null and tkclass = 1 and tkmodel = ("562", "575", "503")