Can I see the last time a phone registered?

[mmillington_nal]

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

TIA,

Matt      

[Aaron Harrison]

Might be better/easier to search the CDR for calls to/from that device?

Aaron

[will.alvord]

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.

[mmillington_nal]

Will,

That would be awesome if you could post a sample.

Thanks,
Matt

[Joseph Martini]

You could try and use the "show risdb query phone" CLI command which prings out the DeviceName as well as the LastActTimeStamp.

[mmillington_nal]

Joe,

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?

Thanks,
Matt

[Joseph Martini]

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.

[mmillington_nal]

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...

[Joseph Martini]

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.

[Richard McLoughlin]

Hi Matt

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?

Richard

[will.alvord]

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.

[mmillington_nal]

I used =(T9-6*3600)/86400+25569

and it seems to have converted properly...

[William Bell]

Joe,

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.

-Bill

[will.alvord]

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.

[will.alvord]

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.