Visualizando o que falamos ao telefone com Grafana

Poço, neste post vamos ver algo como sempre diferente e que podemos simplesmente adicionar à nossa instalação Grafana, e nada mais é do que explorar os dados de utilização das nossas chamadas telefónicas. Eu tenho usado por um longo tempo e eu queria compartilhá-lo no caso de você está interessado, Eu estava curioso o quanto eu falava no meu celular, com quem, o que me chamavam e eu chamava, Veja por horários, Pessoas… com nougat!

Lo haremos mediante una app instalada en el móvil, como es la mítica IFTTT guardaremos en folhas de cálculo de Google Spreadsheet registos de las llamadas perdidas que tenemos, las llamadas que recebemos y las que realizamos, en las mismas hojas se almacenará el nombre del contacto, su número de teléfono y la duración de cada llamada, además de cuándo. Ahora con la nueva versión de Grafana (o 7) podemos consultar hojas de Google Spreadsheet directamente, pero nesta ocasião os mostraré de outra forma, ya que llevo tiempo usándolo y esto todavía no lo tengo desarrollado 🙂 Lo que hasta ahora hago es, mediante una tarea programada con un cron en una máquina linux (podría ser el próprio Grafana), pues me descargo las hojas de cálculo y luego las vuelco a una base de datos de MySQL. Y será esa BD la que visualicemos con Grafana, pudiendo diseñar nuestro Dashboard a nuestro gusto!

Comenzamos con lo dicho, en nuestro móvil instalaremos IFTTT, y añadiremos tres automatizaciones:

Regista automaticamente as chamadas que recebes para o teu telemóvel Android numa folha de cálculo Google
Registar automaticamente as chamadas que fizer no seu telemóvel Android numa folha de cálculo Google
Registo de chamadas, Chamadas perdidas

Configuraremos cada uma de ellas para que nos guarde en uma folha de cálculo distinta todos estes registos. Registar em o móvel indicar que esta app possa seguir executándose no segundo plano e evitar que a bateria del móvil le haga reestricciones, así siempre estará ejecutándose y almacenando en tiempo real nuestros datos.

Poço, arrancamos con los scripts que he ido usando hasta ahora, primero necesitaremos este script de Python que nos permite descargar ficheros de Google Drive (DescargaFicheroDesdeGoogleDrive.py) y luego basta con crear un script mismamente con bash que lo que hace es, (eu) vacía las tablas de MySQL donde almacenaremos los registros de las llamadas, (Ii) se descarga cada SpreadSheet y las almacena en local en CSV, y como veis las transforma o elimina caracteres que sobran:

BajaFicheroGoogleDriveYmeteEnCSV.sh

#!/bin/bash
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "TRUNCATE TABLE llamadas_realizadas"
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "TRUNCATE TABLE llamadas_recibidas"
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "TRUNCATE TABLE llamadas_perdidas"

python DescargaFicheroDesdeGoogleDrive.py llamadas_realizadas.ini
awk '{GSUB(/,/,"",$2)}1' llamadas_realizadas.csv &AMP;Gt; llamadas_realizadas.csv1
sed 's/"//g' llamadas_realizadas.csv1 &AMP;Gt; llamadas_realizadas.csv
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "CARREGAR DADOS LOCAL NO FICHEIRO 'llamadas_realizadas.csv' NOS CAMPOS llamadas_realizadas TABELA TERMINADOS POR ','"

python DescargaFicheroDesdeGoogleDrive.py llamadas_recibidas.ini
awk '{GSUB(/,/,"",$2)}1' llamadas_recibidas.csv &AMP;Gt; llamadas_ recibidas.csv1
sed 's/"//g' llamadas_recibidas.csv1 &AMP;Gt; llamadas_recibidas.csv
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "LOAD DATA LOCAL INFILE 'llamadas_recibidas.csv' NA TABELA llamadas_recibidas CAMPOS TERMINADOS POR ','"

python DescargaFicheroDesdeGoogleDrive.py llamadas_perdidas.ini
sed 's/Missed,//g' llamadas_perdidas.csv &AMP;Gt; llamadas_perdidas.csv1
sed 's/,,/,/g' llamadas_perdidas.csv1 &AMP;Gt; llamadas_perdidas.csv2
awk '{GSUB(/,/,"",$2)}1' llamadas_perdidas.csv2 &AMP;Gt; llamadas_perdidas.csv3
sed 's/"//g' llamadas_perdidas.csv3 &AMP;Gt; llamadas_perdidas.csv
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD  "LOAD DATA LOCAL INFILE 'llamadas_perdidas.csv' INTO TABLE llamadas_perdidas FIELDS TERMINATED BY ','"

Como veis el script de Python nos pide un fichero ini por cada SpreadSheet que queremos descargar, uno para las llamadas realizadas, otro para las recibidas y otro para las perdidas. Este sería el formato de cada fichero, ejemplo de “llamadas_realizadas.ini”

