[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Trouble with a script emailing too much

I have a script that is supposed to email me based on certain criteria. It runs as a cron job The first criteria is that the task sent to our file converter fails (is a field value in the database). This part works fine. The second criteria is that a task has been stuck on converting for more than half an hour. This isnt working. I get emailed when the cron job runs and a task is converting at the time- it doesnt seem to check that it has been converting for half an hour. Consequently, i get loads of emails for tasks that are converting as per normal.The script is as follows:

include ("include.php");

// CASE: Failed file conversion.
// ACTION: Alert SysAdmin.

echo "CASE: ConvertFailed or Converting for too long.<hr>";

// this email is cc'd to admins
$SysAdminEmail = "sebastiz@mail.com";

$Values = 't.TaskID ';
$Table = 'Task t';
$Where = "t.TimePosted > NOW() - INTERVAL 60 MINUTE AND (t.TaskStatus = 'ConvertFailed' OR t.TaskStatus = 'Converting' AND t.LastUpdated > NOW() - INTERVAL 30 MINUTE)";

$Results = $mySQL->dbGetValueWhere($myDb,$Values,$Table,$Where);
      foreach($Results as $Result){
            $TaskID = $Result[TaskID];
              $SystemEmail = $Result[Email];

            $to = $SystemEmail;
            // send email
            $body ="";
            $body .="This task $TaskID is in trouble.\n\n";
            $myMail->Priority = 1;
            $myMail->Body = $body;
            $myMail->Subject = '';
            $myMail->From ="sysadmin@mail.com";
            $myMail->FromName ="mail System monitor";

            // Add the SysAdmin
            $myMail->AddCC($SysAdminEmail); // works with SendMail

                  echo "There has been a mail error sending to " . $to . "<br>";

            // Clear all addresses and attachments for next loop

      }//end for each
 }// end is_array
1 Solution
Looks like a problem with your query.  According to your query, you want to select everything that was posted in the last 60 minutes and has a status of "ConvertFailed" or "Converting" with a LastUpdated time in the last 30 minutes (that is what > NOW() - INTERVAL 30 MINUTE would mean).  The last part of your query appears to be the problem to me, as I think you would want it to retrieve items that have NOT been updated in the last 30 minutes (meaning they are stuck, timed out, etc.).  Try this:

$Where = "t.TimePosted > NOW() - INTERVAL 60 MINUTE AND (t.TaskStatus = 'ConvertFailed' OR t.TaskStatus = 'Converting' AND t.LastUpdated < NOW() - INTERVAL 30 MINUTE)";

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now