Visualiser ce dont nous avons parlé au téléphone avec Grafana

Puits, dans cet article, nous verrons quelque chose comme toujours différent et que nous pouvons simplement ajouter à notre installation Grafana, Et ce n’est rien d’autre que d’exploiter les données d’utilisation de nos appels téléphoniques. Je l’utilise depuis longtemps et je voulais le partager au cas où cela vous intéresserait, J’étais curieux de savoir combien de temps je parlais sur mon téléphone portable, Avec qui, comment ils m’ont appelé et j’ai appelé, Voir par horaires, gens… au nougat!

Nous le ferons via une application installée sur le téléphone portable, comme le célèbre IFTTT, nous enregistrerons dans des feuilles de calcul Google Spreadsheet les journaux des appels manqués que nous avons, les appels que nous recevons et ceux que nous passons, dans les mêmes feuilles, le nom du contact sera enregistré, son numéro de téléphone et la durée de chaque appel, ainsi que la date et l'heure. Maintenant, avec la nouvelle version de Grafana (le 7) nous pouvons consulter directement les feuilles Google Spreadsheet, mais cette fois, je vais vous montrer une autre méthode, car je l'utilise depuis un certain temps et je n'ai pas encore développé cela 🙂 Ce que je fais jusqu'à présent, c'est, via une tâche programmée avec un cron sur une machine Linux (cela pourrait être le propre Grafana), puis je télécharge les feuilles de calcul puis je les transfère dans une base de données MySQL. et c'est cette base de données que nous visualiserons avec Grafana, pouvant concevoir notre tableau de bord selon nos préférences!

Commençons par ce qui a été dit, sur notre mobile nous installerons IFTTT, et nous ajouterons trois automatisations:

Enregistrer automatiquement les appels que vous recevez sur votre téléphone Android dans une feuille de calcul Google
Enregistrer automatiquement les appels que vous passez sur votre téléphone Android dans une feuille de calcul Google
Journal d'appels, Appel manqué

Nous configurerons chacune d'elles pour qu'elle enregistre dans une feuille de calcul différente tous ces journaux. Se rappeler sur le mobile d'indiquer que cette application peut continuer à s'exécuter en arrière-plan et éviter que la batterie du mobile impose des restrictions, así siempre estará ejecutándose y almacenando en tiempo real nuestros datos.

Puits, 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, (Je) 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ÈRE;Gt; llamadas_realizadas.csv1
sed 's/"//g' llamadas_realizadas.csv1 &AMPÈRE;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 &AMPÈRE;Gt; llamadas_recibidas.csv1
sed 's/"//g' llamadas_recibidas.csv1 &AMPÈRE;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 &AMPÈRE;Gt; llamadas_perdidas.csv1
sed 's/,,/,/g' llamadas_perdidas.csv1 &AMPÈRE;Gt; llamadas_perdidas.csv2
awk '{GSUB(/,/,"",$2)}1' llamadas_perdidas.csv2 &AMPÈRE;Gt; llamadas_perdidas.csv3
sed 's/"//g' llamadas_perdidas.csv3 &AMPÈRE;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

Comme nous pouvons le voir, por un lado necesitamos el ID de la Sheet de Google, esa parte es muy sencilla, Il suffira d'ouvrir la feuille avec un navigateur et dans l'URL vous aurez l'ID, exemple: “https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXXXXXXX/edit#gid=0”. Et la partie un peu plus laborieuse est d'obtenir le fichier avec les identifiants d'accès via l'API, qui est le fichier JSON. Comme c'est un processus long et que certains connaissent déjà, comme nous l'avons vu dans un autre post, je pense que le mieux est de vous laisser ici la documentation officielle de Google, où il nous expliquera qu'il faut créer un projet et donner accès via OAuth 2.0, Vérifier: https://developers.google.com/sheets/api/guides/authorizing. Une fois que nous aurons les IDs de chaque Sheet et le JSON avec les identifiants d'accès, vous pourrez tester le script ci-dessus, 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, et comme nous le voyons, almacena el cuando fue la llamada, le numéro de téléphone, 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' CHAR(50) NULL COLLATE 'utf8mb4_general_ci',
`duracion` FLOAT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;

Puits, 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, ce que nous avons appelé nous-mêmes, ce qu'on nous a appelé, le total des appels reçus ou passés. Attention, tout cela sera filtré selon la période de dates que nous sélectionnons, ainsi nous pourrons voir par exemple les données de la semaine dernière, du mois… et vous allez halluciner du temps que nous passons au téléphone. Et nous pouvons visualiser les données comme nous voulons, un graphique des temps de parole qui, en passant la souris, nous dira à qui, ou un tableau du temps total cumulé, ainsi que quelques secteurs où nous verrons avec qui nous passons le plus de temps au téléphone.

Je vous laisse quelques exemples que j'ai dans cette image et ici je vous colle les requêtes que j'ai faites, Bien sûr, rappelez-vous d'abord de créer le connecteur de Grafana vers votre base de données MySQL, depuis les “Sources des données”.

Total al teléfono:

SELECT sum(Duración)
DE (
SÉLECTIONNEZ SOMME(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()
) À

Total llamado:

SÉLECTIONNEZ SOMME(duracion) FROM llamadas_realizadas WHERE STR_TO_DATE(cuando, '%M %d %Y at %l:%i%p') $__timeFilter()

Total Recibido:

SÉLECTIONNEZ SOMME(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')), INTERVALLE 2 HEURE) 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')), INTERVALLE 2 HEURE) 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:

CHOISIR 0 comme valeur, nombre as metric, DATE_SUB(timestamp(str_to_date(cuando, '%M %d %Y at %l:%i%p')), INTERVALLE 2 HEURE) as time_sec FROM llamadas_perdidas WHERE STR_TO_DATE(cuando, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Graphique de la durée des appels reçus et passés

Métrique des appels passés:

Choisir (@sum := @sum + duracion) comme valeur, "Passés" en tant que métrique, DATE_SUB(timestamp(str_to_date(cuando, '%M %d %Y at %l:%i%p')), INTERVALLE 2 HEURE) comme time_sec à partir de appels_passés cross join (select @sum := 0) params WHERE STR_TO_DATE(cuando, '%M %d %Y at %l:%i%p') $__timeFilter( );

Métrica llamadas recibidas:

Choisir (@sum := @sum + duracion) comme valeur, "Reçus" en tant que métrique, DATE_SUB(timestamp(str_to_date(cuando, '%M %d %Y at %l:%i%p')), INTERVALLE 2 HEURE) comme time_sec à partir de appels_reçus cross join (select @sum := 0 ) params WHERE STR_TO_DATE(cuando, '%M %d %Y at %l:%i%p') $__timeFilter( );

Question sur les appels reçus:

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

Question sur les appels passés:

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

J'espère que cela vous intéressera et pourra vous être utile, non seulement de cette façon de le réaliser, mais aussi pour d'autres idées et si vous voulez exploiter ces données, bientôt je vous mettrai un autre exemple, dans ce cas des courriels, il est également intéressant de savoir combien de mails nous recevons ou envoyons… Ce que j’ai dit, comme d'habitude, Merci de votre temps!