Perl or SQL function to match dialplan pattern

FreePBX Development
1

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

19328555
_13XXXX
_1933370[0-2]
_2805XX
_28[3-7]XXX
_331XXX
_848XXX
_85XXXXX
_879XXX

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

$number=‘8481234’;
$pattern=’_879XXX’;
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.

Thanks!

2

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.

EDIT:

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      |
3

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.

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

asterisk.ivr_dests
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 10.22.5.119 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:

4

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

5

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

1 Like
6

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.

7

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