I'm trying to clean up some phones that are still configured and using licenses, but are no longer in use. Is there any way I can see the last time a phone registered with a UCM server? I'm running version 7.0.2
Solved! Go to Solution.
Not to beat a dead horse, but it seems that I have come up with a workaround without realizing there was a problem. I didn't know about the RIS cleanup setting that Joe and Bill discussed earlier. But as luck would have it, what I setup doesn't rely on that - quite by accident.
Here's what I've setup:
1) Grab a list of all devices from sql
2) Feed them to the RIS API
3) Log all to db (this is done nightly). Since no data is returned for the unregistered devices which RIS has discarded the data for, I mark those as unknown status.
4) Then any unknown devices which go 2 weeks without an updated status get marked for removal (manual - I don't automate deletions).
I can't share the code for that since it doesn't belong to me any longer, but you can do the same thing even though it's a manual process. If you're good with excel, you can export all phones, pull the risdb query which Joe listed above for each server and get it into excel, then do some v/hlookup magic. It's not too horrible of a process.
Might be better/easier to search the CDR for calls to/from that device?
The only way that I know of is via the Serviceability AXL API. Basically, you call SelectCmDevice passing one or more device names and it spits back some data including the the last registration info per server. If you want, I can post a small sample.
You could try and use the "show risdb query phone" CLI command which prings out the DeviceName as well as the LastActTimeStamp.
That worked great. I've got it in a CSV, but the time stamp is just a number. Do you know how I need to reformat it to something readable as a date/time?
The timestamp is in epoch time, you can use a tool like this to convert it (http://www.epochconverter.com/). I'm sure there's a way to make excel convert it but I'm no excel expert.
Actually Joe, now that I look at the purpose of the show risdb query, it looks like it doesn't have the historical data that I'm looking for. I'm trying to find phones that are in the DB, but haven't registered in months. I want to delete those phone to free up license units. The query only showed 3 unregistered phones, but i know there are dozens that are not registered...
The RISDB is maintained per server if I'm not mistaken, try and run the command on a few other servers if you have more than one to see if you have more phones showing unregistered.
Stumbled over this thread as I am in the same boat as you were, trying to clean up phones that were still listed as unknown and no longer used. I was hoping that the "show RISDB query phone" output, while it didn't list "unknown" phones, may still list the phones transitioned from "registered" to "unregistered" that had subsequently transitioned to unknown.
Having run the show RISDB query phone on each of our subscribers, imported the output into Excel, applied your formula (tweaked for GMT) to change the Unix timestamp to dd/mm/yyyy hr:mins, I discovered the same as you: phones that are listed in administration page as "unknown", weren't shown in the RISDB output at all. Then I spotted Joe's post saying the show RISDB query phone output wouldn't provide the information we wanted - Doh, I should have paid more attention to the whole thread!
Interestingly, the oldest status changes date back to 19/09/2015, probably the last time the cluster was rebooted looking at the cluster uptime, but these phones are registered. Only a small handful are unregistered.
So I did some more digging, looked at the Cisco RIS Data Collector Service Parameters and sure enough, the is a "RIS Unused Cisco Call Manager Device Store Period which can be up to 30 days and defaults to 3 days.
So I've arrived at the same conclusion - It would seem that the only way deal with unknown phones is to manually trawl though the list of phones in the administration page and deal with the "unknowns" on a case by case basis. Unless anyone else knows different?
It's a unix timestamp. There are plenty of converters online, but "=(A5/86400)+25569+(-5/24)" is an excel formula which might be better for you (where A5 is the cell with the timestamp).
Thanks Joe! That's really useful and there's no need for sample code since that's probably the source of the same data anyways.
That is a good thought for sure. However, I thought that after a period of time records in that table age out. It just so happens that I have a lab set up where I put phones at one site into SRST mode before the holidays. I never took it out of SRST mode and I just ran the risdb query. The output from the query doesn't show the phones at all.
So, I wonder how valid this output would be. I guess if we knew the timeout value we could at least say a phone has been offline for X days/hours/etc.
Please remember to rate helpful responses and identify
Maybe there is a gain for using the serviceability api then. I uploaded a small example to http://godwilla.com/csc-example.com
Just put the extracted php file on any php-enabled web server (xampp will work just fine). You'll need to google 'nusoap' and download that soap class and update line 3 of my php file to ensure a valid path to the nusoap library. Then you'll need to update the user and password variables with valid credentials to either an enduser or appuser with the axl api access role. Finally, you'll need to enter the ip address of one of your CUCM servers running the axl service and add your device name where noted at the bottom.
Bill - I remember some frustration about that and chalked it up to be poor documentation. The serviceability api docs state that one of the possible returned statuses is 'unknown' but I've never had it return that status. Basically, I track the devices I pass in and if I don't see any data returned for a device, then it's safe to remove.
Seems relevant since the api probably just taps into the same risdb data that the cli show command does.