Visualitzant el que parlem per telèfon amb Grafana
Bo, en este post veremos algo como siempre distinto y que podem senzillament afegir a la nostra instal·lació 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… al torró!
Lo haremos mediante una app instalada en el móvil, com es la mítica IFTTT guardaremos en hojas de cálculo de Google Spreadsheet registres 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, a més de quan. Ara, amb la nova versió de Grafana (la 7) podem consultar fulls de Google Spreadsheet directament, però en aquesta ocasió us mostraré una altra manera, ja que fa temps que l'estic utilitzant i això encara no ho tinc desenvolupat 🙂 El que fins ara faig és, mitjançant una tasca programada amb un cron en una màquina Linux (podria ser el propi Grafana), doncs em descarrego els fulls de càlcul i després els bolco a una base de dades MySQL. I serà aquesta BD la que visualitzarem amb Grafana, podent dissenyar el nostre Dashboard al nostre gust!
Comencem amb el que hem dit, al nostre mòbil instal·larem IFTTT, i afegirem tres automatitzacions:
- Registrar automàticament les trucades que reps al teu telèfon Android en un Google Spreadsheet
- Registrar automàticament les trucades que facis al teu telèfon Android a un full de càlcul de Google
- Registre de trucades, Trucada perduda
Configurarem cadascuna d’elles perquè ens guardi en un full de càlcul diferent tots aquests registres. Recorda al mòbil indicar que aquesta aplicació pugui continuar executant-se en segon pla i evitar que la bateria del mòbil li faci restriccions, així sempre estarà executant-se i emmagatzemant en temps real les nostres dades.
Bo, comencem amb els scripts que he anat utilitzant fins ara, primer necessitarem aquest script de Python que ens permet descarregar fitxers de Google Drive (DescarregaFitxerDesDeGoogleDrive.py) i després només cal crear un script amb bash que el que fa és, (i) buida les taules de MySQL on emmagatzemarem els registres de les trucades, (ii) es descarrega cada SpreadSheet i les magatzems en local en CSV, i com veis les transforma o elimina els caràcters 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 > llamadas_realizadas.csv1
sed 's/"//g' llamadas_realizadas.csv1 > llamadas_realizadas.csv
mysql -h SERVIDOR_MYSQL -uUSUARIO -pCONTRASEÑA NOMBRE_BD -e "CARREGA L'ARXIU LOCAL DE DADES 'llamadas_realizadas.csv' A LA TAULA llamadas_realizadas CAMPS ACABATS PER ','"
python DescargaFicheroDesdeGoogleDrive.py llamadas_recibidas.ini
awk '{gsub(/,/,"",$2)}1' llamadas_recibidas.csv > llamadas_recibidas.csv1
sed 's/"//g' llamadas_recibidas.csv1 > 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 > llamadas_perdidas.csv1
sed 's/,,/,/g' llamadas_perdidas.csv1 > llamadas_perdidas.csv2
awk '{gsub(/,/,"",$2)}1' llamadas_perdidas.csv2 > llamadas_perdidas.csv3
sed 's/"//g' llamadas_perdidas.csv3 > 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 seria el format de cada fichero, ejemplo de “llamadas_realizadas.ini”
[fileDownloadFromDrive] clientsecretkeypath = /PATH/api_gugel.json spreadsheetid = filename = llamadas_realizadas.csv filepath = /PATH/ mimetype = text/csv
Com veiem, 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, exemple: “https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXXXXXXX/edit#gid=0”. Y la part d'un poc més laboriós es va aconseguir el fitxatge amb les credencials d'accés mitjançant l'API, que és el fitxer JSON. Com és un procés llarg i que algú ja coneixrà, que és més ló que hem vist en algun altre post, crec que el millor es dejaros en aquesta part de la documentació oficial de Google, on ens explicarà que hem de crear un projecte i donar accés mitjançant OAuth 2.0, fica una ullada a: https://developers.google.com/sheets/api/guides/authorizing. Un cop tinguem els IDs de cada full i el JSON amb les credencials d'accés podràs provar l'script de dalt, un cop validat és qüestió de posar-lo en un cron i que s'executi amb la periodicitat que ens interessi.
Això, com veureu, s'emmagatzema en 3 taules diferents dins d'un MySQL, us deixo el codi per crear una taula d'exemple, són les 3 taules iguals, excepte que la de llamadas_perdidas no té columna de Durada, i com veiem, emmagatzema quan va ser la trucada, el número de telèfon, el nom com el tenim a l'agenda del nostre mòbil i la durada de la trucada:
CREATE TABLE `llamades_realitzades` ( `quan` CHAR(50) NULL COLLATE 'utf8mb4_general_ci', `telefon` CHAR(50) NULL COLLATE 'utf8mb4_general_ci', 'nom' CHAR(50) NULL COLLATE 'utf8mb4_general_ci', `durada` FLOAT NULL ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
Bo, pues con todo esto listo, ya podríamos en Grafana hacer nuestros dibujitos como nos de la gana, cómo vemos, tenemos uns camps de text on vemos el total al telèfon, lo que hem cridat nosaltres, lo que nos han llamado, el total de llamadas rebuts o les realitzades. Ojo que tot això es filtrarà amb el període de data que seleccionem, així podremos veure per exemple les dades de la darrera setmana, del mes… i va a alucinar el temps que passem al dispositiu colgados. Y podem visualitzar les dades com queramos, una gràfica dels temps parlats que al passar el ratón nos dirá a quién, o una tabla del tiempo total acumulado, així com uns formatgets on veurem amb qui passem més temps al telèfon.
Us deixo alguns exemples que tinc en aquesta imatge i aquí us enganxo les consultes que hi he fet, això sí, recordeu primerament de fer el connector des de Grafana a la vostra BD de MySQL, des de els “Data Sources”.
Total al telèfon:
SELECT sum(Durada) FROM ( SELECT SUM(durada) as Durada FROM trucades_realitzades WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter() union allSELECT SUM(durada) as Durada FROM trucades_rebudes WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter() ) a
Total trucada:
SELECT SUM(durada) FROM trucades_realitzades WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter()
Total Rebut:
SELECT SUM(durada) FROM trucades_rebudes WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter()
Total trucades rebudes:
SELECT count(*) FROM trucades_rebudes WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter()
Total trucades realitzades:
SELECT count(*) FROM trucades_realitzades WHERE STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( )
Gràfica de les trucades i la seva durada (la dels puntets)
Mètrica trucades realitzades:
SELECT durada as value, nom as metric, DATE_SUB(TIMESTAMP(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_realitzades ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDRE PER time_sec ASC;
Mètrica cridades rebudes:
SELECT durada as value, nom as metric, DATE_SUB(TIMESTAMP(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_rebudes ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDRE PER time_sec ASC;
Mètrica cridades realitzades:
SELECT 0 as value, nom as metric, DATE_SUB(marcador de temps(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_perdudes ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDRE PER time_sec ASC;
Gràfica de la durada de les cridades rebudes i realitzades
Mètrica cridades realitzades:
select (@sum := @sum + durada) as value, "Realitzades" as metric, DATE_SUB(marcador de temps(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_realitzades cross join (selecciona @sum := 0) paràmetres ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( );
Mètrica cridades rebudes:
select (@sum := @sum + durada) as value, "Rebudes" as metric, DATE_SUB(marcador de temps(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_rebudes cross join (selecciona @sum := 0 ) paràmetres ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( );
Gràfic de sectors de cridades rebudes:
SELECT durada as value, nom as metric, DATE_SUB(marcador de temps(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_rebudes ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDRE PER time_sec ASC;
Gràfic de sectors de cridades realitzades:
SELECT durada as value, nom as metric, DATE_SUB(marcador de temps(str_to_date(quan, '%M %d %Y at %l:%i%p')), INTERVAL 2 HORA) com a time_sec DES DE cridades_realitzades ON STR_TO_DATE(quan, '%M %d %Y at %l:%i%p') $__timeFilter( )ORDRE PER time_sec ASC;
Espero que us interessi i us pugui servir per a alguna cosa, no només d’aquesta manera de fer-ho, sinó per a altres idees i vulgueu explotar aquestes dades, d’aquí poc us posaré un altre exemple, en aquest cas dels correus electrònics, igual és interessant saber quants correus rebem o enviem… El que s'ha dit, com sempre, gràcies pel vostre temps!
