script reporting mail consomation journaliere

[sunny]

Bon courage alors

[fastm3]

Rhaaa, j'ai retrouvé !
C'est pour freepbx , et donc il y a du code inutile pour recuperer les logins.

J'avais adapté ca il y a un moment. Le principe est soit de faire un email quotidien, soit d'envoyer le mail uniquement si la variation depasse un seuil.
Dans ce mode , le script permet d'etre executé plusieurs fois par jour, limitant donc le delai entre la surconsommation et l'alerte.

Il y a un seuil sur le nombre d'appels. Il y a aussi un seuil sur la duree globale des appels.
A+
Fastm3.

#!/usr/bin/env php
#Modification par infimo pour adaptation numero surtaxé francais.
#Basé sur un post sur le forum trixbox de schmoozecom
 
<?php
require_once "DB.php";
/******************************
* Begin User Configuration
*****************************/
$email = "fcouque@nospam_infimo.fr";
// If you would like a daily report, set this to true, otherwise, only email if percent thresholds are reached
$daily_report = true;
// Set each of these percentage thresholds.  If they are met, you will receive a report of the abnormal volume of calls or total duration of calls
$normal_outbound_calls_threshold = "80%";
$normal_outbound_duration_threshold = "40%";
$international_outbound_calls_threshold = "20%";
$international_outbound_duration_threshold = "20%";
// If you set a unique hostname on each of your PBXs, you do not need to change the $hostname variable below.  It will automatically use your
// system hostname.  Otherwise, you should change this to something unique so you know where the emails are coming from ;)
$hostname = "telisk_brunoy";
/******************************
* End User Configuration
*****************************/

function parse_amportal_conf($filename) {
    $file = file($filename);
    foreach ($file as $line) {
        if (preg_match("/^\s*([a-zA-Z0-9]+)\s*=\s*(.*)\s*([;#].*)?/",$line,$matches)) {
            $conf[ $matches[1] ] = $matches[2];
        }
    }
    return $conf;
}


if(!$hostname)  {
    $hostname = `hostname`;
}
$n_vol_theshold = ereg_replace("[^0-9]","",$normal_outbound_calls_threshold)/100;
$n_dur_theshold = ereg_replace("[^0-9]","",$normal_outbound_duration_threshold)/100;
$i_vol_theshold = ereg_replace("[^0-9]","",$international_outbound_calls_threshold)/100;
$i_dur_theshold = ereg_replace("[^0-9]","",$international_outbound_duration_threshold)/100;

$config = parse_amportal_conf("/etc/amportal.conf");
$engine = $config['AMPDBENGINE'];
$db_username = $config['AMPDBUSER'];
$db_password = $config['AMPDBPASS'];
$db_host = $config['AMPDBHOST'];
$db_database = "asteriskcdrdb";

$db_url = $engine."://".$db_username.":".$db_password."@".$db_host."/".$db_database;
$db = DB::connect($db_url);

$email_report = false;

$sql = "SELECT 
        ROUND(AVG(sum_duration)) 
    FROM (
        SELECT 
            SUM(duration)/60 AS sum_duration 
        FROM 
            cdr 
        WHERE 
            dst REGEXP '^[0-9]{7,}' AND 
            DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(calldate) AND 
            (WEEKDAY(calldate) >= 0 AND WEEKDAY(calldate) <= 4 )
        GROUP BY 
            DATE(calldate)
    ) 
    AS sum_query";

$result = $db->query($sql);
list($n_avg_duration) = $result->fetchRow(DB_FETCHMODE_ARRAY);

$sql = "SELECT ROUND(SUM(duration)/60) FROM cdr WHERE dst REGEXP '^[0-9]{7,}' AND DATE(calldate) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)";
$result = $db->query($sql);
list($n_last_duration) = $result->fetchRow(DB_FETCHMODE_ARRAY);

$n_duration_percent_change = ($n_last_duration/$n_avg_duration)-1;

