topic SQL for Last Known IP in Management

SQL for Last Known IP

tryingtodomyjob — Mon, 04 Dec 2023 16:49:39 GMT

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?

Re: SQL for Last Known IP

dstaudt — Mon, 04 Dec 2023 19:47:55 GMT

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

Re: SQL for Last Known IP

tryingtodomyjob — Mon, 04 Dec 2023 19:11:51 GMT

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

Re: SQL for Last Known IP

dstaudt — Mon, 04 Dec 2023 19:48:28 GMT

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

Re: SQL for Last Known IP

tryingtodomyjob — Wed, 06 Dec 2023 14:40:59 GMT

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

Re: SQL for Last Known IP

Elliot Dierksen — Wed, 06 Dec 2023 16:07:52 GMT

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.

Re: SQL for Last Known IP

dstaudt — Wed, 06 Dec 2023 18:54:55 GMT

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