Condition using script in dialplan and check MySQL database

Asterisk Asterisk Support
1

I’m trying to make dialplan with condition base on mysql response. What I want to achieve is when user call to his voicemail script to check if there are any messages left to him/her. If no messages goto other menu or another menu.

So I have this simple shell script check.sh

#!/bin/bash
# Check if user has any messages left to him

count=$(mysql -u mysql -pMYPASS -h xxx.xxx.xxx.xxx asterisk -se "select count(mailboxuser) from voicemessages where mailboxuser=8785;")

if [ $count -gt 0 ]
then
     echo " greater that 0 "
else
     echo " lower than 0 "
fi

And the dialplan so far

[internal]
exten => 119,1,Set(CHANNEL(language)=en)
same => n, System(check.sh ${CALLERID})
same => n,VoicemailMain(${CALLERID(num)}@VoiceMail)
same => n,Hangup

When I call to 119 I see in console this

-- Executing [119@internal:1] Set("SIP/8785-00000058", "CHANNEL(language)=en") in new stack
-- Executing [119@internal:2] System("SIP/8785-00000058", "check.sh 8785") in new stack
-- Executing [119@internal:3] VoiceMailMain("SIP/8785-00000058", "8785@VoiceMail") in new stack

With the query I just want to check if greater than 0. If is greater this means that the user has 1+ messages. Question is how to grab the response and construct properly if/then/gotoif condition in this dialplan. First time dealing with this and I’m not sure how to make it.

When I run it in console I’ve got response like this which is correct

*CLI> !/var/lib/asterisk/agi-bin/check.sh
 lower than 0
2

There is a function with System-like behaviour. You need t use that, rather than System.

You should also run something like FILTER over the caller ID, to avoid a scripting injection attack.

Also, I believe you can do this directly to SQL, without the shell script.

3

Yes as @david551 mentioned you can do this by calling into the database directly from the dialplan. See the func_odbc.conf.sample file for an example. Once you’ve declared your section/SQL in func_odbc.conf you can reference it from the dialplan using something similar to the below:

same => n,Set(<variable>=${ODBC_<section from configuration>(<argument[s]>)})

So, using the example from the sample file, if you wanted to retrieve and store the presence in variable “MYPRESENCE” you’d do the following in the dialplan:

same => n,Set(MYPRESENCE=${ODBC_PRESENCE(${EXTEN})})
4

This might be even simpler, If you are using the built in asterisk app_voicemail there is a function that will count the number of voicemails in a box.

https://wiki.asterisk.org/wiki/display/AST/Asterisk+14+Function_VMCOUNT

6

Okay lets say I’ve make it like this. After the same => n,Set(MYPRESENCE=${ODBC_PRESENCE(${EXTEN})}) how to catch if is there this user or no… and how to proceed to next contexts ?

7

Whether you use the ODBC method or the aforementioned VM_COUNT function you’ll have the count in a variable. At that point there are various ways to switch off a condition or jump to a specified place in the dialplan. For instance see:

Conditional Applications
ExecIf
GosubIf
GotoIf
MacroIf
IF

So for instance if you stored the voicemail count in variable COUNT then you could do something like the following:

....
same => n,GotoIf($[${COUNT} > 0]?has_mail)                                                                                                                          
same => n,Verbose(No mail available)                                                                                                                                
same => n,Hangup()                                                                                                                                                  
same => n(has_mail),Verbose(Num mail messages ${COUNT})                                                                                                             
same => n,Hangup()
2 Likes
8

Edit:
It’s works fine. Thanks for the help!