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)