MySQL Macros for Asterisk RealTime

If you are using Asterisk Realtime, here are some macros that will help with performing different functions in the dialplan.

Page All Phones


This will Page all extensions in the mysql database that begin with a specify number. It will also allow you to pass an additional device that is used for paging an overhead PA system. When the macro is called it will generate a Page command that resembles:

exten => s,1,Page(SIP/6001&SIP/6002&SIP/6003)

You can also set the recording file for the Page, which will record the page to the file specified.

You will need to copy the following Macro into extensions.conf, and create a "paging" directory in /var/lib/asterisk/sounds for the audio recording Note that I used "sip" as the name of my table where the sip peers/users are kept. You will need to edit the Macro and change sip to whatever you have named your table (exten s,3).:

[globals]
; Asterisk RealTime Database Connection Settings
; Use these variables to specify the connection to the Asterisk
; Realtime Database.
realdb_host=localhost
realdb_user=asterisk
realdb_pass=password
realdb_db=asterisk_realtime

[macro-pageall]
; Context for paging all devices.
; This will search the sip table in the realtime database
; for all phones that start with a number. That number is
; passed to this macro as ${ARG1}.
;
; ARG1 = The first digit of the phones to be paged.
; This can also be the first 2 or 3 digits.
; ARG2 = Device for the PA system. If the user selected to
; page the PA system. That will be included.
;
exten => s,1,Set(MEETME_RECORDINGFILE=custom/paging/campuslastpage_${RAND(1|100)})
exten => s,2,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,3,MYSQL(Query resultid ${connid} SELECT\ name\ FROM\ sip\ WHERE\ name\ LIKE\ "'${ARG1}%'")
exten => s,4,MYSQL(Fetch fetchid ${resultid} number)
exten => s,5,GoToIf($["${fetchid}" = "1"]?6:8)
exten => s,6,Set(pagedevice=${pagedevice}&SIP/${number})
exten => s,7,GoToIf($["${fetchid}" = "1"]?4:8)
exten => s,8,Set(pagedevice=${pagedevice:1})
exten => s,9,MYSQL(Clear ${resultid})
exten => s,10,MYSQL(Disconnect ${connid})
exten => s,11,GoToIf($["${ARG2}" != ""]?12:13)
exten => s,12,Set(pagedevice=${pagedevice}&${ARG2})
exten => s,13,SIPAddHeader(Call-Info:answer-after=0)
exten => s,14,SIPAddHeader(Alert-Info: Ring Answer)
exten => s,15,NoOp(${MEETME_RECORDINGFILE})
exten => s,16,Set(CALLERID(all)=System Page <1010>)
exten => s,17,Page(${pagedevice},r)

Group Voicemail in Asterisk


This macro will allow you to assign a named group to a user in the MySQL table. You should have your tables setup as shown in the wiki for RealTime. Note that I used "voicemail_users" as the name of my table where the voicemail users are kept. You will need to edit the Macro and change voicemail_users to whatever you have named your table.

You will then need to add an additional column to you voicemail_users table. Create a new column named customgrouplist as varchar.

ALTER TABLE test ADD customgrouplist VARCHAR(6000)

Now add the following Macro to extensions.conf:

[globals]
realdb_host=hostnameformysqldb
realdb_user=mysqldbuser
realdb_pass=mysqldbpassword
realdb_db=mysqldbthatcontainsthevoicemailusers

[macro-groupvoicemail]
; This macro will search the voicemail users table in the realtime database
; for the group specified as ARG1. The group is looked for in the customgrouplist
; column.
; Call the macro with two arguments.
; ARG1 = The group to search for in the customgrouplist column
; ARG2 = The voicemail context to append to the mailboxes found.
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ mailbox\ from\ voicemail_users\ where\ customgrouplist\ LIKE\ “‘%${ARG1}%’”)
exten => s,3,MYSQL(Fetch fetchid ${resultid} mailbox)
exten => s,4,GoToIf($[”${fetchid}” = “1″]?5:8)
exten => s,5,Set(grouplist=${grouplist}&${mailbox}@${ARG2})
exten => s,6,NoOp(${mailbox})
exten => s,7,GoToIf($[”${fetchid}” = “1″]?3:8)
exten => s,8,Set(grouplist=${grouplist:1})
exten => s,9,MYSQL(Clear ${resultid})
exten => s,10,MYSQL(Disconnect ${connid})
;This next line adds the dummy voicemailbox as the extension that was dialed to call the macro
;The u flag will tell asterisk to use the unavailable message for the first mailbox listed,
;our dummy.
exten => s,11,VoiceMail(${MACRO_EXTEN}@${ARG2}&${grouplist},u)

