Finding old or unused phones that haven't registered in more XX days? How?

[voip7372]

Hi.   I've seen discussions on (via a search) for various ways to find unregistered devices and more importantly, the last date they registered) so you know if a (for example) IP Communicator client has been active and therefore a valid device to leave in the system, but if it hasn't been active in whatever amount of time we decide is long enough, delete the device because it's probably not used anymore.  For example, if you use auto-registration and self-provisioning to configure the CIPC devices, eventually the user will end up getting a new computer and then when they use self-provisioning again, the device name will change and I assume that old device will stay in the system forever - unless we delete it.

Anyway, is there a reasonably easy way for someone like me who is not a SQL expert, etc and doesn't want to or just can't dig deep into the system with specialized tools to simply list all the current phone devices in the system and show the last time it was registered?  

If I know a phone hasn't been registered for something like 90 days for example, I would feel ok deleting it.  I think over time we'll end up with a lot of old CIPC devices that need to be removed if we use self-provisioning because when the user loads CIPC on the new computer the first time, a new device will be created with the new MAC address of that person's new/different computer and it'll get an auto-registered DN.  

[Jonathan Unger · Accepted solution]

Hi there,

The method I know is to query the risdb and cross reference the list of registered phones and dates with a exported list of devices to determine the unneeded devices.

Here are the steps for that process:

1. Obtain the risdb data:

   a. Initiate an SSH cli session with each UCM node.

   b. run the command "show risdb query phone"

   c. Combine the output from each server to a .csv file.

   d. Convert the field in the csv "RegStatusChg TimeStamp" from unix timestamp format to human readable format using the following formula in a new column "=(Z6/86400)+25569+(-7/24)". Z6 is the cell in unix time format and -7 is your GMT offset. After applying the formula, change the cell formatting type to date (I used short date in this case).

2. Obtain the full list of devices from UCM:

Note: I am not an SQL guy either (yet) so I opt for the easier (in my opinion at this time) BAT export method:

   a. Login to the UCM publisher node administration page.

   b. Navigate to "Bulk Administration" > "Import/Export" > "Export".

   c. Under the "Device Data" section select "Phone"

   d. Give the job a description and name and run the job now or later (don't worry about warning messages referencing dependencies).

Note: Depending on your version of UCM, number of devices, and device registration counts, the BAT export job can cause CPU usage spikes which can impact call processing. If in doubt, run this step after hours. I usually have RTMT open to monitor the cluster when performing bulk operations.

   e.Ensure that the export job has completed successfully by navigating to "Bulk Administration" > "Job Scheduler".

   f. After the job as finished running, navigate to "Bulk Administration" > "Upload/Download Files" > and download the exported phones.

Note: You should now have the two parts of information required to make your decision:

- A list of all the phones that have registered recently.

- A list of all the phones on the system.

3. Cross reference the two parts of information and determine the phones that need to be deleted:

This last step can be accomplished a number of ways. You can use excel if you like. A quick google search for comparing two lists is a good start.

[voip7372]

Thanks.  I'll check this out...

[Jonathan Unger]

Excellent, please let us know if this works for you so others can benefit as well.

[Wilson Samuel]

Fantastic info I needed this 

tnx