SQL to extract the VM Email Address for a given Extn


#1

Please does anyone know how/where to get the ‘Voicemail to Email Address’ field for a given Extn (as set on the ‘Voicemail’ tab of an Extension) ?

I have the following SQL to get various Extension details, including the ‘[users].[voicemail]’ field that seems to indicate whether VM is enabled for a given Extn, but NOT what email address it might be forwarded to (This is NOT the same as the Linked Users email address within ‘[userman_users].[email]’).

mysql asterisk -e "SELECT devices.id AS Extn_No, devices.tech AS Extn_Tech, devices.description AS Extn_Desc, users.voicemail AS Extn_Voicemail FROM devices LEFT JOIN users ON devices.id = users.extension WHERE ((devices.description NOT LIKE 'Zulu%') AND (devices.description NOT LIKE 'WebRTC%')) ORDER BY devices.id;"

I cannot see the actual VM email address in either the ‘devices’ or ‘users’ table, neither can I see an obvious other DB table that might contain the Voicemail fields relating to a given Extn.

Any assistance would be greatly appreciated.


(Andrew) #2

They are stored in /etc/asterisk/voicemail.conf


#3

I see them, thanks for the heads-up.


#4
grep "^${EXTEN}=" /etc/asterisk/voicemail.conf|cut -d ',' -f3

#5

Ooh, that’s handy. A bit of Googl’ing tells me that returns the 3rd ‘field’ of the line (ie. Email Address) using comma as the delimiter - nice.

Slightly off this topic but related - My instinct is to grep the VM Email address (as nicely demonstrated above) while looping thru an Extns List dataset returned by the earlier quoted SQL query, however, I did this within a shell script recently (for another purpose) & ran into the issue of ‘Process Substitution’ (ie. nothing assigned within the do loop was visible outside of it) - See example below;

MySqlOutput=$(mysql asterisk -e "$MyExtnListSQL")
MySqlOutputCount=`expr "${MySqlOutput}" | wc -l`
#echo "SQL_OutputCount = "$MySqlOutputCount"  (1st Row is Column Headings)"
MySqlOutputRowNo=0
MySqlRowNo=0
MySqlRowNoLast=`expr $MySqlOutputCount - 1`
echo "$MySqlOutput" | while read Extn_No Extn_Tech Extn_Desc Extn_Voicemail
do
	MySqlOutputRowNo=`expr $MySqlOutputRowNo + 1`
	if [ $MySqlOutputRowNo -gt 1 ]
	then
		MySqlRowNo=`expr $MySqlRowNo + 1`
		#-
		#echo $MySqlRowNo" of "$MySqlRowNoLast" --> ExtnNo: "$Extn_No" (Tech: "$Extn_Tech")"
		#-
		MyExtnNo=$Extn_No
		MyExtnTech=$Extn_Tech
		#-
		if [ $Extn_Voicemail -eq 'default' ]
		then
			MyExtnVmEmailAddr=$(grep "^${MyExtnNo}=" /etc/asterisk/voicemail.conf | cut -d ',' -f3)
		fi
	fi		
done
echo "==> "$MySqlRowNoLast" Extns Found"

Do you have any tips to get around this ? (ie. How to populate additional values within the do loop & use them after the loop has completed).


#6

First of I would first settle on $(...) instead of backquotes `…` because you can ‘nest’ them

hard to comment on the rest without a sample of ${MySqlOutput} but you should likely remove column headers and the grid with -sN.

cat  /etc/asterisk/voicemail.conf|cut -d ',' -f1,3|grep -v device|grep @|sed -e 's/=.*,/ /' -e 's/^/(/' -e 's/$/)/'

returns a list of extensions that have vmail addresses ready to use as an array


#7

The backquotes are a throwback some very early tinkering with inc/dec counters I did in shell script some years ago. As this isn’t a scripting language I’m particularly familiar with, I’ve just copy/pasted/reused those lines here, I will be sure to update them.

As for assigning values within the do loop (in order to use them outside of it later) without hitting the ‘Process Substitution’ issue, it’s something that only came up in a similar script/code loop I worked on recently. I got around it by doing everything within the loop on that occasion. I’ll have to make time to revisit the issue & come back with something more specific.

Thanks for your assistance.


#8

Inc/dec counter is as simple with

(( counter++ ))

  and 

(( counter-- ))

I suggest using zsh over bash because it does strings and math and arrays so much better.


#9

Thanks for the assistance so far, I’ve spent some time working up my script to extract a bunch of Extn related values & have run into another issue.

I’m trying to extract a couple of values from ‘pjsip.endpoint.conf’ relating to a given Extn. The settings in this file are repeated in sections for each Extn & so I believe I need to 1st extract just the Section relating to the Extn I want, then grep out the line for the specific setting within that. The closest I’ve got to this is as follows;

strStart="[403]"
strEnd="["
MyExtractedVar=$(sed -n “/^$strStart/,/^$strEnd/p” /etc/asterisk/pjsip.endpoint.conf | grep “mailboxes=”)
echo "VMMailboxes: "$MyExtractedVar

I have two problems with the above that I haven’t been able to figure out;

  1. The square brackets in my start & end strings have special meaning within the sed command & need escaping in order to be treated as normal chars, but I don’t know/can’t workout the correct syntax for this.
  2. If I use start & end strings that don’t contain square brackets to extract a section of the file, the code sort of works, however, it get every instance of the setting I want (‘mailboxes’ in this example) from the start of the extracted section to the end of the file (rather than the 1 instance to the end of the extracted section).

Please could someone offer the correct code/syntax to extract settings from conf files that are structured in this way ?


#10

Without getting too deep, grep is an acronym for “Global regular expression print” as such you will need to need to understand ‘regular expressions’ and so the difference between single and double quoting characters with ‘special meaning’ some circumstances need them to be ‘escaped’ as an example, to ‘grep’

Start="[403]"

you would need to

grep "^Start=\"\[403\]\""

which 'anchors at the beginning ( ^) Start= an escaped double quoted string while escaping an otherwise group match of one of any of 4 0 or 3 characters. All this because (){}[] are used for various ‘groupings’ and '"`$ for ‘escapings’ , escaping $ in single or double quotes or back-ticks gets even more fun and escaping the escape character in a single quoted string in an otherwise escaped string in a double quoted string even more of a brain f*&k. I suggest you just get very familiar with regexes before expecting them to do what you want and break the problem down step by step:-)


#12

Well, thanks again for your help - The syntax for ‘escaping’ the square brackets was the bit I was missing. The following code now seems to work a treat;

MyStartString="\[403\]"
MyEndString="\["
MyReturnedValue=$(sed -n “/^$MyStartString/,/^$MyEndString/p” /etc/asterisk/pjsip.endpoint.conf | grep “mailboxes=” | cut -d ‘=’ -f2)
echo "Mailboxes Value for Extn 403 = "$MyReturnedValue

My Brain is still scrambled by regexes & escaping special chars but in this instance, I appear to have got what I needed :+1:


(system) closed #13

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.