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

Brunnen, In diesem Beitrag werden wir etwas sehen, das immer anders ist und das wir einfach zu unserer Grafana-Installation hinzufügen können, Und es ist nichts anderes, als die Nutzungsdaten unserer Telefonate auszubeuten. Ich benutze es schon lange und wollte es teilen, falls Sie interessiert sind, Ich war neugierig, wie viel ich mit meinem Handy sprach, Mit wem, wie sie mich nannten und ich, Sehen Sie es nach Zeitplänen, Volk… 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), Also lade ich die Tabellen herunter und übertrage sie dann in eine MySQL-Datenbank. Und es wird diese Datenbank sein, die wir mit Grafana visualisieren, Dabei können wir unser Dashboard nach unseren Wünschen gestalten!

Wir beginnen mit dem Gesagten, Auf unserem Handy werden wir IFTTT installieren, und füge drei Automatisierungen hinzu:

Logge automatisch die Anrufe, die du auf deinem Android-Telefon erhältst, in eine Google-Tabelle
Logge automatisch die Anrufe, die du auf deinem Android-Telefon tätigst, in eine Google-Tabelle
Anrufliste, Verpasster Anruf

Wir konfigurieren jede von ihnen so, dass alle diese Einträge in einer separaten Tabelle gespeichert werden. Denke daran, auf dem Handy anzugeben, dass diese App weiterhin im Hintergrund ausgeführt werden kann und zu verhindern, dass die Handy-Batterie ihr Einschränkungen auferlegt, so wird es immer ausgeführt werden und unsere Daten in Echtzeit speichern.

Brunnen, wir starten mit den Skripten, die ich bisher verwendet habe, zuerst benötigen wir dieses Python-Skript, das uns erlaubt, Dateien von Google Drive herunterzuladen (DescargaFicheroDesdeGoogleDrive.py) und dann reicht es, ein Skript direkt mit Bash zu erstellen, das folgendes macht, (Ich) leert die MySQL-Tabellen, in denen wir die Anrufprotokolle speichern, (Ii) jede Tabelle wird als SpreadSheet heruntergeladen und lokal als CSV gespeichert, und wie ihr seht, werden überflüssige Zeichen umgewandelt oder gelöscht:

BajaFicheroGoogleDriveYmeteEnCSV.sh

#!/bin/bash
mysql -h MYSQL_SERVER -uUSER -pPASSWORD DB_NAME -e "TRUNCATE TABLE llamadas_realizadas"
mysql -h MYSQL_SERVER -uUSER -pPASSWORD DB_NAME -e "TRUNCATE TABLE llamadas_recibidas"
mysql -h MYSQL_SERVER -uUSER -pPASSWORD DB_NAME -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, Sobald es validiert ist, ist es eine Frage, es in einen Cron-Job einzufügen und es mit der gewünschten Häufigkeit auszuführen.

Wie ihr sehen werdet, wird dies gespeichert in 3 verschiedenen Tabellen innerhalb eines MySQL, ich lasse euch den Code, um eine Beispiel-Tabelle zu erstellen, sie sind die 3 gleichen Tabellen, außer dass die Tabelle `llamadas_perdidas` keine Spalte für die Dauer hat, und wie wir sehen, sie speichert, wann der Anruf war, die Telefonnummer, den Namen, wie wir ihn im Telefonbuch unseres Handys haben, und die Dauer des Anrufs:

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, Nun, da alles bereit ist, könnten wir in Grafana unsere Diagramme machen, wie es uns gefällt, wie wir sehen, Wir haben einige Textfelder, in denen wir die Gesamtanzahl der Telefonate sehen, was wir genannt haben, was man uns genannt hat, die Gesamtzahl der erhaltenen oder getätigten Anrufe. Achtung, das alles wird nach dem von uns gewählten Datumsbereich gefiltert, so können wir zum Beispiel die Daten der letzten Woche sehen, des Monats… und ihr werdet staunen, wie viel Zeit wir am Apparat hängen. Und wir können die Daten so visualisieren, wie wir wollen, ein Diagramm der Gesprächszeiten, das uns beim Überfahren mit der Maus sagt, mit wem, oder eine Tabelle der insgesamt kumulierten Zeit, sowie ein Kuchendiagramm, in dem wir sehen, mit wem wir am meisten telefonieren.

Ich lasse euch einige Beispiele, die ich auf diesem Bild habe, und hier füge ich die Queries ein, die ich dafür erstellt habe, Natürlich, Erinnert euch zuerst daran, den Connector von Grafana zu eurer MySQL-Datenbank herzustellen, von den “Datenquellen”.

Gesamt am Telefon:

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

Gesamt angerufen:

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

Gesamt empfangen:

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

Gesamt empfangene Anrufe:

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

Gesamt getätigte Anrufe:

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

Diagramm der Anrufe und deren Dauer (die mit den Punkten)
Metrik getätigte Anrufe:

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

Metrik empfangene Anrufe:

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

Metrik getätigte Anrufe:

AUSWÄHLEN 0 als Wert, nombre als Metrik, DATE_SUB(Zeitstempel(str_to_date(Wann, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) als time_sec VON llamadas_perdidas WO STR_TO_DATE(Wann, '%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 + Dauer) als Wert, "Realizadas" als Metrik, DATE_SUB(Zeitstempel(str_to_date(Wann, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) als time_sec VON llamadas_realizadas Kreuz verbindet (wähle @sum := 0) params WO STR_TO_DATE(Wann, '%M %d %Y at %l:%i%p') $__timeFilter( );

Metrik empfangene Anrufe:

Auswählen (@sum := @sum + Dauer) als Wert, "Empfangen" als Metrik, DATE_SUB(Zeitstempel(str_to_date(Wann, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) als time_sec VON llamadas_recibidas Kreuz verbindet (wähle @sum := 0 ) params WO STR_TO_DATE(Wann, '%M %d %Y at %l:%i%p') $__timeFilter( );

Quesito de llamadas recibidas:

SELECT duracion als Wert, nombre als Metrik, DATE_SUB(Zeitstempel(str_to_date(Wann, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) als time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(Wann, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;

Quesito de llamadas realizadas:

SELECT duracion als Wert, nombre als Metrik, DATE_SUB(Zeitstempel(str_to_date(Wann, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) als time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(Wann, '%M %d %Y at %l:%i%p') $__timeFilter( )ORDER BY time_sec ASC;

Espero que os interese y os pueda servir para algo, no solo 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, danke für eure Zeit!