Grafana-rekin telefonoan hitz egiten duguna ikustea
Onena, 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… gaia aldatuta!
Lo haremos mediante una app instalada en el móvil, como es la mítica IFTTT guardaremos en hojas de calcul 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 (la 7) podemos consultar hojas de Google Spreadsheet zuzenean, baina une honetan os mostraré otra manera, ya que llevo tiempo usándolo y esto todavía no lo tengo garatu 🙂 Lo que hasta ahora hago es, mediante una tarea programada con un cron en una máquina linux (podría ser el propio Grafana), ondoren kalkulu-orriak deskargatzen ditut eta gero MySQL datu-base batean sartzen ditut. Eta BD hori izango da Grafana-rekin ikusiko duguna, Gure gustura gure Dashboarda diseinatu ahal izateko!
Aurrekoarekin hasten gara, Gure mugikorrean IFTTT instalatuko dugu, eta hiru automatizazio gehituko ditugu:
- Automatikoki zure Android telefonoan jasotzen dituzun deiak Google Spreadsheet batean erregistratu
- Automatikoki zure Android telefonoan egiten dituzun deiak Google Spreadsheet batean erregistratu
- Deien erregistroa, Dei galdua
Bakoitza horrelako moduan konfiguratu, kalkulu-orri desberdinetan gorde ditzan erregistro hauek guztiak. Mugikorrean gogoratu aplikazio honek atzeko planoan exekutatzen jarrai dezakeela adierazi eta mugikorraren bateria honek murrizketak ez ditzan egitea, horrela beti exekutatuko da eta gure datuak denbora errealean gordeko ditu.
Onena, orain arte erabili ditudan script-ekin hasiko gara, lehenik Python script hau beharko dugu, Google Drive-tik fitxategiak deskargatzeko aukera ematen diguna (DescargaFicheroDesdeGoogleDrive.py) eta gero nahikoa da bash script bat sortzea, egiten duena da, (i) MySQL taulak hustea, deiak gordeko ditugunak, (ii) Spreadsheet bakoitza deskargatzen du eta tokian tokiko CSV-an gordetzen du, eta ikusten duzu bezala, karaktere gehigarriak aldatzen edo ezabatzen ditu:
BajaFicheroGoogleDriveYmeteEnCSV.sh
#!/bin/bash
mysql -h MYSQL_SERVIDOREA -uERABILTZAILEA -pPASAHITZA DB_IZENA -e "TRUNCATE TABLE llamadas_realizadas"
mysql -h MYSQL_SERVIDOREA -uERABILTZAILEA -pPASAHITZA DB_IZENA -e "TRUNCATE TABLE llamadas_recibidas"
mysql -h MYSQL_SERVIDOREA -uERABILTZAILEA -pPASAHITZA DB_IZENA -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 "KARGATU DATUAK TOKIKO FITXATEGIAN 'llamadas_realizadas.csv' TAULAKO llamadas_realizadas EREMUETAN ','"
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 "KARGATU DATUAK TOKIKO FITXATEGIAN 'llamadas_recibidas.csv' TAULAN llamadas_recibidas EREMUETAN ','"
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 ','"
Nola ikusten duzu Python skriptak ini fitxategi bat eskatzen digula deskargatu nahi dugun SpreadSheet bakoitzeko, bat egindako deiak egiteko, beste bat jasotakoak egiteko eta beste bat galduak egiteko. Hau izango litzateke fitxategi bakoitzaren formatua, adibidea: “llamadas_realizadas.ini”
[fileDownloadFromDrive] clientsecretkeypath = /PATH/api_gugel.json spreadsheetid = filename = llamadas_realizadas.csv filepath = /PATH/ mimetype = text/csv
Ikusten dugun bezala, beste alde batetik Google Sheet-aren ID-a behar dugu, zati hori oso erraza da, orriari nabigatzaile batekin irekitzea nahikoa da eta URL-ean ID-a agertuko zaizue, ejemplo: “https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXXXXXXX/edit#gid=0”. Eta pixka bat neketsuagoa den zatia da API bidez sarbide kredentzialak dituen fitxategia lortzea, JSON fitxategia da. Prozesu luzea denez eta norbaitek jada ezagutzen duenez, Hau gehiago, beste post batean ikusi dugula uste dut, Uste dut onena Google-ren dokumentazio ofiziala uztea dela hemen, Non azalduko digun proiektu bat sortu behar dugula eta OAuth bidez sarbidea eman behar dugula 2.0, echa un vistazo a: https://developers.google.com/sheets/api/guides/authorizing. Sheet bakoitzaren ID-ak eta sarbide kredentzialak dituen JSON fitxategia lortzen ditugunean, goiko script-a probatu ahal izango duzu, una vez validado es question de meterlo en un cron y que se execute con la perioricidad que nos interese.
esto como veréis se almacena en 3 tablas distintas dentro de un MySQL, os dejo el code para crear una tabla de ejemplo, son las 3 tablas berdines, excepto que la de llamadas_perdidas no tiene columna de Duración, eta ikusiko dugun bezala, almacena el when fue la llamada, el numero 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;
Onena, pues con todo esto listo, ya podríamos en Grafana gure dibujitoak gure gana moduan, cómo vemos, tenemos unos campos de testu non vemos el total al telefono, lo que hemos llamado nosotros, el que nos han llamado, el total de appels jasotzen ditu las realizadas. ojo que todo esto se filtrará con el period de fecha que seleccionemos, beraz, adibidez ikusi ahal izango ditugu azken asteko datuak, del mes… eta joango da denbora iluziatzen duena aparatuak bilgados. Y podemos visualizar los datos como queramos, une gráfica de los tiempos hablados que al pasatu el ratón nos dirá a quién, o una tabla del tiempo total accumulado, así como unos quesitos non veremos norekin pasamos más al telefono.
Os dejo zenbait adibide que tengo en esta irudi eta hemen pego las galderas que les tengo hechas, eso sí, acordaros primeramente de hacer el conector desde Grafana a vuestra BD de MySQL, desde los “Data Sources”.
Total al teléfono:
SELECT sum(Duracion) FROM ( SELECT SUM(duracion) as Duracion FROM llamadas_realizadas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter() union all SELECT SUM(duracion) as Duracion FROM llamadas_recibidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter() ) a
Total llamado:
SELECT SUM(duracion) FROM llamadas_realizadas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter()
Total Recibido:
SELECT SUM(duracion) FROM llamadas_recibidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter()
Total llamadas recibidas:
SELECT count(*) FROM llamadas_recibidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter()
Total llamadas realizadas:
SELECT count(*) FROM llamadas_realizadas WHERE STR_TO_DATE(noiz, '%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(noiz, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(noiz, '%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(noiz, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Métrica llamadas realizadas:
SELECT 0 as value, nombre as metric, DATE_SUB(timestamp(str_to_date(noiz, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_perdidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Jasotako eta egindako deiak irauten duten denboraren grafikoa
Egindako deiak metrika:
select (@sum := @sum + duracion) as value, "Egindakoak" metrika gisa, DATE_SUB(timestamp(str_to_date(noiz, '%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(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( );
Métrica llamadas recibidas:
select (@sum := @sum + duracion) as value, "Jasotakoak" metrika gisa, DATE_SUB(timestamp(str_to_date(noiz, '%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(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( );
Jasotako deien quesitoa:
SELECT duracion as value, nombre as metric, DATE_SUB(timestamp(str_to_date(noiz, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Egindako deien quesitoa:
SELECT duracion as value, nombre as metric, DATE_SUB(timestamp(str_to_date(noiz, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(noiz, '%M %d %Y at %l:%i%p') $__timeFilter( )ORDER BY time_sec ASC;
Espero dut interesgarria izan eta zerbaitetan erabilgarria izan daitekeela, ez bakarrik modu honetako egiteko modua, baina beste ideientzat eta datu horiek ustiatu nahi badituzue, laster beste adibide bat jarriko dizuet, kasu honetan posta elektronikoen kasuan, eta antzekoa da zenbat mezu jasotzen ditugun edo bidaltzen ditugun jakitea… Esan dut, como siempre, gracias por vuestro tiempo!
