Duplicating IVR entries in FreePBX 13

JaredBusch

I absolutely hate making IVR entries in FreePBX. It is a hundred million clicks to get anything done.

Unlike many pieces of FreePBX, there is no duplicate button in the GUI for these.

Like everything with FreePBX, the IVR entries are stored in the mysql database.

So first, make one in the GUI and assign everything.
Now we can get into mysql and see what is available.

mysql> select * from ivr_details;
+----+------------------------+--------------------------------+-----------+--------------+------------+---------------+-------------------------+------------------------+-----------------+-------------------+-------+--------------+-------------------+-------------------------+------------------------+---------------+-------------------------+-------------------------+-----------------+-----------------+---------+
| id | name                   | description                    | alertinfo | announcement | directdial | invalid_loops | invalid_retry_recording | invalid_destination    | timeout_enabled | invalid_recording | retvm | timeout_time | timeout_recording | timeout_retry_recording | timeout_destination    | timeout_loops | timeout_append_announce | invalid_append_announce | timeout_ivr_ret | invalid_ivr_ret | rvolume |
+----+------------------------+--------------------------------+-----------+--------------+------------+---------------+-------------------------+------------------------+-----------------+-------------------+-------+--------------+-------------------+-------------------------+------------------------+---------------+-------------------------+-------------------------+-----------------+-----------------+---------+
|  1 | Cape Girardeau         | General IVR for Cape Girardeau |           |            1 | ext-local  | 3             | default                 | app-blackhole,hangup,1 | NULL            | default           |       |           10 | default           | default                 | app-blackhole,hangup,1 | 3             |                       0 |                       0 |               0 |               0 | 0       |
+----+------------------------+--------------------------------+-----------+--------------+------------+---------------+-------------------------+------------------------+-----------------+-------------------+-------+--------------+-------------------+-------------------------+------------------------+---------------+-------------------------+-------------------------+-----------------+-----------------+---------+

And the entries for it..

mysql> select * from ivr_entries;
+--------+-----------+-------------------------------+---------+
| ivr_id | selection | dest                          | ivr_ret |
+--------+-----------+-------------------------------+---------+
|      1 | 0         | ext-group,5303,1              |       0 |
|      1 | 1         | ext-group,5301,1              |       0 |
|      1 | 2         | ext-group,5302,1              |       0 |
|      1 | 3         | ext-group,5304,1              |       0 |
|      1 | 4         | ext-group,5304,1              |       0 |
|      1 | 8         | ext-featurecodes,*98,1        |       0 |
|      1 | 9         | app-pbdirectory,pbdirectory,1 |       0 |
+--------+-----------+-------------------------------+---------+
7 rows in set (0.00 sec)

If you want to not have to edit as much later, you can now make the rest of the IVRs in the GUI, but do not set any of the destinations.

Then once your IVRs are all made you can simply copy the destinations.

mysql> insert into ivr_entries select 2,selection,dest,ivr_ret from ivr_entries where ivr_id = 1;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

now there are two sets in the ivr_entries table and you can tweak them in the GUI easier. Or as is the case here, I do not need to change them because it is simply the night IVR that has a different message.

mysql> select * from ivr_entries;
+--------+-----------+-------------------------------+---------+
| ivr_id | selection | dest                          | ivr_ret |
+--------+-----------+-------------------------------+---------+
|      1 | 0         | ext-group,5303,1              |       0 |
|      1 | 1         | ext-group,5301,1              |       0 |
|      1 | 2         | ext-group,5302,1              |       0 |
|      1 | 3         | ext-group,5304,1              |       0 |
|      1 | 4         | ext-group,5304,1              |       0 |
|      1 | 8         | ext-featurecodes,*98,1        |       0 |
|      1 | 9         | app-pbdirectory,pbdirectory,1 |       0 |
|      2 | 0         | ext-group,5303,1              |       0 |
|      2 | 1         | ext-group,5301,1              |       0 |
|      2 | 2         | ext-group,5302,1              |       0 |
|      2 | 3         | ext-group,5304,1              |       0 |
|      2 | 4         | ext-group,5304,1              |       0 |
|      2 | 8         | ext-featurecodes,*98,1        |       0 |
|      2 | 9         | app-pbdirectory,pbdirectory,1 |       0 |
+--------+-----------+-------------------------------+---------+
14 rows in set (0.00 sec)

I'll try and post some more advanced copy options later.

bigbear

This is awesome.

I wish their was a way to import default trunk settings. Is something similar possible?

JaredBusch

@bigbear said in Duplicating IVR entries in FreePBX 13:

This is awesome.

I wish their was a way to import default trunk settings. Is something similar possible?

Never looked because I never have that multiple trunks from the same provider.

Why would you have multiple trunks from the same provider?

bigbear

@JaredBusch we have 4 border controllers for origination/termination in the U.S. So any IP where I call may originate from needs to be in a trunk. As per FreePBX developers you have to list each IP under a unique trunk.

Generally we would route calls in and out from one region, but the others are for network failover.

It would be nice to import them and then quickly edit each trunks user/password for the unique customer.

On our freeswitch platform and on broadsoft this was a much different experience.

bigbear

@JaredBusch and we are actually a voip provider. We have are own interconnects in local markets. About 10 years running now.

This was originally a service we only provided WISP and Fiber customers. A lot of market competition has kicked up over the last 2 years so we are splitting our business up. Some guys are staying with VSAT/WISP company and the rest are going with expanding VoIP services.

JaredBusch

@bigbear said in Duplicating IVR entries in FreePBX 13:

@JaredBusch and we are actually a voip provider. We have are own interconnects in local markets. About 10 years running now.