$message =  "\n\n";
if($n_duration_percent_change >= $n_dur_theshold)  {
    $email_report = true;
    $message .= "WARNING: ";
}
$message .= "Duree totale des appels journaliers pour un jour de semaine ( moyenne des 30 derniers jours ) : $n_avg_duration minutes. ";
$message .= "Duree totale des appels des dernieres 24 heures: $n_last_duration minutes. Variation en pourcentage: ".(int)($n_duration_percent_change*100)."%";

$sql = "SELECT 
        ROUND(AVG(count_calls)) 
    FROM (
        SELECT 
            COUNT(*) as count_calls 
        FROM 
            cdr 
        WHERE 
            dst REGEXP '^[0-9]{7,}' AND 
            DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(calldate) AND 
            (WEEKDAY(calldate) >= 0 AND WEEKDAY(calldate) <= 4 ) 
        GROUP BY 
            DATE(calldate)
        ) 
        AS count_query";
$result = $db->query($sql);
list($n_avg_total_calls) = $result->fetchRow(DB_FETCHMODE_ARRAY);

$sql = "SELECT COUNT(*) FROM cdr WHERE dst REGEXP '^[0-9]{7,}' AND DATE(calldate) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)";
$result = $db->query($sql);
list($n_last_total_calls) = $result->fetchRow(DB_FETCHMODE_ARRAY);

$n_total_calls_percent_change = ($n_last_total_calls/$n_avg_total_calls)-1;

$message .=  "\n\n";
if($n_total_calls_percent_change >= $n_vol_theshold)  {
    $email_report = true;
    $message .= "WARNING: ";
}

$message .= "Nombre quotidien des appels sortants (moyenne des 30 derniers jours): $n_avg_total_calls. ";
$message .= "Nombre des appels sortants des dernieres 24 heures: $n_last_total_calls. Variation en pourcentage: ".(int)($n_total_calls_percent_change*100)."%";

// International

$sql = "SELECT 
        ROUND(AVG(sum_duration)) 
    FROM (
        SELECT 
            SUM(duration)/60 AS sum_duration 
        FROM 
            cdr 
        WHERE 
            dst REGEXP '^(00|011)[0-9]{10,}' AND 
            DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(calldate) AND 
            (WEEKDAY(calldate) >= 0 AND WEEKDAY(calldate) <= 4 )
        GROUP BY 
            DATE(calldate)
        ) 
        AS sum_query";
$result = $db->query($sql);
list($i_avg_duration) = $result->fetchRow(DB_FETCHMODE_ARRAY);

$sql = "SELECT ROUND(SUM(duration)/60) FROM cdr WHERE dst REGEXP '^(00|089)[0-9]{6,}' AND DATE(calldate) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)";
$result = $db->query($sql);
list($i_last_duration) = $result->fetchRow(DB_FETCHMODE_ARRAY);
if(!$i_last_duration)  {
    $i_last_duration = 0;
}
if($i_avg_duration && $i_avg_duration != 0)  {
    $i_duration_percent_change = ($i_last_duration/$i_avg_duration)-1;
}
else  {
    $i_duration_percent_change = 0;
    $i_avg_duration = 0;
}
$message .=  "\n\n";
if($i_duration_percent_change >= $i_dur_theshold)  {
    $email_report = true;
    $message .= "WARNING: ";
}

$message .= "Duree des appels internationaux et 089X un jour de semaine (moyenne des 30 derniers jours): $i_avg_duration minutes. ";
$message .= "Duree des appels internationaux et 089X des dernieres 24 heures: $i_last_duration minutes. Variation en pourcentage: ".(int)($i_duration_percent_change*100)."%";

$sql = "SELECT 
        ROUND(AVG(count_calls)) 
    FROM (
        SELECT 
            COUNT(*) as count_calls 
        FROM 
            cdr 
        WHERE 
            dst REGEXP '^(00|011)[0-9]{10,}' AND 
            DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(calldate) AND 
            (WEEKDAY(calldate) >= 0 AND 
            WEEKDAY(calldate) <= 4 ) 
        GROUP BY 
            DATE(calldate)
        ) 
        AS count_query";
$result = $db->query($sql);
list($i_avg_total_calls) = $result->fetchRow(DB_FETCHMODE_ARRAY);
if(!$i_avg_total_calls)  {
    $i_avg_total_calls = 0;
}

