Browse By

CUCM SQL Query – Active/Inactive Directory Numbers

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.

uccollabing.com

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Approved
Subodh Kumar
Approved
Claudio Quiroz Cabello

Electrical Engineer CCNA Collaboration CCNA Voice CCNA R&S. I'm Electrical Engineer and I work like Support Engineer for a Telephony Service Provider Company. Before that I had position like Pre-Sale Engineer about small and medium multivendor telephony solutions. I have 5 years of experience working in Telephony Multivendor Projects. I am also 3 years of experience working in Support and implementation about Cisco Collaboration platforms: UCS server, UCM, Cisco Unity, Presence Server, Jabber, Gateway. Support Community is most useful in finding design, implementation and support tricks and shortcuts that greatly help me in my daily activities. If you need to contact me, please you can writte me.

Approved
Kanishka Singh

CCNA Routing and Switching
Am a technology enthusiast. I work in network support providing support with implementation, maintaining and precisely troubleshooting the infra.
Realm of collaboration makes me want to learn and face new challenges more and more.