Utilities to batch create a large number of extensions?
Looking at the MySQL tables
Tue, 06/26/2007 - 1:36pm
Looking at the MySQL tables in the asterisk database, it looks like there is a single entry for each extension in the users table, and 20 entries in the sip table (for sip extensions).
Here's an example:
Lets assume we have "John Doe" with SIP extension 304, and a DID of 6315551304:
select * from users where extension = '304'; +-----------+----------+-------------+-----------+-----------+----------+--------------------+-------------+------------+-------------------------------+ | extension | password | name | voicemail | ringtimer | noanswer | recording | outboundcid | directdid | didalert | +-----------+----------+-------------+-----------+-----------+----------+--------------------+-------------+------------+-------------------------------+ | 304 | | John Doe | default | 0 | | out=Adhoc|in=Adhoc | | 6315551304 | <a href="http://127.0.0.1/Bellcore-dr4" title="http://127.0.0.1/Bellcore-dr4">http://127.0.0.1/Bellcore-dr4</a> | +-----------+----------+-------------+-----------+-----------+----------+--------------------+-------------+------------+-------------------------------+ 1 row in set (0.00 sec) select * from sip where id='304'; +-----+-------------+-------------------+-------+ | id | keyword | data | flags | +-----+-------------+-------------------+-------+ | 304 | account | 304 | 0 | | 304 | accountcode | | 0 | | 304 | allow | | 0 | | 304 | callerid | John Doe <304> | 0 | | 304 | callgroup | | 0 | | 304 | canreinvite | no | 0 | | 304 | context | from-internal | 0 | | 304 | disallow | | 0 | | 304 | dtmfmode | rfc2833 | 0 | | 304 | host | dynamic | 0 | | 304 | mailbox | 304@device | 0 | | 304 | nat | never | 0 | | 304 | pickupgroup | | 0 | | 304 | port | 5060 | 0 | | 304 | qualify | no | 0 | | 304 | record_in | Adhoc | 0 | | 304 | record_out | Adhoc | 0 | | 304 | secret | passwordforjohn | 0 | | 304 | type | friend | 0 | | 304 | username | 304 | 0 | +-----+-------------+-------------------+-------+ 20 rows in set (0.00 sec)
I suppose if we make certain assumptions about the extensions, it wouldn't be that hard to create a csv file that can be imported via mysql at the command line. I'll see what I can come up with.
- Jason
what about freepbx
Tue, 06/26/2007 - 2:01pm
There is a third table that
Tue, 06/26/2007 - 2:38pm
There is a third table that needs to be populated for the extension, or you're right... it doesn't show in FreePBX. That table is devices
So, here's an example of my adding a single extension manually from MySQL:
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','account','755','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','accountcode','','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','allow','','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','callerid','Test User <755>','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','callgroup','','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','canreinvite','no','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','context','from-internal','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','disallow','','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','dtmfmode','rcf2833','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','host','dynamic','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','mailbox','755@device','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','nat','never','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','pickupgroup','','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','port','5060','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','qualify','no','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','record_in','Adhoc','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','record_out','Adhoc','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','secret','PASSWORD','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','type','friend','0');
INSERT INTO sip (id,keyword,data,flags) VALUES ('755','username','755','0');
INSERT INTO users (extension,password,name,voicemail,ringtimer,noanswer,recording,outboundcid,directdid,didalert) VALUES ('755','','Test User','default','0','','out=Adhoc|in=Adhoc','','','http://127.0.0.1/Bellcore-dr4');
INSERT INTO devices (id,tech,dial,devicetype,user,description,emergency_cid) VALUES ('755','sip','SIP/755','fixed','755','Test User','');
Now to think about how to easily duplicate this from PHP while pulling the data from a csv... hrmm...
- Jason
AHHHH... phpmyadmin was the missing key for me
Tue, 06/26/2007 - 5:38pm
I had not used phpmyadmin until today... been using webmin. I was also missing the "devices" table, thats the other reason it wasnt showing up in freepbx, thanks!
I've been using ms sql for years, so this is exactly what I've been looking for. In my environment I wouldnt be doing imports daily or anything, but when I need to turn up 100 + extensions, I know I can just format in xls (still trying get a handle on php) and then blow into the db via the import utility...
Thanks bill, jason...everyone this was (is) extremely useful!
- Matt
there is a thread about
Tue, 06/26/2007 - 7:18pm
there is a thread about three or four months ago when I laid out all the tables you have to hit: user, SIP or IAX [depending on whether the device is sip or IAX], devices.... then you have to stick an entry in the admin table to make the red bar do its thing. Remember that asterisk does not run off the tables, the tables are used to generate the .conf files that asterisk uses.
eoo
Wed, 06/27/2007 - 5:28am
Do you have a link to that thread in your forum history? I dont want to reinvent the wheel here, but the ability to import users is a huge deal... and of course i dont want to import 100 users then realize that I didnt touch another table and screw the whole thing up..
as for the admin table... I just toggled it from false to true and obviously the red bar popped up. What actually triggers the reload though, is it the function behind the red bar? If so, I was just making this much harder than it really is... thanks for your input...
import setting?
Tue, 07/03/2007 - 1:02pm
I have written a small Windows app to import new extension by placing the proper information into sip,users and devices and everything looks fine. When I look at the data that was made by freepbx and the import I did, it looks the same. Freepbx can see that data that I import but when I do the "red bar" reload or any reboot the data is not making it into the Asterisk Database. Only after I go into each extension, click submit, then a red bar reload does it actually go into the asterisk database, 'database show'.
'sip show peers' shows the new data I imported my program without having to go into each extension and clicking submit. I must be missing something crucial.
Thanks.
Hard to say what you're
Tue, 07/03/2007 - 1:41pm
red bar help
Mon, 07/09/2007 - 11:51am
Ok, so this was much easier than I had imagined, but I still have a couple hiccups...anyone care to help?
I have built a web gui using .php pages that can add edit and delete users from the (device,user, sip) tables with no problem. I have 2 downfalls ..
1) I've tried to track it down and cant get a grasp on what the red bar is physically doing. Its obviously taking a snapshot of the db and writing it to the .conf files, but I just cant seem to duplicate. Does anyone else do this via .php?
2) when i add or edit a user via my gui, then go into freepbx (because of the red bar) and press the red bar it reloads and everything is great except voicemail on every user goes back to "disabled". I'm missing a value or adding an incorrect value. I'm placing voicemail=default, after i press the redbar it says voicemail=novm...
I dont want to duplicate freepbx, but i need the basic extension functionality to build an interface that fits into an existing company/client webpage. Any thoughts appreciated...!
Same??
Mon, 07/09/2007 - 8:18pm
Ok... i wrote a quick Perl
Mon, 12/17/2007 - 1:16pm
Ok... i wrote a quick Perl script that does almost everything... the only thing I couldn't figure out is how to get CallWaiting to enable. So after the script is done, you have to go into each extension, enable CallWaiting and submit. Still, it's better than doing everything by hand.
You need to have some understanding of Perl to use this. But it's pretty easy. If you know PHP, you can figure it out.
If you want a file, just message me and I'll send it to you.
Tim
#!/usr/bin/perl -U
# Written by Tim Schreyack, December 2007
# Requirements:
# DBI and DBD::mysql perl modules
# You can install them in perl by running: perl -MCPAN -e 'install DBI' and perl -MCPAN -e 'install DBD::mysql' from the command line
# You may have to manually install DBD::mysql. Download from CPAN: http://search.cpan.org/~capttofu/DBD-mysql-4.005/lib/DBD/mysql.pm
#
# This script is designed to be run as CGI.
# Put it in /var/html/www/cgi-bin and make sure it has read/execute permissions for everyone (ie. chmod 755 batchaddextension.pl).
#
# The CSV file must contain the following comma separated values in each row, in this exact order:
# MAC Address, User Name , Extension, DID, Password, Timezone (Eastern, Central, Mountain, Pacific)
#
# You must change the database username and password on line 48 so the script can login to your database.
$InputPath = "/var/ftp/"; #Location of Polycom Config files
$Voicemail = "/etc/asterisk/voicemail.conf"; #Name of voicemail.conf file including full path
use CGI;
use DBI;
#Get the CSV file from the browser
my $cgi = new CGI;
my $file = $cgi->param('file');
print "Content-type: text/html\n\n
";#Read through the file and process each row
while (<$file>)
{
#Do some error checking before processing that row
($macadd,$name,$exten,$did,$password,$timezone) = split ',', $_;
print "Creating: $macadd : $name : $exten";
if (length($macadd) != 12) {
print " MAC Address not 12 digits long. Skipping.
";
}
elsif (length($name) < 1) {
print " Name is blank. Skipping.
";
}
elsif (length($exten) < 4) {
print " Extension is not at least 4 digits. Skipping.
";
}
elsif (length($did) < 10) {
print " DID is not at least 10 digits. Skipping.
";
}
else {
# If we passed the error checks continue, otherwise skip to the next one
#
# Create FreePBX Extension
$drh = DBI->install_driver('mysql');
$db = $drh->connect(asterisk, datbaseuser, password) #Connect to the database. Replace databaseuser and password with whatever is appropriate for you.
or die "Can't connect to database: asterisk\nError: " . DBI->errstr;
$ucname = uc($name);
$vmailbox = join ('@', $exten, 'default');
$outid = "\"" . $ucname . "\"" . "<" . $did . ">";
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','account','$exten','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','accountcode','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','allow','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','callerid','device <$exten>','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','callgroup','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','canreinvite','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','context','from-internal','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','disallow','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','dtmfmode','rcf2833','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','host','dynamic','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','mailbox','$vmailbox','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','nat','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','pickupgroup','','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','port','5060','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','qualify','yes','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','record_in','Adhoc','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','record_out','Adhoc','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','secret','$FORM{formPassword}','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','type','friend','0')");
$db->do("INSERT INTO sip (id,keyword,data,flags) VALUES ('$exten','dial','SIP/$exten','0')");
$db->do("DELETE FROM users WHERE extension = '$exten'"); #Just in case the extension already was in the table
$db->do("INSERT INTO users (extension,password,name,voicemail,ringtimer,noanswer,recording,outboundcid,directdid,didalert,sipname,faxexten,faxemail,answer,wait,privacyman) VALUES ('$exten','','$ucname','default','0','','out=Adhoc|in=Adhoc','$outid','','','$did','disabled','','0','0','0')");
$db->do("DELETE FROM devices WHERE id = '$exten'"); #Just in case the extension already was in the table
$db->do("INSERT INTO devices (id,tech,dial,devicetype,user,description,emergency_cid) VALUES ('$exten','sip','SIP/$exten','fixed','$exten','$ucname','')");
$db->do("UPDATE admin SET value = 'true' WHERE variable = 'need_reload'"); #Cause the Apply Changes button to appear in FreePBX
$db->disconnect;
# Now create voicemail box
$vmailnotexist = 1;
open(FILEHANDLE, $Voicemail) or die "Error opening $Voicemail: $!";
while (<>)
{
if (/(.*) =>/){ #if the voicemail box already exists, skip it
$vmailnotexist = 0;
break;
}
}
close FILEHANDLE;
if ($vmailnotexist) {
$timezone = "tz=" . lc($timezone . "|");
open(FILEHANDLE, ">>$Voicemail") or die "Error opening $Voicemail: $!";
print FILEHANDLE $exten," => ",$exten,",",$ucname,",,,",$timezone,"attach=no|saycid=yes|envelope=yes|delete=no\n";
close FILEHANDLE;
}
}
print " Success!
";
}
print "
Note: You must now go into FreePBX, edit each extension, change CallWaiting to Enabled, click Submit, and then Apply Changes.
thats pretty cool Tim, I put
Mon, 12/17/2007 - 7:20pm
thats pretty cool Tim, I put it in the wiki for quick reference:
http://www.trixbox.org/wiki/additional-scripts-and-tricks
Check FreePBX.org
Mon, 12/17/2007 - 8:27pm
You should check the FreePBX.org forums on this subject as well.
http://www.freepbx.org/forum/mysql-insert-to-add-bulk-sip-users-d...
nice work on the perl scripts
Tue, 12/25/2007 - 9:10pm
What you are missing is the entries into the asteriskDB. This is a Berkley database that asterisk uses to keep track of stuff.
asterisk -rx "database show"
This is a quick command to show you what's in there. You can also use command to add and remove stuff from it.
/CW/200 : ENABLED
This is the entry you are looking for that enables call waiting for example.
Hi,
Is there a detailed
Wed, 04/09/2008 - 5:33am
Hi,
Is there a detailed tutorial and complete script for this topic? Can be done form someone who doesn't now much about perl, php...etc? Please if someone can send me a file whit this script and short description I think I'll handle myself for the rest. Thanks!
Stan
For what its worth...
Fri, 06/20/2008 - 8:19am
Once you have entered the data into the database and restarted amportal, you need to enter the following lines into asterisk:
Either asterisk -r (and then paste the commands in) or if by script, several lines starting asterisk -rx '#commandline#'
The commandlines being:
database put AMPUSER #EXT#/cidname #DISPLAY NAME#
database put AMPUSER #EXT#/cidnum #EXT#
database put AMPUSER #EXT#/device #EXT#
database put AMPUSER #EXT#/recording out=Adhoc|in=Adhoc
database put AMPUSER #EXT#/ringtimer 20
database put AMPUSER #EXT#/voicemail default
database put DEVICE #EXT#/default_user #EXT#
database put DEVICE #EXT#/dial SIP/#EXT#
database put DEVICE #EXT#/type fixed
database put DEVICE #EXT#/user #EXT#
There is a post over in
Fri, 06/20/2008 - 2:01pm
There is a post over in FreePBX that allows you to use the FreePBX API to do what you are trying to accomplish.
This is the best method as it assures you that have all of the dependencies setup. All your extension will properly be integrated into the FreePBX dialplan.
Scott
--
Scott
aka "Skyking"
Member Since:
2007-01-15