UCM - List all active DN's

[esa_fresa]

We have a number of DN's assigned to users in our UCM Database that are no longer active. I'm looking for a way to find all "active" phone numbers so that we can delete ones which are not being used. Ideally this will be "phones that have not called out in >30 days" but the criteria isn't strict so long as our general objective is accomplished.

I was looking at SQL queries to find this information, as I imagine it's stored within the UCM database, but I haven't found the table I'm looking for yet.

https://developer.cisco.com/media/UCM10.5DataDictionary/UCM10.5DataDictionary.htm

Does anyone know how I could do this?

[Gregory Brunn · Accepted solution]

You could pull your cdrs, make a list of numbers called or being called and then just that against your number range to find them. But I would just start by looking at numbers that are not assigned to anything remove anything not physically assigned to phones, udp, etc in cucm. I don't know any sql statement that has this information. 

[Jaime Valencia · Accepted solution]

No, I'm afraid you're wrong, CUCM doesn't keep track of DN usage in the DB.

As Gregory has pointed out, you'd need to use CDR/CAR to find what DNs ARE being used, and then weed out from your dial plan report those that do not show in the reports.