SQL for Last Known IP

[tryingtodomyjob]

Hi, I am trying to write a SQL query for the device name, last active, and last known IP address. However, IP address is not showing. The phones do show their IP addresses in CUCM. What can I do to fix this?

[dstaudt]

AFAIK the registrationdynamice table is only supposed to work for IP addresses as of CUCM 12.5SU2+ (corrected)..even so I have not had much luck in getting it to show more than a few phone IPs.

The Risport70 web API is designed for this kind of registration/IP query, and would be the route I would recommend, since it's supported for all extant CUCM versions: https://developer.cisco.com/docs/sxml/#!risport70-api

Some samples in this repo: https://github.com/CiscoDevNet/serviceability-python-zeep-samples

[tryingtodomyjob]

According to this post, Solved: Which table of CUCM DB store the IP address of IP Phones？ - Cisco Community It said 12.5SU2+.

[dstaudt]

Ah, you are correct...I've edited my post.

[tryingtodomyjob]

I don't see Risport70 web API able to search by and display the DevicePool information.

[dstaudt]

This is true...you would need to use AXL <getPhone> to obtain the device pool of a given phone: https://pubhub.devnetcloud.com/media/axl-schema-reference/docs/Files/AXLSoap_getPhone.html#Link698
The AXL <listPhone> request allows searching for phone by device pool: https://developer.cisco.com/docs/axl-schema-reference/
So the workflow could be something like:
* Obtain a list of phones for a particular device pool via AXL <listPhone>
* Retrieve the RIS status of the devices via Risport70 <SelectCmDeviceExt>, with <SelectBy>Name</SelectBy> providing each phone device name as an <Item> entry

[Elliot Dierksen]

I have used the CLI commands "show risdb query [phone|gateway|sip]" and then scraped what I needed from the text output. That worked for me on CUCM 10.5 and 12.5.