How to build an email alert notification when a customer is using all his quota space

Discussion in 'Tips/Tricks/Mods' started by voltron81, Sep 8, 2009.

  1. voltron81

    voltron81 New Member

    Hello to everybody,
    I'm realizing a mail server with ISPConfig 3 and Roundcube.

    I would like to realize an "email alert notification" to send to a customer that is using almost all his email space.

    Do you have some idea how to realize it?

    Thanks

    Michele
     
  2. voltron81

    voltron81 New Member

    I've found a script that check the quota of each email and, if it's more that 90%, it will send an email to me and the customer.

    But I think that having this funcion integrated in ISPConfig could be just great...

    Any idea?

    Thanks
    Michele
     
  3. voltron81

    voltron81 New Member

    I was studing a bit the problem, and I realized that into the dbispconfig database there is a table names mail_user, where there is a quota field.

    So I think that the logic way to solve the problem is realize a script that check that quota field for every email and, when this quota is 80% that the space assigned to the customers, just send an email to them saying that the space is almost finished.
    Do you think it is difficult to realize?
    Any idea?

    Thanks
    Michele
     
  4. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Should not be too difficult if you use a scripting language like PHP. :)
     
  5. voltron81

    voltron81 New Member

    Yes, I'm trying to write a PHP script.
    I'm with manuals and books studying what I've to do :eek:

    Basically I've integrated a slider into Roundcube. This slider have 11 steps.
    With ISPConfig I've created 11 spam policy, I now the policy_id of that policy.

    So now what I want to do is connect PHP with MySQL, so that I could realize something like that:
    Code:
    $db_host = "localhost";
            $db_user = "root";
            $db_password = "password";
            $db_name = "dbispconfig";
            $db = mysql_connect($db_host, $db_user, $db_password);
            
            if ($db == FALSE)
            die ("Communication error. Please check the parameters of the connection to the mysql database");
    
            mysql_select_db($db_name, $db)
            or die ("Communication error. Please check the parameters of the connection to the mysql database");
    
            mysql_query("UPDATE spamfilter_users SET policy_id = '$VALUE_SPAM' WHERE email = '$ROUNDCUBE_USER'");
            //where $VALUE_SPAM is the value of the spam policy releated with the slider level
    
            mysql_close($db);
    
    
            $out .= "</table>";
    
            return $out;
    
    At the moment I've 2 problems:
    1) I don't know where to find the $ROUNDCUBE_USER, means the email of the user that at that time is using roundcube
    2)I don't know where this slider is setting his variables releated with the status (0-10)

    Suggestions?
     
  6. mixo

    mixo New Member

    Hello,
    I've ISPConfig 2 and want the same feature.Inform users with mail, when quota is more then 90%.

    Has anybody made such a script?
     
  7. damir

    damir New Member

    This is something i would look to have integrated in ispconfig so im following your adventure voltron81 :)
     
  8. voltron81

    voltron81 New Member

    Great damir...
    Let me know your news about that... :p
     
  9. markbecken09

    markbecken09 New Member

    Hey,
    I've found a script that check the quota of each email and, if it's more that 90%, it will send an email to me and the customer.But I think that having this funcion integrated in ISPConfig could be just great...
    Any idea?


    Thanks.
     
  10. mixo

    mixo New Member

    I have written such a script.
    I will be glad if anyone uses it :)
    Code:
    #!/bin/bash
    
    /usr/sbin/repquota -au |grep web| sed 's/ \+/ /g' | cut -f1,3,4 -d" " | while read LINE
    do
    USED=`echo $LINE | cut -f2 -d" "`
    QUOTA=`echo $LINE | cut -f3 -d" "`
    # TO AVOID DIVISION BY NULL LET'S INCREMENT QUOTA
    let "QUOTA +=1"
    #CALCULATE PERCENT
    let RESULT=USED*100/QUOTA
    if [ $RESULT -ge 90 ]&&[ $RESULT -lt 150 ]; then
         NAME=`echo $LINE | cut -f1 -d" "`
         # SEND MAIL
          SUBJ="-= Your mailbox is full =-"
          mutt  -s "$SUBJ" $NAME@example.ge < /usr/src/scripts/message.txt
    fi
    done
    
     
  11. bluebirdnet

    bluebirdnet New Member

    that looks good thanks Mixo!
     
  12. Darkfire

    Darkfire New Member

    Hi All

    Here is a PHP script I wrote some time ago that uses phpmail to send the notification. For those who are more conformable with PHP. You probably want to change the SQL statement to select mailboxes that are active, or only select whose with a quota.

    Not a fast script as it uses the "du" command line to get the size of the mailbox folder, so probably not great if you have thousands of email boxes to check

    Place a job in the crontab to run once a day.

    PHP:
    <?
            require_once(
    'ispconn.php');
            require_once(
    'phpmail.php');

            
    $sql "select * from mail_user";
            
    $rs mysql_query($sql);

            
    $rowcount mysql_num_rows($rs);
            
    //echo $rowcountm;

    if ($rowcount 0){

            while (
    $row mysql_fetch_object($rs)){
                    
    $over false;
                    
    //0 is unlimited
                    
    if ($row->quota 0){

                            
    $domain = array();
                            
    $domain explode('@',$row->email);
                            
    $dn $domain[1];
                            
    $email $domain[0];
                            
    $path '/var/vmail/' $dn "/" $email "/";
                            
    $output exec('du -s ' $path);
                            
    $foldersize = array();
                            
    $foldersize explode("/",$output);
                            
    $totalsize trim(($foldersize[0]/1024));
                            
    $totalsize round($totalsize);
                            
    //echo $totalsize . chr(10);

                            
    $quota round(($row->quota/1024)/1024);

                            
    $precent = (($totalsize $quota) * 100);
                            
    $precentround($precent);
                            
    //If the folder is over 95 % then try to send a message
                            
    if ($precent >= 95){

                                    
    $email $row->email;
                                    
    $subject "MailBox Full Size Notification" ;

                                    
    $body "<html>";
                                    
    $body .= "<body>";
                                    
    $body .= "Dear User<p>";
                                    
    $body .= "Please note that your email box for "$email " is " $precent "% full. <p>";
                                    
    $body .= "Your mailbox size limit on the server is set to " $quota ."MB and your current usage is ".$totalsize "MB<p>";
                                    
    $body .= "Mail delivery to your email address may be delayed and some mail might not come through. <p>";
                                    
    $body .= "Please clear out some of your email<p>";
                                    
    $body .= "Regards,<BR>";
                                    
    $body .= "Quickspace.co.za Support";

                                    
    $body $body;
                                    
    $mail = new PHPMail();
                                    
    $mail->IsMail();
                                    
    $mail->Sender "me@mydomain.com";
                                    
    $mail->FromName "My Name";
                                    
    $mail->From "me@mydomain.com";
                                    
    $mail->AddAddress("me@mydomain.com");
                                    
    $mail->AddAddress($email);
                                    
    $mail->IsHTML(true);
                                    
    $mail->Subject $subject;
                                    
    $mail->Body $body//Message body in HTML format
                                    
    $mail->AltBody $body//Message in Plain text if recepients mail client doesnt support html mail
                                    
    $mail->WordWrap 50;
                                    if (!
    $mail->Send()){
                                            echo 
    "sendmail error";
                                    }
                            }
                    }
            }
    }
    ?>
     
    Last edited: Jan 26, 2013

Share This Page