This was originally a service we only provided WISP and Fiber customers. A lot of market competition has kicked up over the last 2 years so we are splitting our business up. Some guys are staying with VSAT/WISP company and the rest are going with expanding VoIP services.

Okay, I was wondering WTF you were doing. As a provider I get that. This will be an entire side conversation, but that answer is yes.

Remember FreePBX is not a PBX itself, it is simply a management layer on top of Asterisk. Everything you do in FreePBX writes to the database and then when you click the apply button, it rewrites the asterisk files and restarts asterisk.

Probably some random exception to the rule, but this is how FreePBX is designed. It never writes directly to the Asterisk configs.

As a provider, why would you be using FreePBX anyway? If I was going to be providing SIP service with Asterisk, I would be doing it straight from Asterisk, or my own tools wrote to configure Asterisk.

bigbear

@JaredBusch we aren't. It's a long story. We have about 8 years invested in FreeSwitch, OpenSips and Kamailio. A lot of custom development.

A couple years ago I had looked to leave our setup and we deployed Broadsoft. The grass wasnt greener on the other side so we doubled down on our platform.

Will back channel you sometime about the FreePBX use. Its caught my attention because they seem to be pulling way ahead of all the other legacy vendors who are dropping out of the market.

We are more focused right now on SMS/MMS integration and cellular.

JaredBusch

For the curious, here is an example of inserting a pair of new IVR's.

insert into ivr_details (name,description,alertinfo,announcement,directdial,invalid_loops,invalid_retry_recording,invalid_destination,timeout_enabled,invalid_recording,retvm,timeout_time,timeout_recording,timeout_retry_recording,timeout_destination,timeout_loops,timeout_append_announce,invalid_append_announce,timeout_ivr_ret,invalid_ivr_ret,rvolume) values ('Jeff City','General IVR for Jeff City','',1,'ext-local','3','default','app-blackhole,hangup,1',NULL,'default','',10,'default','default','app-blackhole,hangup,1','3',0,0,0,0,'0'),('Jeff City - Night','IVR for Jeff City after hours','',2,'ext-local','3','default','app-blackhole,hangup,1',NULL,'default','',10,'default','default','app-blackhole,hangup,1','3',0,0,0,0,'0');

insert into ivr_details (name,description,alertinfo,announcement,directdial,invalid_loops,invalid_retry_recording,invalid_destination,timeout_enabled,invalid_recording,retvm,timeout_time,timeout_recording,timeout_retry_recording,timeout_destination,timeout_loops,timeout_append_announce,invalid_append_announce,timeout_ivr_ret,invalid_ivr_ret,rvolume) values ('Jeff City','General IVR for Jeff City','',1,'ext-local','3','default','app-blackhole,hangup,1',NULL,'default','',10,'default','default','app-blackhole,hangup,1','3',0,0,0,0,'0'),('Jeff City - Night','IVR for Jeff City after hours','',2,'ext-local','3','default','app-blackhole,hangup,1',NULL,'default','',10,'default','default','app-blackhole,hangup,1','3',0,0,0,0,'0');

table

I'm currently trying to edit the dest in ivr_entries based off a couple variables from another table with php. Am I going about this the wrong way? Is there a simpler way to do this? I know post is old but thanks for taking time to read.

JaredBusch

@table said in Duplicating IVR entries in FreePBX 13:

I'm currently trying to edit the dest in ivr_entries based off a couple variables from another table with php. Am I going about this the wrong way? Is there a simpler way to do this? I know post is old but thanks for taking time to read.

You are looking in the right place.

Can you post a little more detail? These examples are extremely high level just to show people where to look really.

table

@jaredbusch

<?php
$servername = "";
$username = "";
$password = "";
$dbname = "asterisk";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO ivr_entries (ivr_id, selection, dest)
VALUES ('25', '43156', 'from-did-direct,820,1');";

if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

The script was just a proof of concept to see if I could make the changes without breaking freepbx. I'm now trying to include logic to process data from another table and adjuct the dest fields in the other table accordingly.

I'm trying to make a php script that updates the table ivr_id dest (different queues) base on information from another table called ivr3

I added the pk_column for auto_increment. Should I add this into the ivr_entries also? I'd like to use these four fields for routing the call via ivr to certain dest based on rules. I'm not sure the logic of the rules though. Would I use weight and add a total or something. I've started code academy because my google fu has failed me as of yet.

Thanks for quick reply and taking the time to read that.

JaredBusch

@table said in Duplicating IVR entries in FreePBX 13:

Should I add this into the ivr_entries also?

Never. You will totally break updates if one of them looks at the table.

JaredBusch

Ok, this is definitely complicated.

You will want to make your own tables and use something to reference back.

The part that makes this hard is that if anyone makes a change to an IVR outside of your tool, the references could get changed.

FreePBX does not have any type of unique keys since it is designed with being the master in mind.

table

@jaredbusch

Is there a better way to do this with whats included in freepbx or commercial modules? I was thinking just putting the script in cron and just have it run every so many hours. Then eventually making a form to add or modify the data.

JaredBusch

@table said in Duplicating IVR entries in FreePBX 13:

@jaredbusch

Is there a better way to do this with whats included in freepbx or commercial modules? I was thinking just putting the script in cron and just have it run every so many hours. Then eventually making a form to add or modify the data.

What I would do is update your ivr3 to include the id and name from ivr_details and think of those as the unique key. Or add them to a separate cross reference table, however you want to handle it.

Then add logic to your system to fail and notify if there is a mismatch (aka someone changed it in FreePBX).

There is no way to do this in FreePBX itself.

Once you change the database, you will have to reload everything.

You can issue the fwconsole reload command from your script. That is the same as clicking the red "Apply Config" button.