102 x served & 15 x viewed
Pour utiliser le script il faut:
- MariaDB
- Python
- Grafana.
Slack permet le téléchargement d’un fichier CSV ( access_logs.csv ), dont les données sont les suivantes :
- Date Accessed,
- User Agent – Simple,
- User Agent – Full,
- IP Address,
- Number of Logins,
- Last Date Accessed
Petit rappel sur l’ajout d’une database et d’un utilisateur :
$ sudo mysql -u root MariaDB [(none)]> create database SLACK; MariaDB [(none)]> CREATE USER 'slack'@'localhost' IDENTIFIED BY 'slack'; MariaDB [(none)]> GRANT ALL PRIVILEGES ON SLACK.* TO 'slack'@'localhost'; MariaDB [(none)]> FLUSH PRIVILEGES; MariaDB [(none)]> \quit Bye
Petit rappel aussi en python pour télécharger une classe non disponible :
$ sudo pip install python-dateutil
Le source du programme : ( Les sources sont disponibles ici : https://github.com/farias06/Grafana/blob/master/Slack_CSV_insert.py )
#! /usr/bin/env python3
# ~*~ utf-8 ~*~
import csv
from datetime import datetime
from dateutil.parser import parse
import mysql.connector
from mysql.connector import errorcode
from mysql.connector import (connection)
cnx = connection.MySQLConnection(user='slack', password='slack',
host='127.0.0.1',
database='SLACK')
cursor = cnx.cursor();
now = datetime.now().date();
#cursor.execute("DROP TABLE SLACK;");
#cursor.execute("CREATE TABLE SLACK (DATE datetime, DATE_LAST datetime, USER_AGENT varchar(50),USER_AGENT_FULL varchar(256), IP varchar(26), NUMBER int);");
cursor.execute("DELETE FROM SLACK");
cnx.commit();
with open('access_logs.csv', 'r') as csvfile:
reader = csv.reader(csvfile, quotechar='"')
for row in reader:
MyDate=row[0];
MyDate = MyDate.rsplit('(',1)[0];
if (MyDate == "Date Accessed"):
print("No");
else:
Dt = parse(MyDate)
MyUser=row[1];
MyUser=MyUser.replace("'", " ")
MyUserFull=row[2];
MyUserFull=MyUserFull.replace("'", " ")
MyIP=row[3];
MyNumber=row[4];
MyDateLast=row[5];
MyDateLast = MyDateLast.rsplit('(',1)[0];
DtLast = parse(MyDateLast)
try :
SQLREQUEST = "INSERT INTO SLACK (DATE, USER_AGENT, USER_AGENT_FULL, IP, DATE_LAST, NUMBER) VALUES ('"+str(Dt.date())+" "+str(Dt.time())+"', '"+MyUser+"', '"+MyUserFull+"','"+MyIP+"', '"+str(DtLast.date())+" "+str(DtLast.time())+"', "+MyNumber+" );";
cursor.execute(SQLREQUEST);
except mysql.connector.Error as err:
print("Something went wrong: {}".format(err))
if err.errno == errorcode.ER_BAD_TABLE_ERROR:
print("Creating table SLACK")
else:
None
cnx.commit();
cursor.close();
cnx.close();
# END
Pour lancer le programme :
$ python Slack_CSV_insert.py
Ensuite pour voir les données il y a plusieurs requetes possibles pour le metric :
Par IP :
SELECT
UNIX_TIMESTAMP(date) as time_sec,
SUM(number) as value,
ip as metric
FROM SLACK
WHERE $__timeFilter(date)
GROUP BY day(date),month(date),year(date)
ORDER BY date ASC
Par User Agent :
SELECT
UNIX_TIMESTAMP(date) as time_sec,
SUM(number) as value,
user_agent as metric
FROM SLACK
WHERE $__timeFilter(date)
GROUP BY day(date),month(date),year(date)
ORDER BY date ASC
Par User Agent Full :
SELECT
UNIX_TIMESTAMP(date) as time_sec,
SUM(number) as value,
user_agent_full as metric
FROM SLACK
WHERE $__timeFilter(date)
GROUP BY day(date),month(date),year(date)
ORDER BY date ASC
J’ai noté un bug, j’utilise la version Desktop sous Linux et je n’ai pas de « Application de Bureau Linux » .