Next create a voicemailbox in your table that has hidefromdir=yes and delete=yes (no email address added). This is a dummy voicemail box. Only the unavailable message from this voicemail box will get played. Because delete=yes, no voicemails will get stored here.

insert into `asterisk_realtime`.`voicemail_users` ( `mailbox`, `password`, `fullname`, `delete`, `hidefromdir`, `stamp`) values ( '1801', '1234', 'Group VoiceMail for Sales', 'yes', 'yes', null)

In your new column named customgrouplist add some unique group names to some voicemail boxes. Separate multiple groups with a space.

sales managers

Now all that is left is to call the Macro with the correct arguments.

; Group voicemail list.
; ARG1 = Group to Search for in the customgrouplist column
; ARG2 = VoiceMail Context the users are located in.
;
; Send a VoiceMail to everyone in the sales group.
exten => 1801,1,Macro(groupvoicemail,sales,company1)
exten => 1801,2,Hangup()

Check incoming CALLERID for Blacklist


This is a simple way to keep a blacklist of phone numbers that you don't want to allow to call you/your company. The Macro will quickly check a MySQL database for the incoming callerid. If the callerid exist and the block is enabled, then the caller will get placed into the [blacklistednumber] context.

Create a new table in you MySQL RealTime connection.

CREATE TABLE `bit_blacklist` (
`id` int(11) NOT NULL auto_increment,
`callerid` varchar(10) NOT NULL default '',
`blockenabled` char(1) NOT NULL default '1',
`notes` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `blacklist` values('1','5556671212','1','Spammer');

The blockenabled column should equal 1 to enable the block. Set this to 0 (zero), if you wish to disable blocking the number.

Insert the following Macro and context into your dial plan.

[globals]
realdb_host=hostnameformysqldb
realdb_user=mysqldbuser
realdb_pass=mysqldbpassword
realdb_db=mysqldbthatcontainsthevoicemailusers

[macro-checkblacklist]
; This Macro will check the blacklist table to see if the callerid of the
; caller exist and blockenabled =1 (TRUE). If the callerid is listed, then
; tell the caller they have been blacklisted and politely HangUp()
;
; ${ARG1} = CallerID of incoming call
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ callerid\ from\ blacklist\ where\ callerid=${ARG1} and blockenabled = 1)
exten => s,3,MYSQL(Fetch fetchid ${resultid} blacklistid)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Disconnect ${connid})
exten => s,6,GoToIf($["${blacklistid}" = ""]?7:fail,1)
exten => s,7,NoOp(${blacklistid})
; If the callerid is listed in the database, then send to blacklistednumber
; context
;
exten => fail,1,NoOp(${blacklistid})
exten => fail,2,GoTo(blacklistednumber,s,1)

[blacklistednumber]
; This is where a call will land if the macro-checkblacklist decides that
; the number should not be allowed to dial the company.
exten => s,1,Wait(2)
exten => s,2,Playback(privacy-you-are-blacklisted)
exten => s,3,Zapateller()
exten => s,4,HangUp()

The last step is to call the macro when a call arrives. On the context for your zap channels, add this line:

[zaptel-incoming]
;Incoming context for PRI.
;
exten => 8005551193,1,Macro(checkblacklist,${CALLERID(num)})
exten => 8005551193,2,Dial(SIP/Phone)

If you are using Asterisk Realtime, here are some macros that will help with performing different functions in the dialplan.

Page All Phones


