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!

Published by Team UC Collabing

Leave a Reply

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