Perl or SQL function to match dialplan pattern

I have an old version of freePBX (over 5000 extensions, hundreds of IVRs) that I must document for migration to newer version. I must map what IVRs use which trunks. To do this, I must match the number being dialed by the IVR selection to the dial pattern of the outbound route.

The ‘extensions’ column of the table with patterns I must match looks like


For example I must find which ‘extensions’ pattern matches the number 8481234 then I can grab the trunk from another column.

I know there must be a function embedded in Asterisk that works like

if (asterisk_pattern_match($number,$pattern)) {
#get trunk column from that row

It could be SQL, or Perl or PHP. I could write it, but I’m sure I would be reinventing the wheel. Does anyone have or know where a function like this may be? I have googled every way I can think of, but all the results are about using MySQL within the asterisk dial plan, and that is of no value to me.


Hi Jerry,

First, how old? Is it possible to upgrade from within the FreePBX application? Could you create a second box to do the upgrade on and simply move that back into production?

To answer your actual question:
The table asterisk.ivr_entries contains the “selection” column and the corresponding destination for a given IVR.

mysql -u user -ppassword -e “select * from asterisk.ivr_entries where selection = ‘8481234’;”

The table asterisk.trunks can be connected to the table asterisk.outbound_route_patterns by a join of each table to asterisk.outbound_route_trunks. Here’s a sample query; remove everything except * between “select” and from" so that it reads like the first query to see ALL the columns.

mysql -u user -ppassword -e "select \
asterisk.outbound_route_patterns.match_pattern_pass \
,asterisk.trunks.* \
from asterisk.outbound_route_patterns \
  join asterisk.outbound_route_trunks \
    on asterisk.outbound_route_patterns.route_id=asterisk.outbound_route_trunks.route_id \
  join asterisk.trunks \
    on asterisk.outbound_route_trunks.trunk_id=asterisk.trunks.trunkid \
where asterisk.outbound_route_patterns.match_pattern_pass = '_879XXX';"

You could probably join your destination from ivr_entries to something in trunks, but I’d have to put some test data into our server.


If your destination is a trunk, you could do this:

mysql -u user -ppassword -e "select * \
from asterisk.ivr_entries
  join asterisk.trunks
    on SUBSTRING(asterisk.ivr_entries.dest,LOCATE(',',asterisk.ivr_entries.dest)+1,1)=asterisk.trunks.trunkid;"

This works because the first number after the first comma in the dest column is the ID of the trunk:

select * from ivr_entries;
| ivr_id | selection | dest          | ivr_ret |
|      1 | 1337      | ext-trunk,2,1 |       0 |

select * from trunks;
| trunkid | name  | tech  | outcid     | keepcid | maxchans | failscript | dialoutprefix | channelid | usercontext | provider | disabled | continue |
|       1 |       | dahdi |            |         |          |            |               | g0        |             | NULL     | off      | off      |
|       2 | Bacon | pjsip | 7114222667 | off     |          |            |               | Bacon     |             |          | on       | off      |

select * \
from asterisk.ivr_entries \
  join asterisk.trunks \
    on SUBSTRING(asterisk.ivr_entries.dest,LOCATE(',',asterisk.ivr_entries.dest)+1,1)=asterisk.trunks.trunkid;

| ivr_id | selection | dest          | ivr_ret | trunkid | name  | tech  | outcid     | keepcid | maxchans | failscript | dialoutprefix | channelid | usercontext | provider | disabled | continue |
|      1 | 1337      | ext-trunk,2,1 |       0 |       2 | Bacon | pjsip | 7114222667 | off     |          |            |               | Bacon     |             |          | on       | off      |

Thanks, Overkill, but this version is older than that. It claims to be 2.5.1, but seems older than the 2.1 ISO I downloaded. It was installed in 2005 from scratch. I do not have a outbound_route_patterns or trunks table. The outbound routes are in the extensions table, and the trunks are in the sip table. I have ivr and ivr_dest tables, but I see no way to join anything to get to trunks except to match the number to the pattern in extensions.

outrt-007-SFB_Route _85XXXXX 1 Macro dialout-trunk,19,${EXTEN}, 0

8 2 ext-miscdests,8,1 0 8 SJB Central Scheduling 8136353610 Dial:96353610

asterisk.sip (to describe 1 trunk):
999922 account BCUCCECMSUB1B 2
999922 host 3
999922 type friend 4
999922 context from-trunk 5
999922 canreinvite yes 6

Note that “dialout-trunk,19” appears to be looking for trunk 19, but in sip table they all have 6 digit identifiers (table has no key) so I’m not sure how I’m going to map the trunk once I get the match on the dial pattern, but I can map that manually – there’s only 43 trunks. :smile:

Not my work (largely written by jkiel for Caller ID Superfecta) but I use it often enough I dumped it to a gist:

Thanks, Igaetz! That looks like what I am looking for. I’ll try it out and report results.

1 Like

Thanks Lorne!

Let me know how it goes, Jerry. If it doesn’t work out, attach the following file or put it on pastebin or something:

mysqldump -u user -ppassword --no-data asterisk > schema.sql

This will give me an idea of what to try in a query.

Hey, sorry for the delay. The code worked after I modified it to read from database fields. Thanks!