[fileDownloadFromDrive]
clientsecretkeypath = /PATH/api_gugel.json
spreadsheetid =
filename = llamadas_realizadas.csv
filepath = /PATH/
mimetype = text/csv

Como podemos ver, por un lado necesitamos el ID de la Sheet de Google, esa parte es muy sencilla, bastará con abrir la hoja con un navegador y en la URL os vendrá el ID, Exemplo: “https (em inglês)://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXXXXXXX/edit#gid=0”. Y la parte un poco más laboriosa es conseguir el fichero con los credenciales de acceso mediante la API, que es el fichero JSON. Como es un proceso largo y que alguno ya conocerá, que es más lo hemos visto en algún otro post, creo que lo mejor es dejaros en esta parte la documentación oficial de Google, donde nos explicará que temos que criar um projeto y dar acesso mediante OAuth 2.0, Fazer Check-out: https://developers.google.com/sheets/api/guides/authorizing. Una vez tengamos los IDs de cada Sheet y el JSON con los credenciales de acceso podrás probar el script de arriba, una vez validado es cuestión de meterlo en un cron y que se execute con la perioricidad que nos interessae.

esto como veréis se almacena en 3 tablas distintas dentro de un MySQL, os dejo el código para criar una tabla de ejemplo, son las 3 tablas iguales, excepto que la de llamadas_perdidas no tiene coluna de Duración, y como vemos, almacena el cuando fue la llamada, el número de teléfono, el nombre de cómo lo tenemos en la agenda de nuestro móvil y la duración de la llamada:

CREATE TABLE 'llamadas_realizadas' (
'cuando' CHAR(50) NULL COLLATE 'utf8mb4_general_ci',
'telefono' CHAR(50) NULL COLLATE 'utf8mb4_general_ci',
'nombre' CHAR(50) NULL COLLATE 'utf8mb4_general_ci',
'duracion' FLOAT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

Poço, pues con todo esto listo, ya podríamos en Grafana fazer os nossos dibujitos como nos de la gana, cómo vemos, tenemos unos campos de texto donde vemos el total al teléfono, lo que hemos llamado nosotros, lo que nos han llamado, el total de llamadas recibidas o las realizadas. Ojo que todo esto se filtrará con el período de fecha que seleccionemos, así podremos ver por exemplo los dados de la última semana, del mes… y vais a alucinar el tiempo que passamos al aparato colgados. Y podemos visualizar los datos como queramos, una gráfica de los tiempos falados que al pasar el ratón nos dirá a quién, o una tabla del tiempo total acumulado, así como unos quesitos donde veremos com quem nos pasamos más al teléfono.

Os dejo alguns exemplos que tengo en esta imagem y aqui os pego las quaestioes que les tengo hechas, É claro, acordaros primeramente de hacer el conector desde Grafana a vuestra BD de MySQL, desde los “Fontes de dados”.

Total al teléfono:

SELECT sum(Duracion)
FROM (
SELECT SUM(duracion) as Duracion FROM llamadas_realizadas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter()
union all
SELECT SUM(duracion) as Duracion FROM llamadas_recibidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter()
) Para

Total llamado:

SELECT SUM(duracion) FROM llamadas_realizadas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter()

Total Recibido:

SELECT SUM(duracion) FROM llamadas_recibidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter()

Total llamadas recibidas:

SELECT count(*) FROM llamadas_recibidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter()

Total llamadas realizadas:

SELECT count(*) FROM llamadas_realizadas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( )

Gráfica de las llamadas y su duración (la de los puntitos)
Metrica llamadas realizadas:

SELECT duracion as value, nombre as metric, DATE_SUB(TIMESTAMP(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Métrica llamadas recibidas:

SELECT duracion as value, nombre as metric, DATE_SUB(TIMESTAMP(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Métrica llamadas realizadas:

SELECIONAR 0 como valor, nombre as metric, DATE_SUB(timestamp(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_perdidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Gráfica de la duración de las llamadas recibidas y realizadas

Métrica llamadas realizadas:

select (@sum := @sum + duracion) como valor, "Realizadas" como métrica, DATE_SUB(timestamp(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas cross join (select @sum := 0) params WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( );

Métrica llamadas recibidas:

select (@sum := @sum + duracion) como valor, "Recibidas" como métrica, DATE_SUB(timestamp(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas cross join (select @sum := 0 ) params WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( );

Quesito de Llamadas recibidas:

SELECT duracion as value, nombre as metric, DATE_SUB(timestamp(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Quesito de llamadas realizadas:

SELECT duracion as value, nombre as metric, DATE_SUB(timestamp(str_to_date(quando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(quando, '%M %d %Y at %l:%i%p') $__timeFilter( )ORDER BY time_sec ASC;

Espero que os interese y os pueda servir para algo, no sólo esta manera de realizarlo, si no para otras ideas y queráis explotar esos datos, dentro de poco os pondré otro ejemplo, en este caso de los correos electrónicos, igual es interesante saber cuántos mails recibimos o enviamos… O que eu disse, como de costume, gracias por vuestro tiempo!