Este artículo busca crear un procedimiento para informar por correo electrónico, el comportamiento de extensiones caídas en Asterisk, por periodos de 10 o más días.
El problema en general con respecto al estado de las extensiones Asterisk, radica en que sólo se nos reporta el estado actual de las extensiones, a modo de una foto en el instante en que hacemos un requerimiento de estado de las mismas. No existe un historial que nos indique cuánto tiempo una extensión ha estado desconectada en el transcurso del día o de los días.
Para darle una solución a esta problemática, el siguiente procedimiento busca tomar el estado de las extensiones cada hora, subiendo esta información a una tabla de la base de datos MySQL. Si una extensión ha estado desconectada por 19 o más veces en un día, entonces se considera que ha estado desconectada durante ese día. Con base en esa premisa, se buscan todas las extensiones que han estado desconectadas por 10 o más días consecutivos, para crear una lista que se envía por correo electrónico.
Los siguientes serían los pasos a realizar para activar esta solución:
A) En la DB MySQL
1) Se crea una tabla para subir los estados de las extensiones:
CREATE TABLE `t_peer_status` (
`cod_peer_status` int(11) NOT NULL AUTO_INCREMENT,
`date_st` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
`username` varchar(26) CHARACTER SET latin1 NOT NULL DEFAULT »,
`host` varchar(40) CHARACTER SET latin1 DEFAULT »,
`dyn` varchar(4) CHARACTER SET latin1 DEFAULT NULL,
`forceport` varchar(11) CHARACTER SET latin1 DEFAULT NULL,
`acl` varchar(4) CHARACTER SET latin1 DEFAULT NULL,
`port` varchar(9) CHARACTER SET latin1 DEFAULT NULL,
`status` varchar(12) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`cod_peer_status`),
UNIQUE KEY `date_st` (`date_st`,`username`)
) ENGINE=MyISAM AUTO_INCREMENT=9774040 DEFAULT CHARSET=utf8;
2) Se crea una tabla para almacenar los nombres de los archivos logs de estados de extensiones:
CREATE TABLE `t_files_peer_st` (
`cod_file_per_st` int(11) NOT NULL AUTO_INCREMENT,
`NOMBRE` varchar(30) DEFAULT NULL,
`MODIFICACION` datetime DEFAULT NULL,
`DIRECTORIO` varchar(30) DEFAULT NULL,
PRIMARY KEY (`cod_file_per_st`)
) ENGINE=MyISAM AUTO_INCREMENT=285 DEFAULT CHARSET=latin1;
3) Se crea la siguiente vista de apoyo para recoger el estado de las extensiones de los últimos 12 días:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_exten_cur` AS select `t_peer_status`.`cod_peer_status` AS `cod_peer_status`,`t_peer_status`.`date_st` AS `date_st`,`t_peer_status`.`username` AS `username`,`t_peer_status`.`host` AS `host`,`t_peer_status`.`dyn` AS `dyn`,`t_peer_status`.`forceport` AS `forceport`,`t_peer_status`.`acl` AS `acl`,`t_peer_status`.`port` AS `port`,`t_peer_status`.`status` AS `status` from `t_peer_status` where (cast(`t_peer_status`.`date_st` as date) between (curdate() – interval 12 day) and curdate());
4) Se crea un procedimiento almacenado para hacer la consulta de los 10 días desconectados en forma continua, haciendo uso también de un cursor para detectar líneas desconectadas en ese tiempo :
CREATE DEFINER=`root`@`%` PROCEDURE `off_days`(_inicio int(11),_extension varchar(50), out _exten INT(11))
begin
SET @extension=trim(_extension);
SET @inicio=_inicio;
set @c=»;PREPARE stmt FROM «select SC.cont INTO @c from (
select distinct count(username) as cont,date(date_st),hour(date_st),username
from v_exten_cur
where (date(date_st) = (curdate() – interval (?) day)) and (substring(status,1,2)<>’OK’) and (username = ? )
group by username
) SC»;EXECUTE stmt USING @inicio,@extension;
DEALLOCATE PREPARE stmt;
set _exten=@c;
end;CREATE DEFINER=`root`@`%` PROCEDURE `exten_down_10`(_inicio INT)
begin
DECLARE _extension varchar (26);
DECLARE _dias INT;
DECLARE _dias_caidos INT;
DECLARE _fin_down INT;
DECLARE _exten INT;
DECLARE fin INTEGER DEFAULT 0;DECLARE dircon CURSOR FOR select SC.username from (
select count(username) as cont,date(date_st),hour(date_st) ,username
from t_peer_status
where date(date_st) = curdate() – interval (_inicio) day and substring(status,1,2)<>’OK’
group by username
)
SC where SC.cont>=19;DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;DROP TEMPORARY TABLE IF EXISTS tmp_exten_down;CREATE TEMPORARY TABLE tmp_exten_down (extension varchar(20), dias int);OPEN dircon;loop1: LOOPSET _dias = _inicio;
SET _dias_caidos = 0;
SET _fin_down=0;
SET _exten=0;FETCH dircon INTO _extension;IF fin = 1 THEN
LEAVE loop1;
END IF;while _fin_down=0 and _dias < 11 DO
call off_days(_dias,_extension,_exten);
IF _exten = 0 THEN
SET _fin_down=1;
END IF;SET _dias=_dias+1;
IF _exten >= 19 THEN
SET _dias_caidos=_dias_caidos + 1;
END IF;end while;
insert into tmp_exten_down values (_extension, _dias_caidos);
END LOOP loop1;
CLOSE dircon;
select * from tmp_exten_down where dias>=0 ;
DROP TEMPORARY TABLE IF EXISTS tmp_exten_down;
end;
B) En el servidor Asterisk (Linux):
1) Se crea un script (query.sh) para traer el estado de las extensiones (asterisk -rx «sip show peers«) y guardarlo en un archivo de texto (extension_<fecha>) :
#!/bin/bash
printf «\n\n******************\n» >> /home/soporte/extensiones/logs/extension_$(date +»%m-%d-%y»)
printf «$(date +»%Y-%m-%d %H:%M»)\n» >> /home/soporte/extensiones/logs/extension_$(date +»%m-%d-%y»)
printf «$(/usr/sbin/asterisk -rx «sip show peers»)» >> /home/soporte/extensiones/logs/extension_$(date +»%m-%d-%y»)
exit
2) Se crea un archivo importa.php para recoger los datos de los archivos logs con los estados de las extensiones, al final llama al php ext_down.php que reporta las extensiones caídas por 10 o más días:
<?php
require_once(«includes/CConexion.php»);
$conex=new CConexion();
$conex->Conectar();
$original_mem = ini_get(‘memory_limit’);
mysql_query(«SET AUTOCOMMIT=0″,$conex->getConexion()) or die(mysql_error());function ExplorarDir($ruta){
$direct=null;if($dh=opendir($ruta)){
$j=0;
while (($file = readdir($dh)) !== false) {
if (is_dir($ruta . $file) && $file!=».» && $file!=»..»){
$direct[$j]=(string)$file;
$j++;
}
}
closedir($dh);
}
return $direct;
}function ExplorarFile($ruta){
$files=null;
echo $ruta.»<br>»;
if($dh=opendir($ruta)){
$j=0;
while (($file = readdir($dh)) !== false) {if (!is_dir($ruta . $file) && $file!=».» && $file!=»..»){
$files[$j]=(string)$file;
$j++;
}
}
closedir($dh);
}
return $files;
}function TraerFecha($mifecha){
return $mifecha;
}$ruta=»/home/soporte/extensiones/logs»;
$work=true;
$directorio = null;
$archivos = null;
$directorio=ExplorarDir($ruta);
$ingresos=0;
$error_de_insercion=0;
$archivos=null;
$archivos=ExplorarFile(«/home/soporte/extensiones/logs»);for($k=0;$k<count($archivos);$k++)
{
echo «CONTEO: $i – DIRECTORIO:».$directorio.»- ARCHIVO:».$archivos[$k].»<br>»;$modificacion=date(«Y-m-d H:i:s.», filemtime($ruta.$directorio.»/».$archivos[$k]));
$res_t=mysql_query(«select count(*) as cuenta from t_files_peer_st where NOMBRE='».$archivos[$k].»‘ AND MODIFICACION = ‘».$modificacion.»‘»,$conex->getConexion());if($reg_t=mysql_fetch_assoc($res_t))
if(!$reg_t[«cuenta»])//Si no exste coincidencia en la base de datos
{
$error_de_insercion=0;
$cont_lineas=0;
$periodo=0;
$cerrado=false;
echo $ruta.$directorio;$archivo=fopen($ruta.$directorio.»/».$archivos[$k], «r»);
if(!$archivo)
{
echo «El archivo «.$archivos[$k].» no pudo abrirse»;
exit();
}
$start=false;
while ($linea = fgets($archivo,1024))
{
echo $linea.»<br />»;
if(trim($linea)==»******************») {
$start=true;
echo «encabezado»;
}
else
{
if($start)
{
$fecha=trim($linea);
$start=false;
echo $fecha;
}
else
{
$work=true;
if((strpos($linea,»Name/username»)!==FALSE ) || (strpos($linea,»*********»)!==FALSE ) || (strpos($linea,»sip peers»)!==FALSE ) || trim($linea)==»» )
$work=false;if ($work) {
$string = $linea;
$intervals = array(27, 40, 4, 11, 4,9,11);
$start1 = 0;
$parts = array();
foreach ($intervals as $i)
{
$parts[] = trim(mb_substr($string, $start1, $i));
$start1 += $i;
}
$sql=»
insert into t_peer_status
(
date_st,
username,
host,
dyn,
forceport,
acl,
port,
status
)
values (
‘$fecha’,
‘».$parts[0].»‘,
‘».$parts[1].»‘,
‘».$parts[2].»‘,
‘».$parts[3].»‘,
‘».$parts[4].»‘,
‘».$parts[5].»‘,
‘».$parts[6].»‘
)
«;
echo $sql;
}
mysql_query($sql,$conex->getConexion()) ;
}}
$cont_lineas++;
}if(!$cerrado)
fclose ($archivo);$sql=»insert into t_files_peer_st (NOMBRE,DIRECTORIO,MODIFICACION) values (‘».$archivos[$k].»‘,'».$directorio.»‘,'».$modificacion.»‘)»;
$res_a=@mysql_query($sql,$conex->getConexion());if(!$res_a){
echo «Hubo un inconveniente al ingresar el nombre del archivo a la base de datos<br>»;
//exit();
}mysql_query(«COMMIT»,$conex->getConexion());
}//Cierra no existe en la base de datos
else
echo «$i – El archivo «.$archivos[$k].» ya se encuentra ingresado<br>»;}//cierra el for k
//mysql_query(«ROLLBACK»,$conex->getConexion());
$conex->Desconectar();
ini_set(‘memory_limit’,$original_mem);
ini_set(‘max_execution_time’, 30);require_once(«ext_down.php»);
?>
3) Se crea el script php ext_down.php para traer las extensiones con 10 días o más y reportarlas por correo electrónico:
<?php
$conexion = mysql_connect(‘localhost’, ‘root’, ‘xxxx’);
mysql_select_db(‘mya2billing’);
$exten=array();
$sql3=»CALL mya2billing.exten_down_10(0)»;
$j=0;
$res3=mysql_query($sql3,$conexion) or die(mysql_error());while($reg=mysql_fetch_assoc($res3)){
$exten[$j][0]=$reg[«extension»];
$exten[$j][1]=$reg[«dias»];
$j++;
}require(«class.phpmailer.php»);
$mail = new PHPMailer();
$mail->PluginDir = «»;
$mail->Mailer = «smtp»;
$mail->SMTPAuth = false;
$mail->Host = «mail.xxx.com»;
$mail->Port = 25;
$mail->From = «[email protected]»;
$mail->FromName = «Sistema de Informacion»;
$mail->Subject = «Informe Extensiones Caidas»;
$mail->Username = «[email protected]»;
$mail->Password = «xxxxxx»;
$mail->AddCC(«[email protected]»);
$body .= «<b>Extensiones con 10 dias consecutivos desconectados</b> <br><br>»;
$body .= «<table style=’font-family:Arial, Helvetica, sans-serif; font-size:11px; border-right: 1px solid #001f27; text-indent: 5px;
border-bottom: 1px solid #001f27; border-top: 1px solid #001f27; border-left: 1px solid #001f27;’ border=’0′ width=’100%’ >
<tr>
<td style=’background-color:#069; color:#FFF; ‘><strong>EXTENSION</strong></td>
<td style=’background-color:#069; color:#FFF; ‘><strong>DIAS CAIDOS</strong></td>
</tr>»;for($i=0;$i<sizeof($exten);$i++)
$body .= «<tr style=’background-color: #FFF; font-size:10px;’>
<td style=’font-size:10px; color: $font’>».$exten[$i][0].»</td>
<td style=’font-size:10px; color: $font’>».$exten[$i][1].»</td>
</tr>»;
$mail->Body = $body;if (!$mail->Send())
{
echo «Operación Fallida: «.$mail->ErrorInfo.»</br></br>»;
}
else
{
echo «Notificación Enviada</br></br>»;
$mail->Timeout=120;
}
mysql_close($conexion);
?>
4) Se crean las siguientes tareas programadas para realizar automáticamente esta operación (a las 11:30 pm se importan los datos de los archivos logs, y a las 00 se reporta por correo las extensiones caídas):
$ crontab -l00 * * * * /home/soporte/extensiones/query.sh30 23 * * * php /home/soporte/extensiones/importa/importa.php
En resumen:
El script query.sh se encarga de crear cada hora los logs de estado de las extensiones. Se crea un archivo diario con todas las extensiones.
El archivo importa.php se encarga de importar los archivos creados por query.sh, para subirlos a la base de datos. Luego llama las consultas para determinar el número de días de desconexión de las extensiones, y envía dicho resultado por correo electrónico, mediante el script ext_down.php.
Si estás interesado en realizar automatizaciones de este tipo o similares, en ITSoftware SAS contamos con personal especializado para hacer más eficientes cualquier proceso que quieras mejorar.