WebSVN – ALCASAR – Diff – /scripts/alcasar-generate_log.sh


#!/bin/bash
#
# $Id: alcasar-generate_log.sh 2719 2019-04-05 11:45:35Z tom.houdayer $
#
#Corrélation et Generation des logs d'imputabilité au format PDF.
#Ce script permet de générer un fichier HTML qui sera converti en PDF a l'aide du RPM wkhtmltopdf.
#Ce PDF sera placé dans une archive protégé par un mot de passe.
#Pour extraire ce fichier PDF, il faudra installer le paquet p7zip.
#La génération de ce document préviendra les utilisateurs lors de leur prochaine connection. (utilisateur flagué dans le 4ème 'bit' de l'attribut FilterID de la BDD radius.
#
#Il est possible de demander les logs d'imputabilité :
#-depuis le début (pas d'argument)
#-à partir d'une date (un seul argument)
#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)
#Par Raphaël Pion
 
 
 
usage="Usage: alcasar-generate_log.sh PASSWORD && ({ '' } | { 'YYYY-MM-DD HH:MM:SS' } | { 'YYYY-MM-DD HH:MM:SS' 'YYYY-MM-DD HH:MM:SS' })"
nb_args=$#
DIR='/var/www/html/acc/backup/'
TMP_SQL="/tmp/log_sql.csv"
TMP_USERS="/tmp/log_users"
TMP_HTML="$DIR/log_nf.html"
TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"
PASSWD_FILE="/root/ALCASAR-passwords.txt"
DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE | cut -d'=' -f 2-)
ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"
 
 
if [ $nb_args -eq 1 ]
then
	QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"
	SECTION_LOG="Extraction de tous les journaux"
fi
 
if [ $nb_args -eq 2 ]
then
	QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"
	SECTION_LOG="Extraction des journaux à partir du $2"
fi
 
if [ $nb_args -eq 3 ]
then
	QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' AND acctstarttime <= '$3' ORDER BY acctstoptime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"
	SECTION_LOG="Extraction des journaux entre $2 et $3"
fi
 
if [ $nb_args -eq 0 ]
then
	echo $usage
	exit
fi
 
 
if [ $nb_args -gt 3 ]
then
	echo $usage
	exit
fi
 
if [ -e $TMP_SQL ]
then
	rm $TMP_SQL
fi
 
if [ -e $TMP_PDF ]
then
	rm $TMP_PDF
fi
 
if [ -e $ARCHIVE_LOCATION ]
then
	rm $ARCHIVE_LOCATION
fi
 
 
#get log information for each users
mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"
 
#Create HTML document which contains every informations about users
echo "<!DOCTYPE html>" > $TMP_HTML
echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML
echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML
echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML
echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML
echo "</HEAD>" >> $TMP_HTML
echo "<body>" >> $TMP_HTML
echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML
 
echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML
echo "<font size='1'>" >> $TMP_HTML
cat $TMP_SQL | while read LIGNE_SQL
do
	LOG_IP=$(echo $LIGNE_SQL | cut -d',' -f3)
	LOG_DATE1=$(echo $LIGNE_SQL | cut -d',' -f4)
	LOG_DATE2=$(echo $LIGNE_SQL | cut -d',' -f5)
 
	LOG_Y1=$(echo $LOG_DATE1 | cut -d'-' -f1)
	LOG_M1=$(echo $LOG_DATE1 | cut -d'-' -f2)
	LOG_D1=$(echo $LOG_DATE1 | cut -d'-' -f3 | cut -d' ' -f1)
	LOG_H1=$(echo $LOG_DATE1 | cut -d'-' -f3 | cut -d' ' -f2)
	
	LOG_Y2=$(echo $LOG_DATE2 | cut -d'-' -f1)
	LOG_M2=$(echo $LOG_DATE2 | cut -d'-' -f2)
	LOG_D2=$(echo $LOG_DATE2 | cut -d'-' -f3 | cut -d' ' -f1)
	LOG_H2=$(echo $LOG_DATE2 | cut -d'-' -f3 | cut -d' ' -f2)
	
	DUMP=$(nfdump -q -R /var/log/nfsen/profiles-data/live/alcasar_netflow/ -t $LOG_Y1/$LOG_M1/$LOG_D1.$LOG_H1-$LOG_Y2/$LOG_M2/$LOG_D2.$LOG_H2 -O tstart -o "fmt:<tr><td class='numberLine'></td><td>%sa</td><td>%sp</td><td>%da</td><td>%dp</td><td>%ts</td></tr>" "ip $LOG_IP")
	if [ ! -z "$DUMP" ]
	then
		echo "<div class='container'> " >> $TMP_HTML
		echo "<table class='table table-striped'>" >> $TMP_HTML
		echo "<thead>" >> $TMP_HTML
		echo "<tr>" >> $TMP_HTML
		echo "<th>Username</th>" >> $TMP_HTML
		echo "<th>Client @MAC</th>" >> $TMP_HTML
		echo "<th>Client @IP</th>" >> $TMP_HTML
		echo "<th>Login Time</th>" >> $TMP_HTML
		echo "<th>Logout Time</th>" >> $TMP_HTML
		echo "<th>Upload</th>" >> $TMP_HTML
		echo "<th>Download</th>" >> $TMP_HTML
		echo "<th>Cause</th>" >> $TMP_HTML
		echo "</tr></thead><tbody><tr>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f1) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f2) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f3) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f4) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f5) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f7) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f6) "</td>" >> $TMP_HTML
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f8) "</td>" >> $TMP_HTML
		echo "</tr></tbody></table></div>" >> $TMP_HTML
		echo "<div class='container mySpace'> " >> $TMP_HTML
		echo "<table class='table table-striped'>" >> $TMP_HTML
		echo "<thead>" >> $TMP_HTML
		echo "<tr>" >> $TMP_HTML
		echo "<th>N°</th>" >> $TMP_HTML
		echo "<th>@IP src</th>" >> $TMP_HTML
		echo "<th>Port src</th>" >> $TMP_HTML
		echo "<th>@IP dst</th>" >> $TMP_HTML
		echo "<th>Port dst</th>" >> $TMP_HTML
		echo "<th>Date</th>" >> $TMP_HTML
		echo "</tr></thead><tbody>" >> $TMP_HTML
		echo $DUMP >> $TMP_HTML
		echo "</tbody></table></div>" >> $TMP_HTML
	fi
done
echo "</font>" >> $TMP_HTML
echo "</body>" >> $TMP_HTML
echo "</HTML>" >> $TMP_HTML
 
# inform users about that by setting the Alcasar-Imputability-Warning attribute
QUERY="INSERT INTO radreply (username, attribute, value, op) SELECT ui.username, 'Alcasar-Imputability-Warning', '1' , '=' FROM userinfo ui LEFT JOIN radreply rr ON rr.username = ui.username AND rr.attribute = 'Alcasar-Imputability-Warning' WHERE rr.username IS NULL;"
mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"
 
/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF
 
 
/usr/bin/7za a -tzip -p"$1" -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF
chown apache:apache $ARCHIVE_LOCATION
 
 
rm $TMP_HTML
rm $TMP_SQL
rm $TMP_PDF
 

Subversion Repositories ALCASAR

(root)/scripts/alcasar-generate_log.sh @ 2682 – Rev 2532 → 2719