$sql = "SELECT COUNT(*) FROM cdr WHERE dst REGEXP '^(00|089)[0-9]{6,}' AND DATE(calldate) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY)";
$result = $db->query($sql);
list($i_last_total_calls) = $result->fetchRow(DB_FETCHMODE_ARRAY);

if($i_avg_total_calls && $i_avg_total_calls != 0)  {
    $i_total_calls_percent_change = ($i_last_total_calls/$i_avg_total_calls)-1;
}
else  {
    $i_total_calls_percent_change = 0;
    $i_avg_total_calls = 0;
}
$message .=  "\n\n";
if($i_total_calls_percent_change >= $i_vol_theshold)  {
    $email_report = true;
    $message .= "WARNING: ";
}

$message .= "Nombre des appels internationaux et 089X sortants un jour de la semaine (moyenne des 30 derniers jours): $i_avg_total_calls. ";
$message .= "Nombre des appels internationaux et 089X des dernieres 24 heures: $i_last_total_calls. Variation en pourcentage: ".(int)($i_total_calls_percent_change*100)."%";

// si email_report, la variation max a été dépassée.
if($email_report)
        $subject = "ALERTE: volume d'appel pour $hostname";
else
        $subject = "Rapport quotidien du volume d'appel pour $hostname";

if($daily_report || $email_report)  {
    $from = "Rapport du volume d'appel <$email>";
    $headers = "From: $from" . "\r\n" .
               'Reply-To: noreply@pbx' . "\r\n";
  mail($email,$subject,$message,$headers);
}
echo $message;
?>

[_AK_]

ha yes, il y a des trucs sympa dans ton script.

Je vais piquer les bouts de code qui m'interessent.


Merci

[_AK_]

bon finalement je vais m'en tenir aux mini scripts que j'ai écris car ils sont plus simple à maintenir et ne font que ce que j'ai besoin:

#!/bin/sh

#envoi un mail d'alerte si duree d'appel dépasse le seuil fixé
#a mettre en cron toutes les heures

#requete
requete="connect asterisk; SELECT ROUND(sum(duration)/3600) AS calltime FROM cdr WHERE DATE(calldate) >= DATE_SUB(CURDATE(),INTERVAL 1 DAY) GROUP BY substring(calldate,1,10);"
#execution de la requete
resreq=`mysql -u root -ppass -e "$requete" `
#on récupere juste le montant
resreq=`echo $resreq | awk '{print $NF}'`

#si supérieur au seuil en heure pleine
if [ $resreq -gt 22 ]
then #envoi email
 titre="ALERTE CONSO $resreq HEURES AUJOURD HUI !"
 echo "ALERTE CONSO $resreq HEURES AUJOURD HUI !" |/usr/bin/mail -s "$titre" utilisateur@domaine.fr -b copie@domaine.com
fi

et pour le reporting journalier:

#!/bin/sh
#envoi un mail avec les informations sur le mois en cours
#a mettre en cron une fois par jour en fin de journée

#requete
requete="connect asterisk;  SELECT substring(calldate,1,10) AS day, ROUND(sum(duration)/3600,2) AS calltime, count(*) as nbcall FROM cdr WHERE MONTH(calldate) = MONTH(NOW()) GROUP BY substring(calldate,1,10);"

#execution requete et mise des résultats dans un fichier
mysql -u root -ppass -e "$requete">./resreq.txt

#on défini le titre du mail pour avoir l'information sur le jour en cours
titre=`tail --line=1 ./resreq.txt|tr '\t' '__'`

#envoi du mail
cat ./resreq.txt |/usr/bin/mail -s "$titre" vous@domaine.fr -b copie@domaine.fr

[fastm3]

Pas de pb. Pour l'alerte , les dernieres 24h ( DATE_SUB ) seraient preferables, je pense, pour toi.
Parce que la, si il se connecte à 1h ou 2h du mat, il peut faire l'integralité de ta conso max d'appel de numeros surtaxes ou etranger avant de declencher l'alerte.
A+
Fastm3.

[_AK_]

bien vu, j'ai édité mon script et mon post
merci