cdr_csv est un module qui va s'occuper de genener les csv avec les cdrs. S'il n'est pas chargé, ce qui est problement le cas avec xivo qui doit utilisé une BD , il n'existera pas.
Il est tout de meme plus simple d'exploiter les données ensuite quand c'est stocké dans une base de données. Voir exemple plus bas.
Mais ne pas avoir besoin de RDBMS est aussi le coté pratique de ce module.
C'est une excellente idée de monitorer regulierement les appels sur son asterisk, ( meme si ca peut ne pas etre suffisant comme expliqué plus haut mais pas bien compris a priori , les mots de passe forts ne changent rien...).
Pour monitorer, on peut faire cela de maniere plus ou moins complete. Je vous joins un autre petit script que j'ai utilisé auparavant un petit peu plus évolué meme si tres tres vieux...
Attention , il est brut , adapté plutot pour freepbx mais hormis la partie connection, le principe serait le meme pour xivo. Il tournait sur des anciennes installs et suite au changement de amportal.conf dans les dernieres versions, il faut juste adapter ou coder en dur les identifiants d'acces qui ne s'y trouve plus. Il ne marchera pas sans adaptation à votre install.
On peut avoir 2 strategies. Soit, on envoie un rapport avec les stats du jour , soit on analyse les consos et on previent des qu'un changement des habitudes notable est intervenus.
Le declenchement par cron sera adapté en fonction de vos besoins.
Pour cela, on calcule les valeurs moyennes de conso vers l'etranger ou num surtaxés et on definit une variation autorisée en pourcentage. Des que cette variation est dépassée, on previent l'admin permettant d'analyser rapidement le soucis et d'intervenir ou pas. Certes , dans ce cas, on intervient à posteriori mais c'est toujours mieux que pas du tout.
My €0.02
Fastm3.
Code:
#!/usr/bin/env php
#Modification par infimo ( Francois Couque ) 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_telisk.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;
?>
Règles de messages
Répondre avec citation