Need to find phones that never got removed from the system

[dhoskins]

We have a lot of phones in our system that never got removed. These were phones that got replaced for one reason or another. I would like to be able to get a list of all these phones so I can begin cleaning up the system. They appear as below. I wasn't sure if this would be some sort of Axl query to get a list of these devices or a Risport query. I'd appreciate any guidance that I can get on this.

Real-time Device Status

Registration:	Unknown
IPv4 Address:	None

[dhoskins]

I'm assuming this may not be possible with no responses.

[dstaudt]

The phone registration and last IP can be queried via the Risport API: https://developer.cisco.com/docs/sxml/#!risport70-api
However, that data there is a bit ephemeral, I believe accumuted over the last couple of days only.  For example, if you restart the cluster and check before any phones register, they would all show Unknown/None.  Or, if you used your phone 3 days ago but unplugged it, it might show as Unknown/None.
If you can come up with some additional criteria for how to discriminate unused/unremoved/orphan devices that may be helpful, for example phones that have no lines assigned, or no end user associated, or certain old/obsolete models, etc.  To query those details, you would be looking at the AXL API: https://developer.cisco.com/docs/axl/
In a perfect world, you could maintain a database of phone registrations/sightings from Risport over some length of time (weeks), and correlate that with clues from the database/AXL to come up with a list of candidate devices for removal - that would keep false positives to a minimum.

[Elliot Dierksen]

I haven't tried the RISPORT API. When I had to do this, I used a python script to log in to the CLI of all the CM nodes, collect a risport query, and then parsed it with some shell scripts. It was a bit clunky, but it worked.

Edit: this the thread where this was discussed. https://community.cisco.com/t5/management/sql-for-last-known-ip/td-p/4971079 

[dhoskins]

I did a risport query on phone. Is there any reason why alot of the data is ending up under the wrong column? I copied the output from the cli into notepad and then gave it the csv extension and when I open it in Excel, I have alot of data that appears to be under the wrong column.

[Elliot Dierksen]

I am a Unix guy, so I parsed the data with awk. I don't recall that there were commas in the risport output. You might have better luck making it a txt file and importing it to excel by defining the columns as fixed.

[dhoskins]

Ok, thanks for the help.

[Elliot Dierksen]

I just looked at my script and there are commas in the risdb output. The problem is that the phone descriptions can have commas in them. I did some non-trivial data bludgeoning in my awk script to figure out where the values were that I needed.