Visualisieren, worüber wir am Telefon mit Grafana gesprochen haben

Brunnen, en este post veremos algo como siempre distinto y que podemos sencillamente añadir a nuestra instalación de Grafana, y no es más que explotar los datos de uso de nuestras llamadas de teléfono. Hace tiempo que lo uso y quería compartirlo por si os interesase, tenía curiosidad de cuánto hablaba al teléfono móvil, con quién, lo que me llamaban y yo llamaba, verlo por horarios, personas… mit Nougat!

Lo haremos mediante una app instalada en el móvil, como es la mítica IFTTT guardaremos en hojas de cálculo de Google Spreadsheet registros de las llamadas perdidas que tenemos, las llamadas que recibimos 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 (das 7) podemos consultar hojas de Google Spreadsheet directamente, pero en esta ocasión os mostraré otra manera, 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 propio 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:

Automatically log the calls you receive to your Android phone to a Google Spreadsheet
Automatically log the calls you make on your Android phone to a Google Spreadsheet
Call Log, Missed call

Configuraremos cada una de ellas para que nos guarde en una hoja de cálculo distinta todos estos registros. Recordar en el móvil indicar que esta app pueda seguir ejecutándose en segundo plano y evitar que la batería del móvil le haga reestricciones, así siempre estará ejecutándose y almacenando en tiempo real nuestros datos.

Brunnen, 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, (Ich) 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 &AMPERE;Gt; llamadas_realizadas.csv1
sed 's/"//g' llamadas_realizadas.csv1 &AMPERE;Gt; llamadas_realizadas.csv
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "LOAD DATA LOCAL INFILE 'llamadas_realizadas.csv' INTO TABLE llamadas_realizadas FIELDS TERMINATED BY ','"

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

python DescargaFicheroDesdeGoogleDrive.py llamadas_perdidas.ini
sed 's/Missed,//g' llamadas_perdidas.csv &AMPERE;Gt; llamadas_perdidas.csv1
sed 's/,,/,/g' llamadas_perdidas.csv1 &AMPERE;Gt; llamadas_perdidas.csv2
awk '{gsub(/,/,"",$2)}1' llamadas_perdidas.csv2 &AMPERE;Gt; llamadas_perdidas.csv3
sed 's/"//g' llamadas_perdidas.csv3 &AMPERE;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

Wie wir sehen können, 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, Beispiel: “https://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 tenemos que crear un proyecto y dar acceso mediante OAuth 2.0, Abreise: 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 ejecute con la perioricidad que nos interese.

Esto como veréis se almacena en 3 tablas distintas dentro de un MySQL, os dejo el código para crear una tabla de ejemplo, son las 3 tablas iguales, excepto que la de llamadas_perdidas no tiene columna de Duración, und wie wir sehen, 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',
'name' ZEICHEN(50) NULL COLLATE 'utf8mb4_general_ci',
`duracion` FLOAT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

Brunnen, pues con todo esto listo, ya podríamos en Grafana hacer nuestros 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 periodo de fecha que seleccionemos, así podremos ver por ejemplo los datos de la última semana, del mes… y vais a alucinar el tiempo que pasamos al aparato colgados. Y podemos visualizar los datos como queramos, una gráfica de los tiempos hablados que al pasar el ratón nos dirá a quién, o una tabla del tiempo total acumulado, así como unos quesitos donde veremos con quién nos pasamos más al teléfono.

Os dejo algunos ejemplos que tengo en esta imagen y aquí os pego las queries que les tengo hechas, Natürlich, acordaros primeramente de hacer el conector desde Grafana a vuestra BD de MySQL, desde los “Datenquellen”.

Total al teléfono:

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

Total llamado:

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

Total Recibido:

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

Total llamadas recibidas:

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

Total llamadas realizadas:

SELECT count(*) FROM llamadas_realizadas WHERE STR_TO_DATE(cuando, '%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(cuando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(cuando, '%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(cuando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(cuando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Métrica llamadas realizadas:

AUSWÄHLEN 0 als Wert, nombre as metric, DATE_SUB(timestamp(str_to_date(cuando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_perdidas WHERE STR_TO_DATE(cuando, '%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:

Auswählen (@sum := @sum + duracion) als Wert, "Realizadas" als Metrik, DATE_SUB(timestamp(str_to_date(cuando, '%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(cuando, '%M %d %Y at %l:%i%p') $__timeFilter( );

Métrica llamadas recibidas:

Auswählen (@sum := @sum + duracion) als Wert, "Recibidas" als Metrik, DATE_SUB(timestamp(str_to_date(cuando, '%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(cuando, '%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(cuando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(cuando, '%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(cuando, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(cuando, '%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… Was ich gesagt habe, wie gewöhnlich, gracias por vuestro tiempo!