Wednesday, 21 December 2016

Finding Lines With a Specified External Phone Number Mask

Via the power of SQL queries you can quickly determine what devices & lines have a specified external phone number mask. Handy for homing in on possible causes of calls with incorrect caller ID. Using like in the SQL query means that it's also possible to use certain wildcards (e.g. % being zero or more characters) to aid in the search.

select d.name, d.description, n.dnorpattern, dmap.e164mask from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on dmap.fknumplan = n.pkid where dmap.e164mask like '%2081234567' order by d.name

The output will include the device name, device description, DN & external phone number mask of any matching devices & lines:

admin:run sql select d.name,d.description,n.dnorpattern,dmap.e164mask from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid where dmap.e164mask like '%2081234567' order by d.name
name            description     dnorpattern e164mask
=============== =============== =========== =============
SEPF09E636E5656 SEPF09E636E5656 1000        +442081234567
SEPF09E636E5657 SEPF09E636E5657 1001        +442081234567


No comments:

Post a Comment