This will Page all extensions in the mysql database that begin with a specify number. It will also allow you to pass an additional device that is used for paging an overhead PA system. When the macro is called it will generate a Page command that resembles:

exten => s,1,Page(SIP/6001&SIP/6002&SIP/6003)

You can also set the recording file for the Page, which will record the page to the file specified.

You will need to copy the following Macro into extensions.conf, and create a "paging" directory in /var/lib/asterisk/sounds for the audio recording Note that I used "sip" as the name of my table where the sip peers/users are kept. You will need to edit the Macro and change sip to whatever you have named your table (exten s,3).:

[globals]
; Asterisk RealTime Database Connection Settings
; Use these variables to specify the connection to the Asterisk
; Realtime Database.
realdb_host=localhost
realdb_user=asterisk
realdb_pass=password
realdb_db=asterisk_realtime

[macro-pageall]
; Context for paging all devices.
; This will search the sip table in the realtime database
; for all phones that start with a number. That number is
; passed to this macro as ${ARG1}.
;
; ARG1 = The first digit of the phones to be paged.
; This can also be the first 2 or 3 digits.
; ARG2 = Device for the PA system. If the user selected to
; page the PA system. That will be included.
;
exten => s,1,Set(MEETME_RECORDINGFILE=custom/paging/campuslastpage_${RAND(1|100)})
exten => s,2,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,3,MYSQL(Query resultid ${connid} SELECT\ name\ FROM\ sip\ WHERE\ name\ LIKE\ "'${ARG1}%'")
exten => s,4,MYSQL(Fetch fetchid ${resultid} number)
exten => s,5,GoToIf($["${fetchid}" = "1"]?6:8)
exten => s,6,Set(pagedevice=${pagedevice}&SIP/${number})
exten => s,7,GoToIf($["${fetchid}" = "1"]?4:8)
exten => s,8,Set(pagedevice=${pagedevice:1})
exten => s,9,MYSQL(Clear ${resultid})
exten => s,10,MYSQL(Disconnect ${connid})
exten => s,11,GoToIf($["${ARG2}" != ""]?12:13)
exten => s,12,Set(pagedevice=${pagedevice}&${ARG2})
exten => s,13,SIPAddHeader(Call-Info:answer-after=0)
exten => s,14,SIPAddHeader(Alert-Info: Ring Answer)
exten => s,15,NoOp(${MEETME_RECORDINGFILE})
exten => s,16,Set(CALLERID(all)=System Page <1010>)
exten => s,17,Page(${pagedevice},r)

Group Voicemail in Asterisk


This macro will allow you to assign a named group to a user in the MySQL table. You should have your tables setup as shown in the wiki for RealTime. Note that I used "voicemail_users" as the name of my table where the voicemail users are kept. You will need to edit the Macro and change voicemail_users to whatever you have named your table.

You will then need to add an additional column to you voicemail_users table. Create a new column named customgrouplist as varchar.

ALTER TABLE test ADD customgrouplist VARCHAR(6000)

Now add the following Macro to extensions.conf:

[globals]
realdb_host=hostnameformysqldb
realdb_user=mysqldbuser
realdb_pass=mysqldbpassword
realdb_db=mysqldbthatcontainsthevoicemailusers

[macro-groupvoicemail]
; This macro will search the voicemail users table in the realtime database
; for the group specified as ARG1. The group is looked for in the customgrouplist
; column.
; Call the macro with two arguments.
; ARG1 = The group to search for in the customgrouplist column
; ARG2 = The voicemail context to append to the mailboxes found.
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ mailbox\ from\ voicemail_users\ where\ customgrouplist\ LIKE\ “‘%${ARG1}%’”)
exten => s,3,MYSQL(Fetch fetchid ${resultid} mailbox)
exten => s,4,GoToIf($[”${fetchid}” = “1″]?5:8)
exten => s,5,Set(grouplist=${grouplist}&${mailbox}@${ARG2})
exten => s,6,NoOp(${mailbox})
exten => s,7,GoToIf($[”${fetchid}” = “1″]?3:8)
exten => s,8,Set(grouplist=${grouplist:1})
exten => s,9,MYSQL(Clear ${resultid})
exten => s,10,MYSQL(Disconnect ${connid})
;This next line adds the dummy voicemailbox as the extension that was dialed to call the macro
;The u flag will tell asterisk to use the unavailable message for the first mailbox listed,
;our dummy.
exten => s,11,VoiceMail(${MACRO_EXTEN}@${ARG2}&${grouplist},u)

