SQL command in CUCM to get list of phones and registration status and Owner ID

[CiscoRunner]

I am trying to figure out a way that I can gather a list of all IP phone with the Registration status (Registered, UnRegistered, Unknown). I have tried by using RTMT but that data is not exportable.

I have also tried using "show risdb query phone" but the problem with that was that it only gives you the info for the phones that are currently Registered to the system, so you dont get the info for unregistered phones.

My final attempt to get the data will be a SQL command via the CLI. So far I only know of 

run sql select name,description from device where name like ‘SEP%’

This command does give me data but I am not sure which field is the registration status or the Owner ID

Would anyone have any idea which field refers to the registration status & Owner ID from the output of the SQL command

Many thanks in advance

[Jaime Valencia]

There's no way to get all that with a single command, you want the device and owner ID, you can get that from the DB, but the only way to find registered devices, is with risdb query, you'll need to map the info from both places to get what you want.

[Nikolay Kozyrev]

Hello, Jaime.

Will you able advise If i want the same outputs like "run sql select name,description from device where name like ‘SEP%’" plus registration status (OR better registeredTimeStamp)?

Thanks in advance.

[Ayodeji Okanlawon]

Your best option is show risdb query phone. It does give you phones that are unregistered with the particulate cucm node you ran the query on. It also gives you the userid of a user logged into the phone. It doesn't shown the owner user id. Only RTMT does that..

Here is an example..Under regstatus,, you will see unr (for unregistered) on this cucm node.

 Descr  Ipaddr  Ipv6addr  Ipv4Attr  Ipv6Attr  MACaddr  RegStatus
 DB100 - 2578 - Hin  192.141.158.78   0 0  00E075F3CAD2  reg
 DB001 - EM  192.130.176.39   0 0  00127FDE9BAA  unr
 

[Roger Kallberg]

Have a look at this post. It would do what you ask for. https://nocthoughts-wordpress-com.cdn.ampproject.org/c/s/nocthoughts.wordpress.com/2020/06/01/python-checking-device-registration-cucm/amp/