CUCM SQL Query – Active/Inactive Directory Numbers

Recently i was asked to find Inactive Directory Numbers in Cisco Unified Communications Manager (CUCM), which means that the “Active” checkbox in the Directory Number Configuration Page is not checked.

Immediately I navigated to Call Routing >  Route Plan Report > Select Unassigned DN and Click on Find.
I found all the Directory Numbers which were not assigned to any IP Phones. But the problem with them was, some of them has “Active” checkbox checked and some of them has “Active” box unchecked. Now the biggest task was to find only the Inactive ones out of Active and Inactive. To cut long story short, i had to only find the Directory Numbers where “Active” box was unchecked.

The below SQL query will help you to find the Directory Numbers which are Inactive.
run sql select n.dnorpattern from numplan n left outer join devicenumplanmap m on m.fkdevice = n.pkid where m.fkdevice is null and n.tkpatternusage = ‘2’ and n.iscallable = ‘f’

If you change n.iscallable = ‘t’ then it will find all the Directory Numbers which are Active.
run sql select n.dnorpattern from numplan n left outer join devicenumplanmap m on m.fkdevice = n.pkid where m.fkdevice is null and n.tkpatternusage = ‘2’ and n.iscallable = ‘t’

\\\\\\\\\\\\\\\\\\\\ USAGE\\\\\\\\\\\\\\\\\
tkpatternusage = ‘2‘ refers to Directory Number element
n.iscallable = ‘t‘ refers to True (if the Active checkbox is checked) and ‘f‘ refers to False (if the Active checkbox is unchecked)
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Hope this helps!

Avinash Karnani

I am working in an IT company and having 10+ years of experience into Cisco IP Telephony and Contact Center. I have worked on products like CUCM, CUC, UCCX, CME/CUE, IM&P, Voice Gateways, VG224, Gatekeepers, Attendant Console, Expressway, Mediasense, Asterisk, Microsoft Teams, Zoom etc. I am not an expert but i keep exploring whenever and wherever i can and share whatever i know. You can visit my LinkedIn profile by clicking on the icon below.

“Everyone you will ever meet knows something you don’t.” ― Bill Nye