Análisis y representación de estadísticas de llamadas de una centralita VoIP a través de Grafana

Descripción El texto presentado no puede ocupar más de una línea Autor: Francisco Pablo García Sánchez Tutor: José Manuel Fornés Rumbao Trabajo Fin de Grado Grado en Ingeniería de las Tecnologías de Telecomunicación Análisis y representación de estadísticas de llamadas de una centralita VoIP a través de Grafana Departamento de Ingeniería Telemática Escuela Técnica Superior de Ingeniería Universidad de Sevilla Sevilla, 2021 Trabajo Fin de Grado Grado en Ingeniería de las Tecnologías de Telecomunicación Análisis y representación de estadísticas de llamadas de una centralita VoIP a través de Grafana Autor: Francisco Pablo García Sánchez Tutor: José Manuel Fornés Rumbao Profesor titular Departamento de Ingeniería Telemática Escuela Técnica Superior de Ingeniería Universidad de Sevilla Sevilla, 2021 I Trabajo Fin de Grado: Análisis y representación de estadísticas de llamadas de una centralita VoIP a través de Grafana Autor: Francisco Pablo García Sánchez Tutor: José Manuel Fornés Rumbao El tribunal nombrado para juzgar el Proyecto arriba indicado, compuesto por los siguientes miembros: Presidente: Vocales: Secretario: Acuerdan otorgarle la calificación de: Sevilla, 2021 El Secretario del Tribunal II A mi familia A mis compañeros III Agradecimientos Este trabajo pone punto final a una de las etapas más importantes y bonitas de mi vida. Quien me lo iba a decir aquel 26 de septiembre de 2016, cuando entré por primera vez en la facultad, lleno de nervios, inquietudes e ilusión. No ha sido un camino ni mucho menos fácil, pero, he demostrado que con esfuerzo, trabajo y dedicación puedo conseguir lo que me proponga. Aquí quiero dejar constancia de todas aquellas personas que me han ayudado, de una u otra manera, a llegar hasta aquí. Empezando por todo el personal y compañeros de la Residencia Universitaria Campus Cartuja, que me hicieron sentir como en casa los dos primeros años de carrera. Continuando por todos mis compañeros del grado, por hacer las clases más amenas y por vuestra ayuda estos últimos cinco años. Como no, agradecer a mis compañeros de piso de estos tres últimos años: Alfonso, Pablo y Alejandro, por transmitirme su confianza y apoyo en todo momento, por las noches de fútbol y hacer que las rutinas fueran más llevaderas. También, agradecer a todo el personal docente, desde mis profesores de Educación Primaria hasta todos los que me han impartido clases y prácticas en la Escuela Técnica Superior de Ingeniería de Sevilla, por su ayuda, dedicación y predisposición. Mención especial a mi familia. A mis padres y hermanos, por estar siempre ahí, en los buenos y no tan buenos momentos y por enseñarme que todo se puede ver siempre desde el lado positivo. A todos mís tíos y primos, por siempre preocuparos de que todo fuera bien y por todos los ánimos recibidos en estos cinco años. Este trabajo se lo quiero dedicar especialmente a mis abuelos (Pedro, Julia y Ana), por su apoyo y cariño diario, porque siempre están ahí y sé que hoy están orgullosos de mí. No puedo olvidarme de mi abuelo Paco y de mi tío Nene, que, desgraciadamente, no están con nosotros, pero sé que también estarán orgullosos viéndome allá donde estén. Porque, aunque hoy esté lejos, sé que siempre vais a estar ahí. Quiero daros las gracias a todos, por influir de una u otra manera a ser la persona que soy hoy. Simplemente, gracias. Francisco Pablo García Sánchez Ismaning, Baviera, Alemania Julio de 2021 IV Índice Agradecimientos IV Resumen V Abstract VI 1 Introducción 1 1.1. Objetivos 1 1.2. Solución desarrollada 1 2 Grafana 3 2.1. Historia 3 2.2. Propósito y finalidad 3 2.3. Interfaz Gráfica 3 2.3.1. Dashboards 4 2.3.2. Paneles 5 2.4. Plugins 6 2.4.1. Plugins: Paneles 6 2.4.1. Plugins: Fuentes de datos 6 2.4.1. Plugins: Aplicaciones 6 2.5. Grafana en la actualidad 7 3 Fuente de datos: API de la centralita 8 3.1. Información almacenada 8 3.2. Mecanismos de acceso a los datos 8 3.3. Acceso a los datos requeridos 8 3.3.1. Consulta el registro de llamadas en la centralita 8 3.3.2. Consulta las colas disponibles en la centralita 9 3.3.3. Consulta los agentes existentes en la centralita 10 3.4. Servicios implementados 11 3.4.1. Servicio 1 12 3.4.2. Servicio 2 14 4 Persistencia de la información 17 4.1. Servidor de BBDD: MySQL 17 4.2. Tablas existentes 18 4.2.1. Tabla datos_centralita 18 4.2.2. Tabla colas_centralita 19 4.2.3. Tabla agentes_centralita 19 4.2.4. Tabla llamadastotalesagente 20 4.2.5. Tabla rango_horario 20 4.2.6. Tabla regla_atendido 20 4.2.7. Tabla tipos_destino 21 4.3. Stored Procedures 21 4.3.1. LlamadasTotalesAgentes 21 4.3.2. PorcContactacionTotal 22 4.3.3. PorcNoContestadas 22 4.3.4. PorcSalientesContestadas 23 4.3.5. PorcSalientesContestadasVSEntrantesNoContestadas 23 4.3.6. PorcSalientesVSNoContestadas 24 4.3.7. LlamadasEntrantes_FranjaSemana 24 4.3.8. TotalLlamadas_RangoHora 25 4.4. Funciones 25 4.4.1. getTipoLlamada 25 4.4.2. getSaliente 25 4.4.3. getSalienteKo 26 4.4.4. getLlamada 26 4.4.5. getNoContestada 26 4.4.6. getMes 26 4.4.7. getFranja30segs 26 4.4.8. getDiaSem 27 4.4.9. getTurno 27 4.4.10. getInternoDestino 27 4.4.11. getConcaOff 27 4.4.12. getAtendidoDespues 28 4.4.13. getDiaSaliente 28 4.4.14. getTiempoRespuesta 28 4.4.15. getFechaAnterior 28 4.4.16. getId 29 4.4.17. getSemana 29 4.4.18 getLlamadasEntrantesAgente 29 4.4.19. getLlamadasSalientesAgente 29 4.4.20. getNombreAgente 30 5 Representación de los datos 31 5.1. Análisis Mensual 31 5.1.1. Llamadas Entrantes 31 5.1.2. Llamadas Salientes 33 5.1.3. % Llamadas Entrantes No Contestadas 34 5.1.4. % Llamadas Salientes VS Llamadas Entrantes No Contestadas 35 5.1.5. % Llamadas Salientes Contestadas 36 5.1.6. % Llamadas Salientes Contestadas VS Llamadas Entrantes No Contestadas 36 5.1.7. % Contactación Total 37 5.1.8. Gestiones de cada agente sobre el total de llamadas 38 5.1.9. Gestiones de cada agente sobre el total de llamadas entrantes 39 5.1.10. Gestiones de cada agente sobre el total de llamadas salientes 39 5.1.11. Llamadas por cada agente 40 5.1.12. Número de llamadas grabadas por mes 40 5.1.13. Número de llamadas no grabadas por mes 41 5.2. Análisis Semanal 42 5.2.1. Llamadas Entrantes 42 5.2.2. Llamadas Salientes 42 5.2.3. Llamadas entrantes (semana y franja horaria) 43 5.3. Análisis Diario 44 5.3.1. Llamadas Entrantes 44 5.3.2. Llamadas Salientes 45 5.3.3. Llamadas entrantes y salientes (día y franja horaria) 47 Apéndice 48 A.1. Excel: datos sobre las llamadas 48 A.2. PM2 51 A.3. Dificultades encontradas 51 Referencias 52 Glosario 53 ÍNDICE DE FIGURAS Figura 1. Solución desarrollada 2 Figura 2. Dashboard: Interfaz Gráfica 4 Figura 3. Dashboard: Cabecera 5 Figura 4. Panel de Grafana 6 Figura 5. Petición registro de llamadas 8 Figura 6. JSON de respuesta a la petición de registros de llamadas 9 Figura 7. Petición colas de la centralita 9 Figura 8. JSON de respuesta a la petición colas de la centralita 10 Figura 9. Petición agentes de la centralita 10 Figura 10. JSON de respuesta a la petición agentes de la centralita 11 Figura 11. Servicio: Consulta agentes 12 Figura 12. Servicio 1: Consulta colas 13 Figura 13. Servicio 1: Conexión a la BBDD 13 Figura 14. Servicio 1: Inserción agentes y colas 14 Figura 15. Servicio 2: Petición 14 Figura 16. Servicio 2: Petición de llamadas 15 Figura 17. Resultado procedimiento 'LLamadasTotalesAgentes' 22 Figura 18. Llamada: Contactación Total 22 Figura 19. Resultado procedimiento 'PorcContactacionTotal' 22 Figura 20. Llamada: PorcNoContestadas 23 Figura 21. Resultado procedimiento 'PorcNoContestadas' 23 Figura 22. Llamada: PorcSalientesContestadas 23 Figura 23. Resultado procedimiento 'PorcSalientesContestadas' 23 Figura 24. Llamada: PorcSalientesContestadasVSEntrantesNoContestadas 23 Figura 25. Resultado procedimiento 'PorcSalientesContestadasVSEntrantesNoContestadas' 24 Figura 26. Llamada: PorcSalientesVSEntrantesNoContestadas 24 Figura 27. Resultado procedimiento 'PorcSalientesVSEntrantesNoContestadas' 24 Figura 28. Llamada: LlamadasEntrantes_FranjaSemana 24 Figura 29. Llamada: TotalLlamadas_RangoHora 25 Figura 30. Función: getTipoLlamada 25 Figura 31. Función: getSaliente 25 Figura 32. Función: getSalienteKo 26 Figura 33. Función: getLlamada 26 Figura 34. Función: getNoContestada 26 Figura 35. Función: getMes 26 Figura 36. Función: getFranja30segs 26 Figura 37. Función: getDiaSem 27 Figura 38. Función: getTurno 27 Figura 39. Función: getInternoDestino 27 Figura 40. Función: getConcaOff 27 Figura 41. Función: getAtendidoDespues 28 Figura 42. Función: getDiaSaliente 28 Figura 43. Función: getTiempoRespuesta 28 Figura 44. Función: getFechaAnterior 28 Figura 45. Función: getId 29 Figura 46. Función: getSemana 29 Figura 47. Función: getLlamadasEntrantesAgente 29 Figura 48. Función: getLlamadasSalientesAgente 29 Figura 49. Función: getNombreAgente 30 Figura 50. Representación Mensual: Llamadas Entrantes Contestadas 31 Figura 51. Representación Mensual: Llamadas Entrantes No Contestadas 32 Figura 52. Representación Mensual: Llamadas Entrantes 32 Figura 53. Representación Mensual: Llamadas Salientes Contestadas 33 Figura 54. Representación Mensual: Llamadas Salientes No Contestadas 33 Figura 55. Representación Mensual: Llamadas Salientes 34 Figura 56. Representación Mensual: % Llamadas Entrantes No Contestadas 34 Figura 57. Representación Mensual: % Llamadas Salientes VS Entrantes No Contestadas 35 Figura 58. Representación Mensual: % Llamadas Salientes Contestadas 36 Figura 59. Representación Mensual: % Llamadas Salientes Contestadas VS Entrantes No Contestadas 37 Figura 60. Representación Mensual: % Contactación Total 37 Figura 61. Representación Mensual: Gestiones de cada agente sobre el total de llamadas (D.Barras) 38 Figura 62. Representación Mensual: Gestiones de cada agente sobre el total de llamadas (S.Circular) 38 Figura 63. Representación Mensual: Gestiones de cada agente sobre el total de llamadas entrantes 39 Figura 64. Representación Mensual: Gestiones de cada agente sobre el total de llamadas salientes 39 Figura 65. Representación Mensual: Llamadas por cada agente 40 Figura 66. Representación Mensual: Número de llamadas grabadas 40 Figura 67. Representación Mensual: Número de llamadas No grabadas 41 Figura 68. Representación Semanal: Llamadas Entrantes 42 Figura 69. Representación Semanal: Llamadas Salientes 43 Figura 70. Representación Semanal: Llamadas Entrantes por semana y franja horaria 43 Figura 71. Representación Diaria: Llamadas Entrantes 44 Figura 72. Representación Diaria: Llamadas totales entrantes 45 Figura 73. Representación Diaria: Llamadas Salientes 46 Figura 74. Representación Diaria: Llamadas totales salientes 46 Figura 75. Representación Diaria: Llamadas Entrantes y Salientes 47 Figura 76. Excel: Llamadas Entrantes y Salientes por meses 48 Figura 77. Excel: Llamadas Entrantes y Salientes por semana 49 Figura 78. Excel: Llamadas Entrantes y Salientes por día 49 Figura 79. Excel: Llamadas Entrantes por franja y día 50 Figura 80. Excel: Gestiones de cada agente 50 1 1 INTRODUCCIÓN continuación, se exponen los objetivos fundamentales que se pretenden conseguir con la realización del trabajo, así como la solución para llegar a tal fin. 1.1 Objetivos El objetivo fundamental de este trabajo es, gracias a la herramienta gráfica Grafana, poder hacer un seguimiento y control continuo sobre el estado de las llamadas que circulan por la centralita VoIP a lo largo del tiempo. En otras palabras, será posible visualizar y analizar de forma inmediata las estadísticas de las llamadas, la forma en la que son tratadas por los distintos agentes existentes en la centralita o su paso por las distintas colas. Además, será posible realizar una comparativa sobre todo el flujo de llamadas (entrantes, salientes, contestadas, perdidas, etc) en intervalos de tiempo (meses, semanas, días), con el objetivo de medir la eficiencia de la centralita y de sus agentes en lo que al tratamiento de llamadas se refiere. 1.2 Solución desarrollada Para llevar a cabo la propuesta anterior con éxito se ha recurrido a la herramienta de visualización y monitorizacón de datos ‘Grafana’. Como toda aplicación de monitorización, necesita una fuente de datos. Estos datos (información) proceden de una API que nos proporciona la propia centralita con toda la información necesaria para hacer el seguimiento de las llamadas. Dicha información será accedida por dos servicios, programados en JavaScript, los cuales mediante peticiones GET y POST acceden a las tablas y registros necesarios para obtener la información y, al mismo tiempo, realizar las modificaciones necesarias, en cuanto al tipo de datos se refiere, para cargarlos en la tabla correspondiente dentro de la Base de Datos a la que se conecta Grafana. En nuestro caso, usaremos una Base de Datos basada en el gestor y servidor MySQL. Por su parte, Grafana, mediante peticiones SQL en tiempo real a la Base de Datos, representa la información obtenida en Dashboards y Paneles de distinto tipo como, por ejemplo, gráficas continuas, diagrama de barras, sectores circulares, etc. A El éxito del triunfo no radica en vencer siempre, sino en no dejarse tumbar nunca. -Napoleón- 2 Figura 1. Solución desarrollada 3 2 GRAFANA n este punto se describirán, tanto desde el punto de vista gráfico como funcional, las opciones que nos proporciona Grafana para la monitorización y representación de datos. Además, se detallan algunos de los plugins más destacables que se pueden incorporar a Grafana y algunos de los usos actuales en los que Grafana está siendo protagonista. 2.1 Historia Grafana es una herramienta o aplicación web multiplataforma de software libre. Fue lanzada el 19 de enero de 2014 por Torkel Ödegard como una rama de un proyecto en Orbitz. Inicialmente estaba dirigido a bases de datos de series de tiempo como puden ser InfluxDB, OpenTSDB o Prometheus. Con el paso del tiempo, ha logrado evolucionar hasta lograr soportar bases de datos relacionales como MySQL o PostgreSQL. Grafana está escrita en Lenguaje Go (creado por Google) y Node.js TLS, con una fuerte interfaz de programación de aplicaciones (API). Actualmente, Torkel, su creador, sigue al frente de su desarrollo y mantenimiento. Grafana tiene publicado de forma abierta su código fuente en GitHub. 2.2 Propósito y finalidad Como se ha comentado en un punto anterior, la funcionalidad principal de Grafana es la visualización y monitorización de información. Para ello, cuenta con el soporte de diferentes tipos de bases de datos (temporales, relacionales, …), así como una interfaz gráfica sencilla e intuitiva. Gracias a Grafana, podemos ver la evolución de nuestra información al instante y en tiempo real, así como programar alertas en el caso de que queramos controlar ciertos umbrales. 2.3 Interfaz Gráfica Grafana cuenta con multitud de herramientas que nos permiten organizar nuestras visualizaciones de una forma sencilla y cómoda. Grafana puede ser utilizado en modo ‘Viewer’ o ‘Administrador’, siendo los privilegios del usuario distintos en cada uno de los casos. El primero de ellos solo tiene la opción de visualizar los distintos Dashboards y Paneles, sin opción a editarlos o añadir nuevos. En cambio, si nos encontramos en modo administrador (root) podremos desde añadir nuevos paneles, editarlos o eliminarlos, hasta modificar los priveligos de otros usuarios u organizaciones. Uno de los componentes principales de Grafana es el ‘Dashboard’ que no es más que una agrupación de paneles. Consideramos un panel a cualquier elemento que nos represente de forma gráfica nuestra información. Existen múltiples tipos de paneles (gráficas de tiempo, sectores cirulares, diagramas de barras, tablas, mapas, etc). A continuación, se explican en profundidad tanto el concepto de ‘Dashboard’ como de ‘Panel’, acompañado de una serie de imágenes que muestran su aspecto en la plataforma. E 10 Figura 8. JSON de respuesta a la petición colas de la centralita 3.3.3 Consulta agentes existentes en la centralita Figura 9. Petición agentes de la centralita 11 Figura 10. JSON de respuesta a la petición agentes de la centralita 3.4 Servicios implementados Este punto está destinado al análisis de los dos servicios implementados en JavaScript cuyo objetivo es recoger los datos devueltos por el servicio web, tratarlos de manera correcta, para posteriormente insertarlos en la tabla correspondiente de la Base de datos MySQL. Estos servicios serán automatizados gracias al administrador de procesos PM2 (Apéndice A.2). Finalmente se han implementado dos servicios independientes con el objetivo de disminuir la carga computacional y el procesado de los datos en las peticiones. Uno de ellos, en este caso el ‘Servicio 1’ sólo se ejecutará una vez ya que insertará en la Base de Datos los datos de las colas y de los agentes existentes en la centralita. Esto no es necesario ir actualizándolo puesto que son datos que no cambian con el tiempo y que se consideran constantes. En cambio, el segundo servicio (‘Servicio 2’), se irá ejecutando periódicamente y automáticamente gracias al administrador de procesos PM2 con el objetivo de recopilar los registros completos de las llamadas del día anterior a la ejecución del servicio. 12 3.4.1 Servicio 1 Como se ha comentado anteriormente, el objetivo de este script es insertar los datos referentes a las colas y a los agentes existentes en la centralita en las tablas de la Base de Datos diseñadas a tal efecto. Este script recibe el nombre de ‘servicio_colas_y_agentes.js’. A continuación, se describen los métodos y funciones necesarios para realizar la petición, obtener la respuesta e introducir los datos en la Base de datos. En primer lugar, las funciones que se muestran preparan y realizan la petición al servicio web (colas y agentes respectivamente). Figura 11. Servicio: Consulta agentes 13 Figura 12. Servicio 1: Consulta colas Una vez realizadas las consultas, el siguiente paso es preparar la conexión con la Base de Datos MySQL. Hay que destacar que, la Base de Datos, siempre va a ser local a Grafana debido a que estarán ubicadas en la misma máquina. Para indicar la ubicación de la Base de Datos utilizamos el campo ‘host’ de la conexión. Figura 13. Servicio 1: conexión a la BBDD Una vez establecida la conexión con la Base de Datos sólo nos queda parsear el JSON de respuesta y, mediante el uso de código SQL, introducir cada uno de los valores del JSON recibido en el campo correspondiente de la tabla de la Base de Datos. 14 Figura 14. Servicio 1: Inserción agentes y colas 3.4.2 Servicio 2 Como ya hemos mencionado, este script será ejecutado periódicamente mediante el administrador de procesos PM2. El objetivo principal de este servicio es recopilar registros completos de llamadas entre dos fechas dadas e insertarlos en la tabla correspondiente de la BBDD de la misma forma que el script anterior. Este script recibe el nombre de ‘servicio_llamadas.js’. En primer lugar, se establecen el número de registros que se quieren extraer de el Servicio Web. Por ejemplo, en el caso de la imagen adjunta podemos observar que se extraerán los primeros 10000 campos (‘start’, ‘stop’) desde el 03/02/2021 hasta el 28/02/2021 (‘from’, ‘to’) en orden ascendente (‘ascending’). Figura 15. Servicio 2: Petición Al igual que el ‘Servicio 1’, se prepara la cabecera, el cuerpo de la petición (en este caso estamos ante una petición POST) y la conexión con la Base de Datos. 15 Figura 16. Servicio 2: Petición de llamadas Una vez que se obtiene el JSON de respuesta por parte del servicio web, se parsea y se le aplican ciertos cambios (ajustes necesarios en cuanto a tipos de datos) previos a la inserción para que sean introducidos correctamente en la Base de Datos. Para ello, se hace uso de funciones implementadas en JavaScript. Algunos ejemplos son las siguientes: -getFormatoCorrectoFecha()-> recibe una fecha en milisegundos desde el 01/01/1970 y devuelve un string con el formato yyyy-mm-dd. --getFormatoCorrectoHora()-> recibe una fecha en milisegundos desde el 01/01/1970 y devuelve la hora (string) en el formato hh:mm:ss. -getSemanaAnio()-> recibe una fecha en milisegundos desde el 01/01/1970 y devuelve el número de semana dentro del año en la que se encuentra dicha fecha. -getTipoLlamada()-> recibe los valores origen y destino de los registros de la centralita y devuelve el tipo de llamada (entrante, saliente o interna). La distinción entre tipos de llamadas, es decir, cómo consideramos una llamada entrante, saliente, 16 perdida, contestada, etc, se hace en base a un fichero Excel que contiene diversas fórmulas para identificar las llamadas (Apéndice, A.1), así como algunos ejemplos de representaciones gráficas que se pueden llegar a conseguir con los datos de dichas llamadas. Además de introducir los registros de las llamadas, este servicio, inserta otros datos necesarios para las consultas SQL de Grafana en la tabla ‘regla_atendido’ (explicada más adelante) y actualiza una tabla en la que se almacena las llamadas, tanto entrantes como salientes, en las que interviene cada uno de los agentes (tabla ‘llamadastotalesagentes’, explicada en puntos posteriores). 17 4 PERSISTENCIA DE LA INFORMACIÓN n este capítulo vamos a describir la forma en la que se almacena toda la información recibida por la API de la centralita. Como hemos comentado en el punto anterior, los dos servicios programados en JavaScript tienen la función de recoger los datos de la centralita, mediante los servicios web que esta proporciona, para, tras tratarlos de forma conveniente, insertarlos en las distintas tablas existentes en la Base de Datos MySQL, facilitando a Grafana la recolección de los mismos para su representación. 4.1 Servidor de BBDD: MySQL El Sistema Gestor de Base de Datos elegido para dar soporte a la persistencia de la información durante el trabajo ha sido MySQL (v8.0). MySQL es uno de los gestores sde bases de datos más usados en la actualidad y uno de los más recurrentes a la hora de trabajar con Grafana, debido a la simplicidad de conexión y obtención de los datos. MySQL es el sistema gestor de bases de datos SQL más popular en el mundo. Es desarrollado, distribuido y soportado por Oracle. Es OpenSource, lo que significa que cualquiera puede usar y modificar el software, simplemente descargando el código fuente y adaptándolo a sus necesidades. El software de MySQL está basado en GPL (GNU General Public License) que define qué y qué no se debe hacer con el software en diferentes situaciones. El primer estándar de SQL nació en 1987 (Primer Estándar internacional de ISO-9075, SQL/86, SQL/87). Es un estándar normalizado (ANSI X3.135-1986 e ISO9075:1987). SQL es un lenguaje de consulta estructurado de alto nivel (SQL, Structured Query Language) y completo dirigido a bases de datos relacionales (dialogar con SGBBDD relacionales) desarrollado por IBM en 1981. Entre las funciones más importantes, SQL nos permite: definir esquemas (definición), actualizar y consultar datos (manipulación) y controlar dichos datos (control). Entre las ventajas de SQL podemos destacar las siguientes: -Velocidad en las operaciones (alto rendimiento). -Bajo consumo de recursos en la máquina. -Facilidad de configuración e instalación. -Soporte para varios Sistemas Operativos. -Conectividad y seguridad. MySQL, desde la versión 5.6, proporciona una herramienta gráfica para la gestión de servidores y bases de datos denominada ‘MySQLWorkbench’. Esta herramienta nos facilita, de una forma sencilla e intuitiva, numerosas funciones, entre las que podemos destacar las siguientes: E 18 -Desarrollo SQL: nos permite crear y administrar conexiones a servidores de bases de datos, así como la realización de consultas SQL mediante el editor. -Modelado de datos (diseño): nos facilita la creación de esquemas, tablas, vistas y diversas formas de organizar nuestros datos de manera gráfica, así como administrar y configurar todas sus opciones (columnas, tipos de datos, permisos, etc). -Administración del servidor: permite administrar nuestro propio servidor MySQL, usuarios y copias de seguridad (backups). -Migración de datos: facilita la migración de nuestros datos desde Microsoft SQL Server, Microsoft Acces, Sybase ASE, SQLite, etc. Además, nos permite la migración desde versiones anteriores hasta posteriores, incluso la actual. 4.2 Tablas existentes En MySQL las tablas son relacionales. Conceptualmente, el modelo relacional se basa en tablas, apoyado en una sólida base matemática (Álgebra Relacional (1970) y Cálculo relacional (1972)). Fue introducido por E.F Codd en 1970 y hoy en día es el principal modelo de datos implementable para aplicaciones comerciales. El modelo relacional proporciona un procedimiento para representar los datos y manipular dicha representación, ocupándose también de la integridad. A continuación, vamos a describir cada una de las tablas existentes en nuestra Base de datos, sus columnas, la información que almacenan y la procedencia de dicha información. 4.2.1 Tabla datos_centralita Esta tabla almacena la información general de las llamadas, la cual es extraída de la API de la centralita y es la base de la mayoría de las peticiones SQL que realiza Grafana a la Base de Datos. Es la tabla que más columnas posee, las cuales son las siguientes: -id_columna-> identificador único para cada fila/registro de la tabla. -UUID-> identificador único de cada llamada. -fecha-> día en el que se realizó la llamada. Sigue el formato yyyy-mm-dd. -hora-> hora en la que la llamada fue realizada. Sigue el formato hh:mm:ss. -did-> Número externo por el que entró la llamada. -origen-> origen de la llamada. -destino-> destino de la llamada. 19 -duracion-> duración total de la llamada en segundos (desde que el llamante la inicia hasta que origen o destino cuelgan). -tiempo_conversacion-> tiempo en segundos desde que el destinatario descuelga hasta que uno de los dos cuelga el teléfono (llamante o llamado). -terminada-> causa de cuelgue. -cola-> identificador de la cola (en el que caso de que la llamada haya estado en cola). -tiempo_espera_cola-> tiempo que la llamada ha estado en la cola (si la llamada ha pasado por una cola). -agente-> agente que ha atendido la llamada (si la llamada ha pasado por una cola). -terminada_en_cola-> causa de colgado en la cola (si la llamada ha pasado por una cola). -motivo-> razón de colgado en la cola (si la llamada ha pasado por una cola). -c2c-> si ha sido originada en c2c (boolean). -transfer_UUID-> identificador de la cola (si la llamada ha pasado por una cola). -grabada-> si la llamada ha sido grabada o no. 4.2.2 Tabla colas_centralita En esta tabla se almacenan las colas existentes en la centralita. Esta información, al igual que en la tabla anterior, también procede de la API. Sus columnas son las siguientes: -id-> identificador de la cola (único para cada cola). -nombre-> nombre de la cola. -dpNumber-> número de la cola. -dialplanNumber-> número del destino que entra en la cola. 4.2.3 Tabla agentes_centralita Esta tabla almacena toda la información relativa a los agentes existentes en la centralita. Esta información también es extráida de la API. Las columnas de esta tabla son las siguientes: -id-> identificador del agente (único). -extension-> identicador de dos cifras. -extensionName-> Nombre y Primer Apellido del agente. -nombre-> identificador de tres cifras. -estado-> si se encuentra disponible en ese momento. -breakType-> si se encuentra de descanso o no (tipo de descanso si procede). 26 4.4.3 getSalienteKo A partir de esta función podemos saber si una llamada es saliente y ha sido rechazada (llamada saliente perdida o no contestada). En el caso afirmativo, esta función devuelve el entero ‘1’. En caso contrario, devuelve ‘0’. Para ello hace uso de los parámetros TIPO_LLAMADA (devuelto por la función getTipoLlamada) y SALIENTE (función getSaliente). Figura 32. Función: getSalienteko 4.4.4 getLlamada Esta función nos permite saber si una llamada es entrante y contestada. Para ello recibe los parámetros TIPO_LLAMADA, terminada_en_cola y tiempo_conversacion (estos dos últimos son columnas de la tabla datos_centralita). Figura 33. Función: getLlamada 4.4.5 getNoContestada Con esta función podemos saber si la llamada en cuestión se trata de una llamada entrante y no contestada. En tal caso, obtenemos como valor de retorno el entero ‘1’ y en caso contrario ‘0'. Recibe los parámetros TIPO_LLAMADA, LLAMADA (devuelto por la función anterior) y la columna ‘terminada_en_cola’ de la tabla datos_centralita. Figura 34. Función: getNoContestada 4.4.6 getMes Esta función, a partir del número del mes (1-12), devuelve el mes en letras y en castellano (ENERODICIEMBRE). Figura 35. Función: getMes 4.4.7 getFranja30segs Esta función asigna el valor ‘1’ a la primera media hora de cualquier hora y el valor ‘2’ a la segunda media hora. Para ello, solo es necesario recibir como parámetro los minutos de la hora en cuestión. Figura 36. Función: getFranja30segs 27 4.4.8 getDiaSem Con esta función Podemos hacer la conversión entre el número del día dentro de la semana con su nombre en letras. SQL con la función dayofweek(fecha) obtiene el número del día en la semana (1-6). Esta función, recibe ese parámetro y le asocia el nombre del día de la semana en castellano (de Domingo (0) a Sábado (6)). Figura 37. Función: getDiaSem 4.4.9 getTurno Esta función, a partir del parámetro franjahora, el cual se corresponde con los dos dígitos de la hora en cuestión, establece si un turno es de mañana o de tarde. Figura 38. Función: getTurno 4.4.10 getInternoDestino Esta función, es útil para otras funciones que necesitan recurrir a ella. Recibe como parámetro el ‘destino’ de la columna de datos_centralita y consultando la tabla tipos_destino (ver 4.2.7) devuelve la segunda columna de la misma, la cual se corresponde con el tipo del destino en cuestión (interno, externo, …). Figura 39. Función: getInternoDestino 4.4.11 getConcaOff Esta función, al igual que la anterior, es de utilidad para otras funciones. Recibe los parámetros NOCONTESTADA (función getNoContestada), TIPOLLAMADA (función getTipoLlamada), ‘origen’ e ‘id’ (estos dos últimos de la tabla datos_centralita). Devuelve la cadena “NO CONTACTO”, “” (cadena vacía) o la hora de la primera fila de la tabla ‘datos_centralita’ donde el destino coincida con el origen pasado como parámetro. Figura 40. Función: getConcaOff 28 4.4.12 getAtendidoDespues Esta función, al igual que las anteriores, también la necesitan otras funcionas para subconsultas. Sus parámetros son NO_CONTESTADA, TIPO_LLAMADA, origen, fecha e id. Tras realizar varias comprobaciones con los parámetros puede devolver “NO CONTACTO”, “” ó el campo regla_atendido de la propia tabla regla_atendido. Figura 41. Función: getAtendidoDespues. 4.4.13 getDiaSaliente Al igual que las anteriores, esta función también le es de utilidad a otras para diversas consultas. A partir de los parámetros ATENDIDO_DESPUES (devuelto por la función anterior), origen, fecha e id, devuelve tres posibles valores: “”, “NO CONTACTO” ó el primer día de la tabla datos_centralita donde el destino de la llamada sea igual al origen pasado como parámetro. Figura 42. Función: getDiaSaliente 4.4.14 getTiempoRespuesta Esta función también es útil para subconsultas de otras funciones. Recibiendo los parámetros ATENDIDO_DESPUES, DIA_SALIENTE, CONCA_OFF, hora y fecha (estos dos últimos de la tabla datos_centralita), devuelve cuatro posibles valores: “DIA SIGUIENTE”, “”, “NO CONTACTO” ó la diferencia de tiempo entre los parámetros CONCA_OFF y hora. Figura 43. Función: getTiempoRespuesta 4.4.15 getFechaAnterior Esta función nos permite obtener la fecha del registro inmediatamente anterior. Para ello, solo es necesario el identificador del registro/fila en cuestión. Figura 44. Función: getFechaAnterior 29 4.4.16 getId Esta función simplemente nos devuelve el identificador del registro que cumpla con los valores pasado como parámetros, los cuales son únicos en cada registro. Esto significa que no pude haber dos registros con los mimos valores de fecha, hora y UUID. Figura 45. Función: getId 4.4.17 getSemana Esta función nos devuelve la cadena “Semana __” siendo dicho espacio la semana dentro del año, la cual es pasada como parámetro a la función. Esta función es de utilidad para Grafana para representar las leyendas con dicha etiqueta. Figura 46. Función: getSemana 4.4.18 getLlamadasEntrantesAgente Esta función es invocada a la hora de actualizar la tabla llamadastotalesagentes. A partir del nombre del agente (recibido como parámetro), haciendo una consulta a la tabla datos_centralita, devuelve el número de llamadas entrantes atendidas por dicho agente. Figura 47. Función: getLlamadasEntrantesAgente 4.4.19 getLlamadasSalientesAgente Esta función, al igual que la anterior, es invocada a la hora de actualizar la tabla llamadastotalesagentes. A partir del nombre del agente (recibido como parámetro), haciendo una consulta a la tabla datos_centralita, devuelve el número de llamadas salientes realizadas por dicho agente. Figura 48. Función: getLlamadasSalientesAgente 30 4.4.20 getNombreAgente Esta función, útil para otras funciones, simplemente devuelve el nombre del agente a partir de su identificador, el cual es pasado como parámetro. Figura 49. Función: getNombreAgente 31 5 REPRESENTACIÓN DE LOS DATOS En este último punto se describirán cada una de las representaciones realizadas en Grafana, junto con una breve descripción de lo que podemos ver en cada una de ellas. Para ello se ha dividido el análisis en tres partes: Análisis Mensual, Análsisis Semanal y Análisis Diario. 5.1 Análisis Mensual La representación de los datos en el Análisis Mensual, como su propio nombre indica, estará organizado por meses, es decir, los datos mostrados estarán agrupados por los meses del año existentes en ese momento en la Base de Datos. Además, en este punto, se verán una serie de representaciones relacionadas con los agentes existentes en la centralita (desentendiéndonos del análisis mensual). 5.1.1 Llamadas Entrantes En las dos siguientes figuras podemos ver dos diagramas de sectores que nos muestran las llamadas entrantes contestadas (Figura 50) y las llamadas entrantes no contestadas (Figura 51) por cada mes del año. Hay que recalcar que sólo se muestran esos meses porque en ese momento sólo hay fechas hasta el mes de mayo y el mes de marzo no estaba cargado en ese momento. Figura 50. Representación Mensual: Llamadas Entrantes Contestadas 32 Figura 51. Representación Mensual: Llamadas Entrantes No Contestadas En la Figura 52 observamos en un mismo diagrama de barras tanto las llamadas entrantes contestadas como no contestadas para cada uno de los meses, es decir, mostramos los datos de los diagramas anteriores en un solo diagrama de barras. A la vista de los resultados, podemos concluir que el mes en el que se contestaron más llamadas desde la centralita fue abril. Estos resultados se obtienen en tiempo real mediante peticiones SQL a la Base de Datos. Figura 52. Representación Mensual: Llamadas Entrantes 33 5.1.2 Llamadas Salientes Al igual que en el punto anterior, aquí podemos apreciar dos sectores circulares correspondientes a las llamadas salientes contestadas (Figura 53) y no contestadas (Figura 54), mientras que en la Figura 55 observamos un diagrama de barras donde se representan ambos tipos de llamadas para cada uno de los meses. Figura 53. Representación Mensual: Llamadas Salientes Contestadas Figura 54. Representación Mensual: Llamadas Salientes No Contestadas 34 Figura 55. Representación Mensual: Llamadas Salientes 5.1.3 % Llamadas Entrantes No Contestadas A continuación, podemos apreciar el % de las llamadas entrantes no contestadas para cada mes, es decir, que porcentaje del total de llamadas para cada uno de los meses se corresponde con llamadas no contestadas (perdidas). Podemos verificar el resultado, observando que en el mes de enero obtenemos el mayor porcentaje, lo que corrobora a lo obtenido en la Figura 51. Los resultados son obtenidos gracias al procedimiento almacenado ‘porcNoContestadas’. El cálculo se corresponde con la fórmula matemática (aplicable a cada mes): 𝐿𝑙𝑎𝑚𝑎𝑑𝑎𝑠 𝐸𝑛𝑡𝑟𝑎𝑛𝑡𝑒𝑠 𝑁𝑜 𝐶𝑜𝑛𝑡𝑒𝑠𝑡𝑎𝑑𝑎𝑠 𝑇𝑜𝑡𝑎𝑙 𝐿𝑙𝑎𝑚𝑎𝑑𝑎𝑠 𝐸𝑛𝑡𝑟𝑎𝑛𝑡𝑒𝑠 Figura 56. Representación Mensual: % Llamadas Entrantes No Contestadas 35 5.1.4 % Llamadas Salientes VS Llamadas Entrantes No Contestadas En la Figura 57 observamos una representación correspondiente al % de llamadas salientes frente a las llamadas entrantes no contestadas. Para la obtención de la misma se ha recurrido al procedimiento almacenado ‘PorcSalientesVSNoContestadas’ (ver punto 4.3.6). Podemos llegar a la conclusión de que, en todos los meses, se han perdido más llamadas entrantes que todas las salientes producidas debido a que no se supera el 100% en ninguno de los meses. Figura 57. Representación Mensual: % Llamadas Salientes VS Entrantes No Contestadas 42 5.2 Análisis Semanal Una vez visto el análisis mensual de las llamadas, podemos pasar al análisis semanal, es decir, aquel cuyos datos van a poder ser visualizados por semanas. 5.2.1 Llamadas entrantes En el diagrama de barras que aparece a continuación observamos el total de llamadas entrantes que se producen para cada semana del año (siempre que la fecha exista en la base de datos). Nota: debido a que la leyenda es muy extensa, esta no puede apreciarse completamente. Figura 68. Representación Semanal: Llamadas Entrantes 5.2.2 Llamadas salientes En el diagrama de barras que aparece a continuación observamos el total de llamadas salientes que se producen para cada semana del año (siempre que la fecha exista en la base de datos). Nota: debido a que la leyenda es muy extensa, esta no puede apreciarse completamente. 43 Figura 69. Representación Semanal: Llamadas Salientes 5.2.3 Llamadas entrantes (semana y franja horaria) En la siguiente figura, se observa una fracción de la tabla que nos muestra para cada día el total de llamadas entrantes que se han producido fraccionado por las horas de trabajo de la centralita, junto con el día de la semana y la semana del año a la que pertenece dicho día. Figura 70. Representación Semanal: Llamadas Entrantes por semana y franja horaria 44 5.3 Análisis Diario En este análisis los datos van a venir representados para cada uno de los días de todos los meses existentes en la Base de Datos. 5.3.1 Llamadas Entrantes En la siguiente figura observamos una gráfica cuyo eje de abcisas es el tiempo (desde el 1 de abril al 5 de mayo), mientras que el eje de ordenadas se corresponde con el número de llamadas entrantes. Podemos observar la evolución de las llamadas entrantes contestadas (color verde) y no contestadas (color amarillo) junto con sus valores totales durante dicho intervalo de tiempo. El día en el que más llamadas entrantes se contestaron desde la centralita fueron 421, mientras que el día con más llamadas entrantes no contestadas se produjeron 222. Figura 71. Representación Diaria: Llamadas Entrantes Por su parte, la Figura 72, nos muestra la superposición de las curvas anteriores, es decir, la suma de las llamadas entrantes contestadas y no contestadas, que se corresponde con el total de llamadas entrantes, para el mismo intervalo de tiempo. El pico está en 641 llamadas y durante dicho tiempo se produjeron 9220 llamadas 45 Figura 72. Representación Diaria: Llamadas totales entrantes 5.3.2 Llamadas Salientes En la siguiente figura observamos una gráfica cuyo eje de abcisas es el tiempo (desde el 1 de abril al 5 de mayo), mientras que el eje de ordenadas se corresponde con el número de llamadas salientes. Podemos observar la evolución de las llamadas salientes contestadas (color verde) y no contestadas (color amarillo) junto con sus valores totales durante dicho intervalo de tiempo. El día en el que más llamadas salientes se produjeron desde la centralita fueron 60, mientras que el día con más llamadas salientes no contestadas fueron 29. 46 Figura 73. Representación Diaria: Llamadas Salientes Por su parte, la Figura 74, nos muestra la superposición de las curvas anteriores, es decir, la suma de las llamadas salientes contestadas y no contestadas, que se corresponde con el total de llamadas salientes, para el mismo intervalo de tiempo. El pico está en 89 llamadas y durante dicho tiempo se produjeron 931 llamadas. Figura 74. Representación Diaria: Llamadas totales salientes 47 5.3.3 Llamadas entrantes y salientes (día y franja horaria) Para acabar con el análisis diario lo hacemos con la siguiente porción de tabla. En ella podemos observar tanto llamadas entrantes (contestadas y no contestadas) como llamadas salientes (contestadas y no contestadas). Además, la tabla nos permite ver, para cada día, un desglose de cada una de las llamadas en intervalos de media hora. Figura 75. Representación Diaria: Llamadas Entrantes y Salientes 48 APÉNDICE En el apéndice se aborda la explicación del fichero Excel que ha servido como base para el estudio de las llamadas de la centralita, una descripción del administrador de procesos PM2 utilizado para la automatización de los servicios y un resumen sobre las dificultades encontradas durante la realización de este trabajo. A.1 Excel: datos sobre las llamadas Este fichero Excel, proporcionado antes de comenzar con el proyecto, ha sido la base para entender como podemos reconocer un tipo de llamada u otro. En él, podemos ver las fórmulas necesarias para obtener cada tipo de llamada en función de los campos de los registros de la centralita. Este fichero contiene una serie de tablas dinámicas e ideas de gráficas y diagramas que se pueden llegar a representar con los datos de la centralita obtenidos de la API. Por ejemplo, en las siguientes figuras podemos ver tanto las fórmulas que indican la obtención de cada campo (formato Excel), como una serie de tablas y gráficas representativas de dichas tablas. Figura 76: Excel: Llamadas Entrantes y Salientes por meses En la imagen anterior podemos ver, en la parte superior de la pantalla, seleccionando el campo, la fórmula que nos permite calcular el valor de dicho campo. Además, podemos ver dos gráficas representativas sobre las llamadas entrantes (contestadas y no contestadas) y llamadas salientes (contestadas y no contestadas) a lo largo del año. 49 Figura 77. Excel: Llamadas Entrantes y Salientes por semana Figura 78. Excel: Llamadas Entrantes y Salientes por día En la siguiente imagen, podemos apreciar una tabla que nos muestra las llamadas entrantes por día y franja horaria en el mes de enero. Como cualquier hoja Excel, para cada campo podemos ver de donde procede su valor, para así ir conociendo la forma en la que podemos obtener las distintas métricas para las llamadas. 50 Figura 79. Excel: Llamadas Entrantes por franja y día Por último, la hoja Excel también nos hace un análisis sobre la participación de los distintos agentes existentes en la centralita sobre el total de llamadas. Como vemos a continuación, podemos apreciar que Cristina Barradas es el agente que mas gestiones ha realizado. Figura 80. Excel: Gestiones de cada agente. 51 A.2 PM2 PM2 o Process Manager 2 es un administrador de procesos de producción de Node.js. de código abierto que permite a los desarrolladores administrar y monitorizar aplicaciones Node.js en el entorno de producción. Fue inicado por Alexandre Strzelewicz en 2013 y actualmente su código fuente puede descargarse libremente a través de Github. En este trabajo hemos usado PM2 para automatizar ambos servicios implementados en JavaScript. PM2 nos va a permitir ejecutar ambos ficheros periódicamente y de forma automática. Para ser más exactos, PM2 va a ejecutar el Servicio 2 (‘servicio_llamadas.js’), todas las noches a las 2:00am una sola vez. Con esto conseguimos que dicho servicio cargue en la Base de Datos MySQL los registros completos del día anterior. Con esto conseguimos despreocuparnos de la ejecución de los ficheros puesto que PM2 lo hará por nosotros. Recalcar que, como se ha comentado en un punto anterior, el primer servicio implementado en Java Script sólo es necesario ejecutarlo una vez, puesto que sus datos vamos a considerarlos estáticos, es decir, que no cambian con el tiempo. Para más información sobre PM2 puede consultar la referencia [9]. A.3 Dificultades encontradas Para finalizar esta memoria se van a describir las dificultades encontradas durante la realización del trabajo. En primer lugar, como se ha comentado anteriormente, toda la correspondencia entre los tipos de llamadas y los datos existentes en la centralita ha sido gracias al fichero Excel. Respecto a él, recalcar que han surgido complicaciones a la hora de estudiarlo y entender su contendio, sobre todo los primeros días, debido a la gran cantidad de información y al peso del mismo. Otro punto importante del trabajo ha sido adaptar todos los tipos de datos devueltos por la API a la Base de Datos MySQL, ya que son plataformas totalmente distintas. Todo este proceso ha sido desarrollado mediante las distintas funciones implementadas en JavaScript y que son ejecutadas antes de insertar los datos en la Base de Datos. Esto implica un aumento del procesamiento y tiempo de ejecución de ambos servicios implementados en JavaScript. En cuanto a las peticiones realizadas por Grafana, la mayoría de ellas son gracias a funciones implementadas en código SQL, lo cual también ha sido complicado de realizar debido al hecho de que cada una de ellas han tenido que ser adaptadas a SQL, sabiendo su definición en formato Excel. Dichas peticiones, al ser en tiempo real, ralentizan el tiempo de procesamiento y visualización debido a la gran cantidad de datos que se manejan en cada consulta.