Visualizing what we talked about on the phone with Grafana
Well, in this post we will see something as always different and that we can simply add to our Grafana installation, and it is nothing more than exploiting the usage data of our phone calls. I've been using it for a long time and I wanted to share it in case you're interested, I was curious how much I talked on my mobile phone, with whom, what they called me and I called, See it by schedules, people… with nougat!
We will do it through an app installed on the mobile, as is the mythical IFTTT we will save in Google Spreadsheet spreadsheets records of the missed calls we have, the calls we receive and those we make, the contact's name will be stored on the same sheets, your phone number and the duration of each call, plus when. Now with the new version of Grafana (the 7) we can consult Google Spreadsheet sheets directly, But this time I'll show you another way, since I've been using it for a long time and I haven't developed 🙂 this yet. What I do so far is, Using a Scheduled Task with a CRON on a Linux Machine (it could be Grafana himself), I download the spreadsheets and then dump them into a MySQL database. And it will be that DB that we visualize with Grafana, being able to design our Dashboard to our liking!
Let's start with what has been said, on our mobile we will install IFTTT, and we'll add three automations:
- 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
We will configure each of them so that it saves all these records in a different spreadsheet. Remember on your mobile phone to indicate that this app can continue to run in the background and prevent the mobile battery from restricting it, so it will always be running and storing our data in real time.
Well, We start with the scripts I've been using so far, first we will need this Python script that allows us to download files from Google Drive (DescargaFicheroDesdeGoogleDrive.py) And then it is enough to create a script itself with bash that what it does is, (i) flushes the MySQL tables where we will store the call logs, (Ii) each SpreadSheet is downloaded and stored locally in CSV, and as you can see it transforms them or eliminates characters that are left over:
BajaFicheroGoogleDriveYmeteEnCSV.sh
#!/bin/bash mysql -h SERVIDOR_MYSQL -uUSERNAME -pPASSWORD NOMBRE_BD -e "TRUNCATE TABLE llamadas_realizadas" mysql -h SERVIDOR_MYSQL -uUSERNAME -pPASSWORD NOMBRE_BD -e "TRUNCATE TABLE llamadas_recibidas" mysql -h SERVIDOR_MYSQL -uUSERNAME -pPASSWORD 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 -uUSERNAME -pPASSWORD 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 ≫ llamadas_recibidas.csv1 sed 's/"//g' llamadas_recibidas.csv1 ≫ llamadas_recibidas.csv mysql -h SERVIDOR_MYSQL -uUSERNAME -pPASSWORD 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 -uUSERNAME -pPASSWORD NOMBRE_BD "LOAD DATA LOCAL INFILE 'llamadas_perdidas.csv' INTO TABLE llamadas_perdidas FIELDS TERMINATED BY ','"
As you can see, the Python script asks us for an ini file for each SpreadSheet we want to download, One for calls made, another for those received and another for those lost. This would be the format of each file, Example of “llamadas_realizadas.ini”
[fileDownloadFromDrive] clientsecretkeypath = /PATH/api_gugel.json spreadsheetid = filename = llamadas_realizadas.csv filepath = /PATH/ mimetype = text/csv
As we can see, on the one hand we need the Google Sheet ID, That part is very simple, all you have to do is open the sheet with a browser and the URL will show you the ID, example: “https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXX_XXXXXXXXXXXXXXXXX/edit#gid=0”. And the slightly more laborious part is getting the file with the login credentials through the API, What is the JSON file. As it is a long process and some of you will already know, What's more, we've seen it in some other post, I think it is best to leave you in this part the official Google documentation, where he will explain that we have to create a project and give access through OAuth 2.0, Check out: https://developers.google.com/sheets/api/guides/authorizing. Once we have the IDs of each Sheet and the JSON with the login credentials you can try the script above, Once validated, it is a matter of putting it in a cron and running it with the periodicity that interests us.
This, as you will see, is stored in 3 different tables within a MySQL, I leave you the code to create an example table, are the 3 Equal Tables, except that the llamadas_perdidas has no Duration column, and as we see, stores the when the call was, the phone number, the name of how we have it in our mobile phone address book and the duration of the call:
CREATE TABLE 'llamadas_realizadas' ( 'when' to CHAT(50) NULL COLLATE 'utf8mb4_general_ci', 'phone' CHAR(50) NULL COLLATE 'utf8mb4_general_ci', 'name' CHAR(50) NULL COLLATE 'utf8mb4_general_ci', 'duration' FLOAT NULL ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
Well, Well, with all this ready, we could already in Grafana make our drawings as we please, How we see, we have text fields where we see the total on the phone, What We Have Called, What they have called us, the total number of calls received or made. Be careful that all this will be filtered with the date period we select, so we can see, for example, the data of the last week, of the month… and you will be amazed by the time we spend hanging on the device. And we can visualize the data however we want, a graph of the times spoken that when you hover the mouse will tell you to whom, or a table of the total accumulated time, as well as some cheeses where we will see who we spend the most time on the phone with.
I leave you some examples that I have in this image and here I paste the wishes that I have made, yes indeed, first remember to make the connector from Grafana to your MySQL database, from the “Data Sources”.
Total on the phone:
SELECT sum(Duracion) FROM ( SELECT SUM(Duration) as Duration FROM llamadas_realizadas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter() union all SELECT SUM(Duration) as Duration FROM llamadas_recibidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter() ) a
Total call:
SELECT SUM(Duration) FROM llamadas_realizadas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter()
Total Received:
SELECT SUM(Duration) FROM llamadas_recibidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter()
Total calls received:
SELECT count(*) FROM llamadas_recibidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter()
Total calls made:
SELECT count(*) FROM llamadas_realizadas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( )
Graph of calls and their duration (the one with the dots)
Metric calls made:
SELECT duracion as value, Name as metric, DATE_SUB(TIMESTAMP(str_to_date(when, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Calls received metric:
SELECT duracion as value, Name as metric, DATE_SUB(TIMESTAMP(str_to_date(when, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Metric Calls made:
SELECT 0 as value, Name as metric, DATE_SUB(timestamp(str_to_date(when, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_perdidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Graph of the duration of incoming and outgoing calls
Metric of calls made:
Select (@sum := @sum + Duration) as value, "Made" as metric, DATE_SUB(timestamp(str_to_date(when, '%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(when, '%M %d %Y at %l:%i%p') $__timeFilter( );
Calls received metric:
Select (@sum := @sum + Duration) as value, "Received" as metric, DATE_SUB(timestamp(str_to_date(when, '%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(when, '%M %d %Y at %l:%i%p') $__timeFilter( );
Quesito de Llamadas Received:
SELECT duracion as value, Name as metric, DATE_SUB(timestamp(str_to_date(when, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_recibidas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( ) ORDER BY time_sec ASC;
Quesito de llamadas hecho:
SELECT duracion as value, Name as metric, DATE_SUB(timestamp(str_to_date(when, '%M %d %Y at %l:%i%p')), INTERVAL 2 HOUR) as time_sec FROM llamadas_realizadas WHERE STR_TO_DATE(when, '%M %d %Y at %l:%i%p') $__timeFilter( )ORDER BY time_sec ASC;
I hope you are interested in it and it can be useful for you, not only this way of doing it, if not for other ideas and you want to exploit that data, I'll give you another example soon, in this case of emails, It is also interesting to know how many emails we receive or send… What I said, as usual, Thank you for your time!
