Finding Incorrectly Provisioned Extensions on CUCM with SQL

A customer had a multi-site deployment, with 30,000+ extensions, but is facing problems with dialing from missed calls directory not working for random users, generating on-going tickets for L1 support teams.  As is often the case, this is due to improper provisioning standards on a system with growth spanning several years and staggered site deployments.

In this case, External Phone Number Mask was not set on the directory number for extensions with E164 numbers, without a leading “+”.  However, users needed to dial with the plus.  As a result, the calling number dialing from the missed called directory was missing a plus sign, so calls were failing.  L1 had been struggling to deal with this for several weeks.  I offered to find all the affected extensions using SQL.  We needed to account for the following requirements:

  • Ignore hunt pilots
  • Ignore “AndTek” 3rd-party CTI Route points and ports, all provisioned with an “AND” naming prefix
  • Ignore dummy extensions starting with “789”
  • Ignore correct provisioning EPNMs, provisioned with a leading +

The query used to achieve this is:

SELECT d.name, n.dnorpattern, dnmap.e164mask, pu.name
FROM device d
INNER JOIN devicenumplanmap dnmap ON dnmap.fkdevice=d.pkid
INNER JOIN numplan n ON dnmap.fknumplan=n.pkid
INNER JOIN typepatternusage pu ON n.tkpatternusage=pu.enum
WHERE dnmap.e164mask NOT LIKE '+%'
AND n.dnorpattern NOT LIKE '789%'
AND d.name NOT LIKE 'AND%'
AND pu.name != 'Hunt Pilot'
ORDER BY pu.name, d.name

Additionally, as there were several hundred affected extensions, and as the EPNM was used extensively in the customer dial plan, we were worried that these would break certain call flows – especially for outbound dialing via SIP trunks with IOS transition rules, which may have been incorrectly dependent there NOT being a plus prefix.  We decided to conduct call flow tests an extension per range.  To do this, I crafted the following query to match all number ranges targeted for testing:

SELECT DISTINCT LEFT(n.dnorpattern, 8) AS did_range
FROM device d
INNER JOIN devicenumplanmap dnmap ON dnmap.fkdevice=d.pkid
INNER JOIN numplan n ON dnmap.fknumplan=n.pkid
INNER JOIN typepatternusage pu ON n.tkpatternusage=pu.enum
WHERE dnmap.e164mask NOT LIKE '+%'
AND n.dnorpattern NOT LIKE '789%'
AND d.name NOT LIKE 'AND%'
AND pu.name != 'Hunt Pilot'
AND n.dnorpattern MATCHES '[0-9]*'
ORDER BY LEFT(n.dnorpattern, 8)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.