53 x served & 5 x viewed

J’ai fait un petit script pour envoyer les logs vers Grafana :

mysql -u root -e "CREATE DATABASE TEAMS;"
mysql -u root -e "USE TEAMS;DROP TABLE TEAMS_AUDIT;"
mysql -u root -e "USE TEAMS; CREATE TABLE TEAMS_AUDIT (DATE datetime, duration float, vdiMode int, eventpdclevel int);"
echo "Version 1.0";
mysql -u root -e "USE TEAMS; DELETE FROM TEAMS_AUDIT";

grep "desktop_foreground_duration" ~/.config/Microsoft/Microsoft\ Teams/logs.txt ~/.config/Microsoft/Microsoft\ Teams/old_logs_* | sed 's/:/ /g' | awk '{print $6 "-" $4 "-" $5 " " $7 ":" $8 ":" $9 " " $20 " " $24 " " $26}' | sed 's/,/ /g' | sed 's/Jun/06/g' | sed 's/May/05/g' > /tmp/audit_teams.txt

while read line
do
DATE=$(echo $line | awk '{print $1 " " $2}');
DURATION=$(echo $line | awk '{print $3}');
VDI=$(echo $line | awk '{print $4}');
LEVEL=$(echo $line | awk '{print $5}');
SQL="USE TEAMS; INSERT INTO TEAMS_AUDIT (DATE, duration, vdiMode, eventpdclevel) VALUES ('$DATE',$DURATION,$VDI,$LEVEL);"
#echo $SQL
mysql -u root -e "$SQL"
done < /tmp/audit_teams.txt

echo "Done"

Ensuite sur Grafana il suffit de faire :

– Pour avoir le temps passé (en seconde) par jour :

SELECT
UNIX_TIMESTAMP(date) as time_sec,
SUM(duration) as value
FROM TEAMS_AUDIT
WHERE
$__timeFilter(DATE)
GROUP BY day(date),month(date),year(date)
ORDER BY DATE

-Pour avoir le temps passé (en jours) par jour :

SELECT
  UNIX_TIMESTAMP(date) as time_sec,
  sum(duration)/60/60 as value
FROM TEAMS_AUDIT
WHERE
  $__timeFilter(DATE)
  GROUP BY  day(date),month(date),year(date)
ORDER BY DATE

– Pour avoir le nombre d’activation de la fenetre par jour:

SELECT
UNIX_TIMESTAMP(date) as time_sec,
count(duration) as value
FROM TEAMS_AUDIT
WHERE
$__timeFilter(DATE)
GROUP BY day(date),month(date),year(date)
ORDER BY DATE