Conditional bash/mysql query help


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Conditional bash/mysql query help
# 1  
Old 03-02-2015
Conditional bash/mysql query help

I think(hope) I've got a simple one - I just need to send an email if a mysql query returns any results (ideally - it will never match).

Currently I just pipe the mysql query output to the mail program, but of course that emails regardless of the output( and I check this every 10 minutes from cron, so that's a lot of mail when it's empty).

I only want to send mail (the actual mysql output) if there is anything returned from the query.

Here is my query - ideally it will never find any messages that meet those conditions, but if it does, I need an email sent with the results of the query.

Code:
mysql -e 'select new_messages,FROM_UNIXTIME(last_purge_at)
  AS 'last_purge_at',comment
  FROM zimbra.mailbox
  WHERE new_messages > 0
  AND last_purge_at < DATE_SUB(NOW(),INTERVAL 10 MINUTE)' \
  | mail -s "Zimbra email overdue" user@email.com

Anyone got any suggestions? Thanks in advance

Last edited by rbatte1; 03-02-2015 at 01:08 PM.. Reason: Split up long line to make it more readable
# 2  
Old 03-02-2015
Show an example of output when it does and when it doesn't find results.
# 3  
Old 03-02-2015
You might be better to catch the output into a file and then decide if you need to send it. Your code may well return a No rows found type message and email it every run whether there is anything interesting or not. It might even cause you problems if you try to send nothing.
  • What output do you get from the mysql command?
  • What is the condition to send or not?



Regards,
Robin
# 4  
Old 03-02-2015
Here is an example when it finds something:

+--------------+---------------------+-----------------------+
| new_messages | last_purge_at | comment |
+--------------+---------------------+-----------------------+
| 3 | 2015-03-02 11:41:55 | email@something.com |
+--------------+---------------------+-----------------------+

When it returns nothing, I get no kind of results - just a new prompt.
# 5  
Old 03-03-2015
You might be having a problem with the quoting too. Your command has a single quoted input to the mysql but then you single quote within that. You might need to use double quotes for the mysql command or escape all the single quotes.

If you are getting output from your command as it is, then catch it to a file instead of trying to pipe it to the mail command. You can then use the shell test to see if the file contains anything:-
Code:
mysql -e ............ whatever you already have > /tmp/new_msgs
if [ -s /tmp/new_msgs ]
then
   mail -s "An e-mail subject" user@email.com < /tmp/new_msgs
fi


Does this help?


Robin
This User Gave Thanks to rbatte1 For This Post:
# 6  
Old 03-03-2015
Thanks Robin, this looks like it would work perfectly (and thanks for mentioning the dual single-quotes).

I will test this out and let you know.

---------- Post updated at 03:28 PM ---------- Previous update was at 10:16 AM ----------

FYI, this solution works perfectly. Thanks again for all your help.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Programming

Need help in mysql query

Hi All, i have a table in mysql with the following data Table name Test Assettype Serial_No Status location Mouse 123456 In Stock chennai Mouse 98765 Allocated chennai Keyboard ... (2 Replies)
Discussion started by: venkitesh
2 Replies

2. Shell Programming and Scripting

Conditional emailing with mysql query

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... (10 Replies)
Discussion started by: jazzyzha
10 Replies

3. Programming

mysql query help

Hello i have created mysql query to compare to values and get difference in percentage as following: SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Discussion started by: mogabr
1 Replies

4. Web Development

mysql query help

hello all i have 2 columns every column in the following format column1 2011-04-01 11:39:54 column2 2019-02-03 00:00:00 i want get difference between above data as following 2 days 11:39 how to do so ? i tried many functions but nothing works please advice what is the query... (6 Replies)
Discussion started by: mogabr
6 Replies

5. Web Development

Complex MySQL Query(s)

Hi all, I have a bit of an issue, I am working on a bit of a CMDB for a friend, it's to do with real estate. Anyway, this is my situation. I have a table which contains all the properties (forsale, sold, etc) in the DB named "property". Now, this is what I want to achieve, I wish to... (5 Replies)
Discussion started by: STOIE
5 Replies

6. Shell Programming and Scripting

bash assign mysql query single field to variable

I'm running a bash script query and assigning the output to a variable like this: exists=`mysql -u $USER_NAME --password=$PASSWORD -D "somedb" \ -e "SELECT * FROM somedb.sometable WHERE field1 ='$a' \ AND field2 ='$b' LIMIT 0 , 30";` which returns something like: echo... (2 Replies)
Discussion started by: unclecameron
2 Replies

7. Shell Programming and Scripting

mysql help : query with 2 conditionals

Hi there, I have a table that stores multiple records for many different servers, each of which is timestamped ... I wanted to write a query that would enable me to only output the "latest" record (based on timestamp) for each "unique" server. So for example my main table looks like this ... (3 Replies)
Discussion started by: hcclnoodles
3 Replies

8. Programming

How to query one to many mysql

Hi there, I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks! YanYan (0 Replies)
Discussion started by: pinkgladiator
0 Replies
Login or Register to Ask a Question