Rev 2532		Rev 2719
1	`#!/bin/bash`	1	`#!/bin/bash`
2	`#`	2	`#`
3	`# $Id: alcasar-generate_log.sh 2532 2018-04-30 03:55:35Z tom.houdayer $`	3	`# $Id: alcasar-generate_log.sh 2719 2019-04-05 11:45:35Z tom.houdayer $`
4	`#`	4	`#`
5	`#Corrélation et Generation des logs d'imputabilité au format PDF.`	5	`#Corrélation et Generation des logs d'imputabilité au format PDF.`
6	`#Ce script permet de générer un fichier HTML qui sera converti en PDF a l'aide du RPM wkhtmltopdf.`	6	`#Ce script permet de générer un fichier HTML qui sera converti en PDF a l'aide du RPM wkhtmltopdf.`
7	`#Ce PDF sera placé dans une archive protégé par un mot de passe.`	7	`#Ce PDF sera placé dans une archive protégé par un mot de passe.`
8	`#Pour extraire ce fichier PDF, il faudra installer le paquet p7zip.`	8	`#Pour extraire ce fichier PDF, il faudra installer le paquet p7zip.`
9	`#La génération de ce document préviendra les utilisateurs lors de leur prochaine connection. (utilisateur flagué dans le 4ème 'bit' de l'attribut FilterID de la BDD radius.`	9	`#La génération de ce document préviendra les utilisateurs lors de leur prochaine connection. (utilisateur flagué dans le 4ème 'bit' de l'attribut FilterID de la BDD radius.`
10	`#`	10	`#`
11	`#Il est possible de demander les logs d'imputabilité :`	11	`#Il est possible de demander les logs d'imputabilité :`
12	`#-depuis le début (pas d'argument)`	12	`#-depuis le début (pas d'argument)`
13	`#-à partir d'une date (un seul argument)`	13	`#-à partir d'une date (un seul argument)`
14	`#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)`	14	`#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)`
15	`#Par Raphaël Pion`	15	`#Par Raphaël Pion`
16		16
17		17
18		18
19	`usage="Usage: alcasar-generate_log.sh PASSWORD && ({ '' } \| { 'YYYY-MM-DD HH:MM:SS' } \| { 'YYYY-MM-DD HH:MM:SS' 'YYYY-MM-DD HH:MM:SS' })"`	19	`usage="Usage: alcasar-generate_log.sh PASSWORD && ({ '' } \| { 'YYYY-MM-DD HH:MM:SS' } \| { 'YYYY-MM-DD HH:MM:SS' 'YYYY-MM-DD HH:MM:SS' })"`
20	`nb_args=$#`	20	`nb_args=$#`
21	`DIR='/var/www/html/acc/backup/'`	21	`DIR='/var/www/html/acc/backup/'`
22	`TMP_SQL="/tmp/log_sql.csv"`	22	`TMP_SQL="/tmp/log_sql.csv"`
23	`TMP_USERS="/tmp/log_users"`	23	`TMP_USERS="/tmp/log_users"`
24	`TMP_HTML="$DIR/log_nf.html"`	24	`TMP_HTML="$DIR/log_nf.html"`
25	`TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"`	25	`TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"`
26	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`	26	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`
27	`DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE \| cut -d'=' -f 2-)`	27	`DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE \| cut -d'=' -f 2-)`
28	`ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"`	28	`ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"`
29		29
30		30
31	`if [ $nb_args -eq 1 ]`	31	`if [ $nb_args -eq 1 ]`
32	`then`	32	`then`
33	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`	33	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`
34	`SECTION_LOG="Extraction de tous les journaux"`	34	`SECTION_LOG="Extraction de tous les journaux"`
35	`fi`	35	`fi`
36		36
37	`if [ $nb_args -eq 2 ]`	37	`if [ $nb_args -eq 2 ]`
38	`then`	38	`then`
39	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`	39	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' ORDER BY acctstarttime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`
40	`SECTION_LOG="Extraction des journaux à partir du $2"`	40	`SECTION_LOG="Extraction des journaux à partir du $2"`
41	`fi`	41	`fi`
42		42
43	`if [ $nb_args -eq 3 ]`	43	`if [ $nb_args -eq 3 ]`
44	`then`	44	`then`
45	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' AND acctstarttime <= '$3' ORDER BY acctstoptime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`	45	`QUERY="SELECT username,callingstationid,framedipaddress,acctstarttime,acctstoptime,acctinputoctets,acctoutputoctets,acctterminatecause FROM radacct WHERE acctstarttime >= '$2' AND acctstarttime <= '$3' ORDER BY acctstoptime INTO OUTFILE '$TMP_SQL' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`
46	`SECTION_LOG="Extraction des journaux entre $2 et $3"`	46	`SECTION_LOG="Extraction des journaux entre $2 et $3"`
47	`fi`	47	`fi`
48		48
49	`if [ $nb_args -eq 0 ]`	49	`if [ $nb_args -eq 0 ]`
50	`then`	50	`then`
51	`echo $usage`	51	`echo $usage`
52	`exit`	52	`exit`
53	`fi`	53	`fi`
54		54
55		55
56	`if [ $nb_args -gt 3 ]`	56	`if [ $nb_args -gt 3 ]`
57	`then`	57	`then`
58	`echo $usage`	58	`echo $usage`
59	`exit`	59	`exit`
60	`fi`	60	`fi`
61		61
62	`if [ -e $TMP_SQL ]`	62	`if [ -e $TMP_SQL ]`
63	`then`	63	`then`
64	`rm $TMP_SQL`	64	`rm $TMP_SQL`
65	`fi`	65	`fi`
66		66
67	`if [ -e $TMP_PDF ]`	67	`if [ -e $TMP_PDF ]`
68	`then`	68	`then`
69	`rm $TMP_PDF`	69	`rm $TMP_PDF`
70	`fi`	70	`fi`
71		71
72	`if [ -e $ARCHIVE_LOCATION ]`	72	`if [ -e $ARCHIVE_LOCATION ]`
73	`then`	73	`then`
74	`rm $ARCHIVE_LOCATION`	74	`rm $ARCHIVE_LOCATION`
75	`fi`	75	`fi`
76		76
77		77
78	`#get log information for each users`	78	`#get log information for each users`
79	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`	79	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`
80		80
81	`#Create HTML document which contains every informations about users`	81	`#Create HTML document which contains every informations about users`
82	`echo "<!DOCTYPE html>" > $TMP_HTML`	82	`echo "<!DOCTYPE html>" > $TMP_HTML`
83	`echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML`	83	`echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML`
84	`echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML`	84	`echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML`
85	`echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML`	85	`echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML`
86	`echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML`	86	`echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML`
87	`echo "</HEAD>" >> $TMP_HTML`	87	`echo "</HEAD>" >> $TMP_HTML`
88	`echo "<body>" >> $TMP_HTML`	88	`echo "<body>" >> $TMP_HTML`
89	`echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML`	89	`echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML`
90		90
91	`echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML`	91	`echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML`
92	`echo "<font size='1'>" >> $TMP_HTML`	92	`echo "<font size='1'>" >> $TMP_HTML`
93	`cat $TMP_SQL \| while read LIGNE_SQL`	93	`cat $TMP_SQL \| while read LIGNE_SQL`
94	`do`	94	`do`
95	`LOG_IP=$(echo $LIGNE_SQL \| cut -d',' -f3)`	95	`LOG_IP=$(echo $LIGNE_SQL \| cut -d',' -f3)`
96	`LOG_DATE1=$(echo $LIGNE_SQL \| cut -d',' -f4)`	96	`LOG_DATE1=$(echo $LIGNE_SQL \| cut -d',' -f4)`
97	`LOG_DATE2=$(echo $LIGNE_SQL \| cut -d',' -f5)`	97	`LOG_DATE2=$(echo $LIGNE_SQL \| cut -d',' -f5)`
98		98
99	`LOG_Y1=$(echo $LOG_DATE1 \| cut -d'-' -f1)`	99	`LOG_Y1=$(echo $LOG_DATE1 \| cut -d'-' -f1)`
100	`LOG_M1=$(echo $LOG_DATE1 \| cut -d'-' -f2)`	100	`LOG_M1=$(echo $LOG_DATE1 \| cut -d'-' -f2)`
101	`LOG_D1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f1)`	101	`LOG_D1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f1)`
102	`LOG_H1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f2)`	102	`LOG_H1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f2)`
103		103
104	`LOG_Y2=$(echo $LOG_DATE2 \| cut -d'-' -f1)`	104	`LOG_Y2=$(echo $LOG_DATE2 \| cut -d'-' -f1)`
105	`LOG_M2=$(echo $LOG_DATE2 \| cut -d'-' -f2)`	105	`LOG_M2=$(echo $LOG_DATE2 \| cut -d'-' -f2)`
106	`LOG_D2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f1)`	106	`LOG_D2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f1)`
107	`LOG_H2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f2)`	107	`LOG_H2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f2)`
108		108
109	`DUMP=$(nfdump -O tstart -R /var/log/nfsen/profiles-data/live/alcasar_netflow/ -t $LOG_Y1/$LOG_M1/$LOG_D1.$LOG_H1-$LOG_Y2/$LOG_M2/$LOG_D2.$LOG_H2 -o "fmt:<tr><td class='numberLine'></td><td>%sa</td><td>%sp</td><td>%da</td><td>%dp</td><td>%ts</td></tr>" \| tail -n +2 \| head -n -4 \| grep "$LOG_IP")`	109	`DUMP=$(nfdump -q -R /var/log/nfsen/profiles-data/live/alcasar_netflow/ -t $LOG_Y1/$LOG_M1/$LOG_D1.$LOG_H1-$LOG_Y2/$LOG_M2/$LOG_D2.$LOG_H2 -O tstart -o "fmt:<tr><td class='numberLine'></td><td>%sa</td><td>%sp</td><td>%da</td><td>%dp</td><td>%ts</td></tr>" "ip $LOG_IP")`
110	`if [ ! -z "$DUMP" ]`	110	`if [ ! -z "$DUMP" ]`
111	`then`	111	`then`
112	`echo "<div class='container'> " >> $TMP_HTML`	112	`echo "<div class='container'> " >> $TMP_HTML`
113	`echo "<table class='table table-striped'>" >> $TMP_HTML`	113	`echo "<table class='table table-striped'>" >> $TMP_HTML`
114	`echo "<thead>" >> $TMP_HTML`	114	`echo "<thead>" >> $TMP_HTML`
115	`echo "<tr>" >> $TMP_HTML`	115	`echo "<tr>" >> $TMP_HTML`
116	`echo "<th>Username</th>" >> $TMP_HTML`	116	`echo "<th>Username</th>" >> $TMP_HTML`
117	`echo "<th>Client @MAC</th>" >> $TMP_HTML`	117	`echo "<th>Client @MAC</th>" >> $TMP_HTML`
118	`echo "<th>Client @IP</th>" >> $TMP_HTML`	118	`echo "<th>Client @IP</th>" >> $TMP_HTML`
119	`echo "<th>Login Time</th>" >> $TMP_HTML`	119	`echo "<th>Login Time</th>" >> $TMP_HTML`
120	`echo "<th>Logout Time</th>" >> $TMP_HTML`	120	`echo "<th>Logout Time</th>" >> $TMP_HTML`
121	`echo "<th>Upload</th>" >> $TMP_HTML`	121	`echo "<th>Upload</th>" >> $TMP_HTML`
122	`echo "<th>Download</th>" >> $TMP_HTML`	122	`echo "<th>Download</th>" >> $TMP_HTML`
123	`echo "<th>Cause</th>" >> $TMP_HTML`	123	`echo "<th>Cause</th>" >> $TMP_HTML`
124	`echo "</tr></thead><tbody><tr>" >> $TMP_HTML`	124	`echo "</tr></thead><tbody><tr>" >> $TMP_HTML`
125	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f1) "</td>" >> $TMP_HTML`	125	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f1) "</td>" >> $TMP_HTML`
126	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f2) "</td>" >> $TMP_HTML`	126	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f2) "</td>" >> $TMP_HTML`
127	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f3) "</td>" >> $TMP_HTML`	127	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f3) "</td>" >> $TMP_HTML`
128	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f4) "</td>" >> $TMP_HTML`	128	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f4) "</td>" >> $TMP_HTML`
129	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f5) "</td>" >> $TMP_HTML`	129	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f5) "</td>" >> $TMP_HTML`
130	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f7) "</td>" >> $TMP_HTML`	130	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f7) "</td>" >> $TMP_HTML`
131	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f6) "</td>" >> $TMP_HTML`	131	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f6) "</td>" >> $TMP_HTML`
132	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f8) "</td>" >> $TMP_HTML`	132	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f8) "</td>" >> $TMP_HTML`
133	`echo "</tr></tbody></table></div>" >> $TMP_HTML`	133	`echo "</tr></tbody></table></div>" >> $TMP_HTML`
134	`echo "<div class='container mySpace'> " >> $TMP_HTML`	134	`echo "<div class='container mySpace'> " >> $TMP_HTML`
135	`echo "<table class='table table-striped'>" >> $TMP_HTML`	135	`echo "<table class='table table-striped'>" >> $TMP_HTML`
136	`echo "<thead>" >> $TMP_HTML`	136	`echo "<thead>" >> $TMP_HTML`
137	`echo "<tr>" >> $TMP_HTML`	137	`echo "<tr>" >> $TMP_HTML`
138	`echo "<th>N°</th>" >> $TMP_HTML`	138	`echo "<th>N°</th>" >> $TMP_HTML`
139	`echo "<th>@IP src</th>" >> $TMP_HTML`	139	`echo "<th>@IP src</th>" >> $TMP_HTML`
140	`echo "<th>Port src</th>" >> $TMP_HTML`	140	`echo "<th>Port src</th>" >> $TMP_HTML`
141	`echo "<th>@IP dst</th>" >> $TMP_HTML`	141	`echo "<th>@IP dst</th>" >> $TMP_HTML`
142	`echo "<th>Port dst</th>" >> $TMP_HTML`	142	`echo "<th>Port dst</th>" >> $TMP_HTML`
143	`echo "<th>Date</th>" >> $TMP_HTML`	143	`echo "<th>Date</th>" >> $TMP_HTML`
144	`echo "</tr></thead><tbody>" >> $TMP_HTML`	144	`echo "</tr></thead><tbody>" >> $TMP_HTML`
145	`echo $DUMP >> $TMP_HTML`	145	`echo $DUMP >> $TMP_HTML`
146	`echo "</tbody></table></div>" >> $TMP_HTML`	146	`echo "</tbody></table></div>" >> $TMP_HTML`
147	`fi`	147	`fi`
148	`done`	148	`done`
149	`echo "</font>" >> $TMP_HTML`	149	`echo "</font>" >> $TMP_HTML`
150	`echo "</body>" >> $TMP_HTML`	150	`echo "</body>" >> $TMP_HTML`
151	`echo "</HTML>" >> $TMP_HTML`	151	`echo "</HTML>" >> $TMP_HTML`
152		152
153	`# inform users about that by setting the Alcasar-Imputability-Warning attribute`	153	`# inform users about that by setting the Alcasar-Imputability-Warning attribute`
154	`QUERY="INSERT INTO radreply (username, attribute, value, op) SELECT ui.username, 'Alcasar-Imputability-Warning', '1' , '=' FROM userinfo ui LEFT JOIN radreply rr ON rr.username = ui.username AND rr.attribute = 'Alcasar-Imputability-Warning' WHERE rr.username IS NULL;"`	154	`QUERY="INSERT INTO radreply (username, attribute, value, op) SELECT ui.username, 'Alcasar-Imputability-Warning', '1' , '=' FROM userinfo ui LEFT JOIN radreply rr ON rr.username = ui.username AND rr.attribute = 'Alcasar-Imputability-Warning' WHERE rr.username IS NULL;"`
155	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`	155	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`
156		156
157	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`	157	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`
158		158
159		159
160	`/usr/bin/7za a -tzip -p"$1" -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`	160	`/usr/bin/7za a -tzip -p"$1" -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`
161	`chown apache:apache $ARCHIVE_LOCATION`	161	`chown apache:apache $ARCHIVE_LOCATION`
162		162
163		163
164	`rm $TMP_HTML`	164	`rm $TMP_HTML`
165	`rm $TMP_SQL`	165	`rm $TMP_SQL`
166	`rm $TMP_PDF`	166	`rm $TMP_PDF`
167		167