Hi, Im new with bash scripting and I want to create a conditional emailing with bash scripting. But it doesn't workout.
the conditional requirement are; if the TextDecoded value from inbox table are not the same with sms_keyword value from kontak_group table then don't email.
It keep emailing whether it meet or not using the requirements above.
Please help, and thank you very much. 
TABLES:
mysql -uroot -p123456 smsd -e "SELECT * FROM kontak_group"
+- -------------+----------+-------------+---------------+
| group_name | group_ID | sms_keyword | email_group |
+---------------+----------+-------------+---------------+
| Information | 1 | IT | it@acme.com |
+---------------+----------+-------------+---------------+
mysql -uroot -p123456 smsd -e "SELECT * FROM inbox"
+-------------+-------------------+---------------+-------------+----+--------------+-----------+-----------+
| UpdatedInDB | ReceivingDateTime | SenderNumber | TextDecoded | ID | status_reply | RecipientID | Processed |
+-------------+-------------------+---------------+-------------+----+--------------+-----------+-----------+
# send to email function
mysql -uroot -p123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox WHERE TextDecoded NOT regexp ( select concat_ws ( '', '^(', group_concat(sms_keyword separator '|'), ').*' ) from kontak_group) ORDER BY ReceivingDateTime DESC LIMIT 1" | mailx -s "$SUBJECT" -a From:$SENDER $EMAIL_ADDRESS
mailx will run as long as there is some output coming thru the pipe regardless of whether or not it is what you expected ... you may want to put the mysql output into a variable, check that variable value and then pipe to mailx if appropriate (code sample below may need your tweaking)...
TEXTD=$(mysql -uroot -p123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox WHERE TextDecoded NOT regexp ( select concat_ws ( '', '^(', group_concat(sms_keyword separator '|'), ').*' ) from kontak_group) ORDER BY ReceivingDateTime DESC LIMIT 1")
if $TEXTD
then
echo $TEXTD | mailx -s "$SUBJECT" -a From:$SENDER $EMAIL_ADDRESS
fi
Hi, thanks for your repply.
I changed the code to this:
status=0
PTMemails="acme@gmail.com"
HCSemails="acme@gmail.com"
PTMSubject="Email to group status - Sent"
HCSSubject="Email to group status - Sent"
CHECKER='mysql -u root --password=123456 smsd -e "select * from inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HCS ADM|HC|IRC|IT|PTM|HCS''
if [ "CHECKER" = "TextDecoded: PTM test filter 19:38" ]; then
message="send email to PTM"
status=1
fi
if [ "CHECKER" = "TextDecoded: HCS test filter 19:38" ]; then
message="send email to HCS"
status=2
fi
if [ $status = 1 ]; then
for address in $PTMemails; do
echo -e $message | mail -s $PTMSubject $address
echo " email sent to PTM contacts"
done
fi
if [ $status = 2 ]; then
for address in $HCSemails; do
echo -e $message | mail -s $HCSSubject $address
echo " email sent to PTM contacts"
done
fi
and give me this output:
+ status=0
+ PTMemails=acme@gmail.com
+ HCSemails=acme@gmail.com
+ PTMSubject=Email to group status - Sent
+ HCSSubject=Email to group status - Sent
+ HCS ADM
kategori.sh: 1: HCS: not found
+ HC
kategori.sh: 1: HC: not found
+ BS
kategori.sh: 1: BS: not found
+ IRC
kategori.sh: 1: IRC: not found
+ IT
kategori.sh: 1: IT: not found
+ CHECKER=mysql -u root --password=gurlia001 smsd -e "select * from inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w ADM
+ PTM
kategori.sh: 1: PTM: not found
+ HCS
: not found: 1: HCS
+
: not found: 1:
kategori.sh: 20: Syntax error: word unexpected (expecting "do")
i'm thinking your mysql command is putting out extraneous info ... see if you can redirect the unneeded info (i.e., CHECKER=$(some_command | some_command 2> /dev/null) ... echo out the value of $CHECKER to make sure are only getting what you need ...
also, check your egrep output and see if HCS or ADM will not show HCS ADM ...
you could also combine your blocks to make the script shorter (see below) ...
PTMemails="acme@gmail.com"
HCSemails="acme@gmail.com"
PTMSubject="Email to group status - Sent"
HCSSubject="Email to group status - Sent"
CHECKER='mysql -u root --password=123456 smsd -e "select * from inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HCS ADM|HC|IRC|IT|PTM|HCS'' ### tweak this line
if [ "CHECKER" = "TextDecoded: PTM test filter 19:38" ]; then
message="send email to PTM"
for address in $PTMemails; do
echo -e $message | mail -s $PTMSubject $address
done
echo " email sent to PTM contacts"
fi
if [ "CHECKER" = "TextDecoded: HCS test filter 19:38" ]; then
message="send email to HCS"
for address in $HCSemails; do
echo -e $message | mail -s $HCSSubject $address
done
echo " email sent to PTM contacts"
fi
changed and echo from $CHECKER :
mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS'
TextDecoded: ADM test filter 19:38
The result are right. I only want the newest row from inbox table with TextDecoded value are starting with the word described from egrep.
changed the whole script:
ADMemails="acme@gmail.com"
HCSemails="acme@gmail.com"
ADMSubject="Email to group status - Sent"
HCSSubject="Email to group status - Sent"
CHECKER='mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS'' ### tweak this line
if [ "CHECKER" = "TextDecoded: ADM*" ]; then
message="send email to ADM"
for address in $ADMemails; do
echo -e $message | mail -s $ADMSubject $address
done
echo " email sent to ADM contacts"
fi
if [ "CHECKER" = "TextDecoded: HCS*" ]; then
message="send email to HCS"
for address in $HCSemails; do
echo -e $message | mail -s $HCSSubject $address
done
echo " email sent to PTM contacts"
fi
gives an error result:
+ ADMemails=acme@gmail.com
+ HCSemails=acme@gmail.com
+ ADMSubject=Email to group status - Sent
+ HCSSubject=Email to group status - Sent
+ IRC
kategori2.sh: 1: IRC: not found
+ HC
kategori2.sh: 1: HC: not found
+ IT
kategori2.sh: 1: IT: not found
+ BS
kategori2.sh: 1: BS: not found
+ PTM
kategori2.sh: 1: PTM: not found
+ HCS
kategori2.sh: 1: HCS: not found
+ CHECKER=mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w ADM
kategori2.sh: 8: Syntax error: word unexpected (expecting "do")
try this one ... btw, i am testing this on ksh on solaris 10 ... works fine here ...
ADMemails="acme@gmail.com"
HCSemails="acme@gmail.com"
ADMSubject="Email to group status - Sent"
HCSSubject="Email to group status - Sent"
CHECKER='mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS' 2> /dev/null'
echo $CHECKER | grep ADM > /dev/null
if [ $? -eq 0 ]
then
message="send email to ADM"
for address in $ADMemails
do
echo -e $message | mail -s $ADMSubject $address
done
echo " email sent to ADM contacts"
fi
echo $CHECKER | grep HCS > /dev/null
if [ $? -eq 0 ]
then
message="send email to HCS"
for address in $HCSemails
do
echo -e $message | mail -s $HCSSubject $address
done
echo " email sent to PTM contacts"
fi
Thanks Bro, but it doesn't work out. I already tried use your script. btw, Im using debian squeeze.
The result are:
+ ADMemails=acme@gmail.com
+ HCSemails=acme@gmail.com
+ ADMSubject=Email to group status - Sent
+ HCSSubject=Email to group status - Sent
+ IRC
senin.sh: 1: IRC: not found
+ HC
senin.sh: 1: HC: not found
+ IT
senin.sh: 1: IT: not found
+ BS
senin.sh: 1: BS: not found
+ PTM
senin.sh: 1: PTM: not found
+ HCS 2> /dev/null
senin.sh: 1: HCS 2> /dev/null: not found
+ CHECKER=mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w ADM
+ grep ADM
+ echo
+ [ 1 -eq 0 ]
+ grep HCS
+ echo
+ [ 1 -eq 0 ]
still theres no mail receive from it.
You're getting all those command not found errors because the pipes in the egrep pattern occur outside a quoted string. The shell is interpreting them and searching for the executables that match what it considers command names.
Further, unless my sight is starting to fail completely, I see nothing but a garbled variable assignment to CHECKER. There is no command substitution, so even if everything were quoted correctly, mysql will never execute.
Regards,
Alister
1 Like
Dear Alister, thanks for your repply.
variable assigned to CHECKER are right:
mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS'
the result are:
TextDecoded: ADM test filter 19:38
But I'm confuse in bash scripting for conditional email with mysql.
i was just looking at this again and not seeing the mysql comand output that you showed as an example ... it seems your issue is that your script does not run the mysql command like alister said though not because of your command ... it looks like you are using single quotes instead of backticks when setting the CHECKER variable ... i also added the error redirect to /dev/null so you see less errors ...
change this ...
CHECKER='mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS''
to this ...
CHECKER=$(mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS' 2> /dev/null)
or this ...
CHECKER=`mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS' 2> /dev/null`
1 Like
hmm.. now i understand what Alister said.
Thanks Just Ice. ok now I can run the code with:
CHECKER=$(mysql -u root --password=123456 smsd -e "SELECT ID, SenderNumber, TextDecoded FROM inbox ORDER BY ReceivingDateTime DESC LIMIT 1 \G" |egrep -w 'ADM|BS|HC|IRC|IT|PTM|HCS' 2> /dev/null)
adding more knowledge to my brain. error redirect using /dev/null so I can see less errors
Thank you so much brother Just Ice and Alister for helping me.