Next create a voicemailbox in your table that has hidefromdir=yes and delete=yes (no email address added). This is a dummy voicemail box. Only the unavailable message from this voicemail box will get played. Because delete=yes, no voicemails will get stored here.

insert into `asterisk_realtime`.`voicemail_users` ( `mailbox`, `password`, `fullname`, `delete`, `hidefromdir`, `stamp`) values ( '1801', '1234', 'Group VoiceMail for Sales', 'yes', 'yes', null)

In your new column named customgrouplist add some unique group names to some voicemail boxes. Separate multiple groups with a space.

sales managers

Now all that is left is to call the Macro with the correct arguments.

; Group voicemail list.
; ARG1 = Group to Search for in the customgrouplist column
; ARG2 = VoiceMail Context the users are located in.
;
; Send a VoiceMail to everyone in the sales group.
exten => 1801,1,Macro(groupvoicemail,sales,company1)
exten => 1801,2,Hangup()

Check incoming CALLERID for Blacklist


This is a simple way to keep a blacklist of phone numbers that you don't want to allow to call you/your company. The Macro will quickly check a MySQL database for the incoming callerid. If the callerid exist and the block is enabled, then the caller will get placed into the [blacklistednumber] context.

Create a new table in you MySQL RealTime connection.

CREATE TABLE `bit_blacklist` (
`id` int(11) NOT NULL auto_increment,
`callerid` varchar(10) NOT NULL default '',
`blockenabled` char(1) NOT NULL default '1',
`notes` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `blacklist` values('1','5556671212','1','Spammer');

The blockenabled column should equal 1 to enable the block. Set this to 0 (zero), if you wish to disable blocking the number.

Insert the following Macro and context into your dial plan.

[globals]
realdb_host=hostnameformysqldb
realdb_user=mysqldbuser
realdb_pass=mysqldbpassword
realdb_db=mysqldbthatcontainsthevoicemailusers

[macro-checkblacklist]
; This Macro will check the blacklist table to see if the callerid of the
; caller exist and blockenabled =1 (TRUE). If the callerid is listed, then
; tell the caller they have been blacklisted and politely HangUp()
;
; ${ARG1} = CallerID of incoming call
;
exten => s,1,MYSQL(Connect connid ${realdb_host} ${realdb_user} ${realdb_pass} ${realdb_db})
exten => s,2,MYSQL(Query resultid ${connid} SELECT\ callerid\ from\ blacklist\ where\ callerid=${ARG1} and blockenabled = 1)
exten => s,3,MYSQL(Fetch fetchid ${resultid} blacklistid)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Disconnect ${connid})
exten => s,6,GoToIf($["${blacklistid}" = ""]?7:fail,1)
exten => s,7,NoOp(${blacklistid})
; If the callerid is listed in the database, then send to blacklistednumber
; context
;
exten => fail,1,NoOp(${blacklistid})
exten => fail,2,GoTo(blacklistednumber,s,1)

[blacklistednumber]
; This is where a call will land if the macro-checkblacklist decides that
; the number should not be allowed to dial the company.
exten => s,1,Wait(2)
exten => s,2,Playback(privacy-you-are-blacklisted)
exten => s,3,Zapateller()
exten => s,4,HangUp()

The last step is to call the macro when a call arrives. On the context for your zap channels, add this line:

[zaptel-incoming]
;Incoming context for PRI.
;
exten => 8005551193,1,Macro(checkblacklist,${CALLERID(num)})
exten => 8005551193,2,Dial(SIP/Phone)

Created by: forrest.beck, Last modification: Fri 05 of Nov, 2010 (23:11 UTC) by admin
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+