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


#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.
#
#Attribut FilterID dans la table radreply: 12345678
#1-> profile1
#2-> profile2
#3-> profile3
#4-> warn_user (if imputability report has been generated)
#6-> WL
#7-> BL
#8-> HAVP
#
#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';"
	echo $QUERY
	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 -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")
	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
 

Rev 2454	Rev 2501
1	`#Corrélation et Generation des logs d'imputabilité au format PDF.`	1	`#Corrélation et Generation des logs d'imputabilité au format PDF.`
2	`#Ce script permet de générer un fichier HTML qui sera converti en PDF a l'aide du RPM wkhtmltopdf.`	2	`#Ce script permet de générer un fichier HTML qui sera converti en PDF a l'aide du RPM wkhtmltopdf.`
3	`#Ce PDF sera placé dans une archive protégé par un mot de passe.`	3	`#Ce PDF sera placé dans une archive protégé par un mot de passe.`
4	`#Pour extraire ce fichier PDF, il faudra installer le paquet p7zip.`	4	`#Pour extraire ce fichier PDF, il faudra installer le paquet p7zip.`
5	`#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.`	5	`#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.`
6	`#`	6	`#`
7	`#Attribut FilterID dans la table radreply: 12345678`	7	`#Attribut FilterID dans la table radreply: 12345678`
8	`#1-> profile1`	8	`#1-> profile1`
9	`#2-> profile2`	9	`#2-> profile2`
10	`#3-> profile3`	10	`#3-> profile3`
11	`#4-> warn_user (if imputability report has been generated)`	11	`#4-> warn_user (if imputability report has been generated)`
12	`#6-> WL`	12	`#6-> WL`
13	`#7-> BL`	13	`#7-> BL`
14	`#8-> HAVP`	14	`#8-> HAVP`
15	`#`	15	`#`
16	`#Il est possible de demander les logs d'imputabilité :`	16	`#Il est possible de demander les logs d'imputabilité :`
17	`#-depuis le début (pas d'argument)`	17	`#-depuis le début (pas d'argument)`
18	`#-à partir d'une date (un seul argument)`	18	`#-à partir d'une date (un seul argument)`
19	`#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)`	19	`#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)`
20	`#Par Raphaël Pion`	20	`#Par Raphaël Pion`
21		21
22		22
23		23
24	`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' })"`	24	`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' })"`
25	`nb_args=$#`	25	`nb_args=$#`
26	`DIR='/var/www/html/acc/backup/'`	26	`DIR='/var/www/html/acc/backup/'`
27	`TMP_SQL="/tmp/log_sql.csv"`	27	`TMP_SQL="/tmp/log_sql.csv"`
28	`TMP_USERS="/tmp/log_users"`	28	`TMP_USERS="/tmp/log_users"`
29	`TMP_HTML="$DIR/log_nf.html"`	29	`TMP_HTML="$DIR/log_nf.html"`
30	`TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"`	30	`TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"`
31	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`	31	`PASSWD_FILE="/root/ALCASAR-passwords.txt"`
32	`DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE \| cut -d'=' -f 2-)`	32	`DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE \| cut -d'=' -f 2-)`
33	`ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"`	33	`ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"`
34		34
35		35
36	`if [ $nb_args -eq 1 ]`	36	`if [ $nb_args -eq 1 ]`
37	`then`	37	`then`
38	`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';"`	38	`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';"`
39	`SECTION_LOG="Extraction de tous les journaux"`	39	`SECTION_LOG="Extraction de tous les journaux"`
40	`fi`	40	`fi`
41		41
42	`if [ $nb_args -eq 2 ]`	42	`if [ $nb_args -eq 2 ]`
43	`then`	43	`then`
44	`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';"`	44	`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';"`
45	`echo $QUERY`	45	`echo $QUERY`
46	`SECTION_LOG="Extraction des journaux à partir du $2"`	46	`SECTION_LOG="Extraction des journaux à partir du $2"`
47	`fi`	47	`fi`
48		48
49	`if [ $nb_args -eq 3 ]`	49	`if [ $nb_args -eq 3 ]`
50	`then`	50	`then`
51	`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';"`	51	`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';"`
52	`SECTION_LOG="Extraction des journaux entre $2 et $3"`	52	`SECTION_LOG="Extraction des journaux entre $2 et $3"`
53	`fi`	53	`fi`
54		54
55	`if [ $nb_args -eq 0 ]`	55	`if [ $nb_args -eq 0 ]`
56	`then`	56	`then`
57	`echo $usage`	57	`echo $usage`
58	`exit`	58	`exit`
59	`fi`	59	`fi`
60		60
61		61
62	`if [ $nb_args -gt 3 ]`	62	`if [ $nb_args -gt 3 ]`
63	`then`	63	`then`
64	`echo $usage`	64	`echo $usage`
65	`exit`	65	`exit`
66	`fi`	66	`fi`
67		67
68	`if [ -e $TMP_SQL ]`	68	`if [ -e $TMP_SQL ]`
69	`then`	69	`then`
70	`rm $TMP_SQL`	70	`rm $TMP_SQL`
71	`fi`	71	`fi`
72		72
73	`if [ -e $TMP_PDF ]`	73	`if [ -e $TMP_PDF ]`
74	`then`	74	`then`
75	`rm $TMP_PDF`	75	`rm $TMP_PDF`
76	`fi`	76	`fi`
77		77
78	`if [ -e $ARCHIVE_LOCATION ]`	78	`if [ -e $ARCHIVE_LOCATION ]`
79	`then`	79	`then`
80	`rm $ARCHIVE_LOCATION`	80	`rm $ARCHIVE_LOCATION`
81	`fi`	81	`fi`
82		82
83		83
84	`#get log information for each users`	84	`#get log information for each users`
85	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`	85	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`
86		86
87	`#Create HTML document which contains every informations about users`	87	`#Create HTML document which contains every informations about users`
88	`echo "<!DOCTYPE html>" > $TMP_HTML`	88	`echo "<!DOCTYPE html>" > $TMP_HTML`
89	`echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML`	89	`echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML`
90	`echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML`	90	`echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML`
91	`echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML`	91	`echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML`
92	`echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML`	92	`echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML`
93	`echo "</HEAD>" >> $TMP_HTML`	93	`echo "</HEAD>" >> $TMP_HTML`
94	`echo "<body>" >> $TMP_HTML`	94	`echo "<body>" >> $TMP_HTML`
95	`echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML`	95	`echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML`
96		96
97	`echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML`	97	`echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML`
98	`echo "<font size='1'>" >> $TMP_HTML`	98	`echo "<font size='1'>" >> $TMP_HTML`
99	`cat $TMP_SQL \| while read LIGNE_SQL`	99	`cat $TMP_SQL \| while read LIGNE_SQL`
100	`do`	100	`do`
101	`LOG_IP=$(echo $LIGNE_SQL \| cut -d',' -f3)`	101	`LOG_IP=$(echo $LIGNE_SQL \| cut -d',' -f3)`
102	`LOG_DATE1=$(echo $LIGNE_SQL \| cut -d',' -f4)`	102	`LOG_DATE1=$(echo $LIGNE_SQL \| cut -d',' -f4)`
103	`LOG_DATE2=$(echo $LIGNE_SQL \| cut -d',' -f5)`	103	`LOG_DATE2=$(echo $LIGNE_SQL \| cut -d',' -f5)`
104		104
105	`LOG_Y1=$(echo $LOG_DATE1 \| cut -d'-' -f1)`	105	`LOG_Y1=$(echo $LOG_DATE1 \| cut -d'-' -f1)`
106	`LOG_M1=$(echo $LOG_DATE1 \| cut -d'-' -f2)`	106	`LOG_M1=$(echo $LOG_DATE1 \| cut -d'-' -f2)`
107	`LOG_D1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f1)`	107	`LOG_D1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f1)`
108	`LOG_H1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f2)`	108	`LOG_H1=$(echo $LOG_DATE1 \| cut -d'-' -f3 \| cut -d' ' -f2)`
109		109
110	`LOG_Y2=$(echo $LOG_DATE2 \| cut -d'-' -f1)`	110	`LOG_Y2=$(echo $LOG_DATE2 \| cut -d'-' -f1)`
111	`LOG_M2=$(echo $LOG_DATE2 \| cut -d'-' -f2)`	111	`LOG_M2=$(echo $LOG_DATE2 \| cut -d'-' -f2)`
112	`LOG_D2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f1)`	112	`LOG_D2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f1)`
113	`LOG_H2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f2)`	113	`LOG_H2=$(echo $LOG_DATE2 \| cut -d'-' -f3 \| cut -d' ' -f2)`
114		114
115	`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")`	115	`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")`
116	`if [ ! -z "$DUMP" ]`	116	`if [ ! -z "$DUMP" ]`
117	`then`	117	`then`
118	`echo "<div class='container'> " >> $TMP_HTML`	118	`echo "<div class='container'> " >> $TMP_HTML`
119	`echo "<table class='table table-striped'>" >> $TMP_HTML`	119	`echo "<table class='table table-striped'>" >> $TMP_HTML`
120	`echo "<thead>" >> $TMP_HTML`	120	`echo "<thead>" >> $TMP_HTML`
121	`echo "<tr>" >> $TMP_HTML`	121	`echo "<tr>" >> $TMP_HTML`
122	`echo "<th>Username</th>" >> $TMP_HTML`	122	`echo "<th>Username</th>" >> $TMP_HTML`
123	`echo "<th>Client @MAC</th>" >> $TMP_HTML`	123	`echo "<th>Client @MAC</th>" >> $TMP_HTML`
124	`echo "<th>Client @IP</th>" >> $TMP_HTML`	124	`echo "<th>Client @IP</th>" >> $TMP_HTML`
125	`echo "<th>Login Time</th>" >> $TMP_HTML`	125	`echo "<th>Login Time</th>" >> $TMP_HTML`
126	`echo "<th>Logout Time</th>" >> $TMP_HTML`	126	`echo "<th>Logout Time</th>" >> $TMP_HTML`
127	`echo "<th>Upload</th>" >> $TMP_HTML`	127	`echo "<th>Upload</th>" >> $TMP_HTML`
128	`echo "<th>Download</th>" >> $TMP_HTML`	128	`echo "<th>Download</th>" >> $TMP_HTML`
129	`echo "<th>Cause</th>" >> $TMP_HTML`	129	`echo "<th>Cause</th>" >> $TMP_HTML`
130	`echo "</tr></thead><tbody><tr>" >> $TMP_HTML`	130	`echo "</tr></thead><tbody><tr>" >> $TMP_HTML`
131	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f1) "</td>" >> $TMP_HTML`	131	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f1) "</td>" >> $TMP_HTML`
132	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f2) "</td>" >> $TMP_HTML`	132	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f2) "</td>" >> $TMP_HTML`
133	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f3) "</td>" >> $TMP_HTML`	133	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f3) "</td>" >> $TMP_HTML`
134	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f4) "</td>" >> $TMP_HTML`	134	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f4) "</td>" >> $TMP_HTML`
135	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f5) "</td>" >> $TMP_HTML`	135	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f5) "</td>" >> $TMP_HTML`
136	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f7) "</td>" >> $TMP_HTML`	136	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f7) "</td>" >> $TMP_HTML`
137	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f6) "</td>" >> $TMP_HTML`	137	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f6) "</td>" >> $TMP_HTML`
138	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f8) "</td>" >> $TMP_HTML`	138	`echo "<td>" $(echo $LIGNE_SQL \| cut -d',' -f8) "</td>" >> $TMP_HTML`
139	`echo "</tr></tbody></table></div>" >> $TMP_HTML`	139	`echo "</tr></tbody></table></div>" >> $TMP_HTML`
140	`echo "<div class='container mySpace'> " >> $TMP_HTML`	140	`echo "<div class='container mySpace'> " >> $TMP_HTML`
141	`echo "<table class='table table-striped'>" >> $TMP_HTML`	141	`echo "<table class='table table-striped'>" >> $TMP_HTML`
142	`echo "<thead>" >> $TMP_HTML`	142	`echo "<thead>" >> $TMP_HTML`
143	`echo "<tr>" >> $TMP_HTML`	143	`echo "<tr>" >> $TMP_HTML`
144	`echo "<th>N°</th>" >> $TMP_HTML`	144	`echo "<th>N°</th>" >> $TMP_HTML`
145	`echo "<th>@IP src</th>" >> $TMP_HTML`	145	`echo "<th>@IP src</th>" >> $TMP_HTML`
146	`echo "<th>Port src</th>" >> $TMP_HTML`	146	`echo "<th>Port src</th>" >> $TMP_HTML`
147	`echo "<th>@IP dst</th>" >> $TMP_HTML`	147	`echo "<th>@IP dst</th>" >> $TMP_HTML`
148	`echo "<th>Port dst</th>" >> $TMP_HTML`	148	`echo "<th>Port dst</th>" >> $TMP_HTML`
149	`echo "<th>Date</th>" >> $TMP_HTML`	149	`echo "<th>Date</th>" >> $TMP_HTML`
150	`echo "</tr></thead><tbody>" >> $TMP_HTML`	150	`echo "</tr></thead><tbody>" >> $TMP_HTML`
151	`echo $DUMP >> $TMP_HTML`	151	`echo $DUMP >> $TMP_HTML`
152	`echo "</tbody></table></div>" >> $TMP_HTML`	152	`echo "</tbody></table></div>" >> $TMP_HTML`
153	`fi`	153	`fi`
154	`done`	154	`done`
155	`echo "</font>" >> $TMP_HTML`	155	`echo "</font>" >> $TMP_HTML`
156	`echo "</body>" >> $TMP_HTML`	156	`echo "</body>" >> $TMP_HTML`
157	`echo "</HTML>" >> $TMP_HTML`	157	`echo "</HTML>" >> $TMP_HTML`
158		158
159	`#inform users about that by setting the fourth bit of Filter-Id at 1.`	159	`# inform users about that by setting the Alcasar-Imputability-Warning attribute`
160	`QUERY="SELECT username from radreply INTO OUTFILE '$TMP_USERS' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`	160	`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;"`
161	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`	161	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`
162		162
163	`if [ -e $TMP_USERS ] && [ $(cat $TMP_USERS \| wc -l) -gt 0 ]`	-
164	`then`	-
165	`for user in $(cat $TMP_USERS)`	-
166	`do`	-
167	`QUERY="set @CurrentFilter=(SELECT value from radreply where username='$user');set @CurrentFilterLeft=(SELECT LEFT(@CurrentFilter,3));set @CurrentFilterRight=(SELECT RIGHT(@CurrentFilter,4));UPDATE radreply SET value = CONCAT((@CurrentFilterLeft),'1', (@CurrentFilterRight)) WHERE username='$user' ;"`	-
168	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`	-
169	`done`	-
170	`fi`	-
171	`rm $TMP_USERS`	-
172		-
173		-
174	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`	163	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`
175		164
176		165
177	`/usr/bin/7za a -tzip -p$1 -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`	166	`/usr/bin/7za a -tzip -p$1 -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`
178	`chown apache:apache $ARCHIVE_LOCATION`	167	`chown apache:apache $ARCHIVE_LOCATION`
179		168
180		169
181	`rm $TMP_HTML`	170	`rm $TMP_HTML`
182	`rm $TMP_SQL`	171	`rm $TMP_SQL`
183	`rm $TMP_PDF`	172	`rm $TMP_PDF`
184		173

Subversion Repositories ALCASAR

(root)/scripts/alcasar-generate_log.sh – Rev 2454 → 2501