Subversion Repositories ALCASAR

Rev

Rev 2506 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log

Rev Author Line No. Line
2532 tom.houday 1 
#!/bin/bash
2 
#
3 
# $Id: alcasar-generate_log.sh 2532 2018-04-30 03:55:35Z tom.houdayer $
4 
#
2079 raphael.pi 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.
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.
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 
#
11 
#Il est possible de demander les logs d'imputabilité :
2010 raphael.pi 12 
#-depuis le début (pas d'argument)
13 
#-à partir d'une date (un seul argument)
14 
#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives)
15 
#Par Raphaël Pion
16 
 
17 
 
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' })"
20 
nb_args=$#
21 
DIR='/var/www/html/acc/backup/'
22 
TMP_SQL="/tmp/log_sql.csv"
23 
TMP_USERS="/tmp/log_users"
24 
TMP_HTML="$DIR/log_nf.html"
25 
TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf"
26 
PASSWD_FILE="/root/ALCASAR-passwords.txt"
2412 tom.houday 27 
DB_ROOT_PW=$(grep '^db_root=' $PASSWD_FILE | cut -d'=' -f 2-)
2010 raphael.pi 28 
ARCHIVE_LOCATION="$DIR/imputabilities_logs.zip"
29 
 
30 
 
31 
if [ $nb_args -eq 1 ]
32 
then
2454 tom.houday 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"
2010 raphael.pi 35 
fi
36 
 
37 
if [ $nb_args -eq 2 ]
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';"
2454 tom.houday 40 
	SECTION_LOG="Extraction des journaux à partir du $2"
2010 raphael.pi 41 
fi
42 
 
43 
if [ $nb_args -eq 3 ]
44 
then
2454 tom.houday 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"
2010 raphael.pi 47 
fi
48 
 
49 
if [ $nb_args -eq 0 ]
50 
then
51 
	echo $usage
52 
	exit
53 
fi
54 
 
55 
 
56 
if [ $nb_args -gt 3 ]
57 
then
2454 tom.houday 58 
	echo $usage
59 
	exit
2010 raphael.pi 60 
fi
61 
 
62 
if [ -e $TMP_SQL ]
63 
then
64 
	rm $TMP_SQL
65 
fi
66 
 
67 
if [ -e $TMP_PDF ]
68 
then
69 
	rm $TMP_PDF
70 
fi
71 
 
72 
if [ -e $ARCHIVE_LOCATION ]
73 
then
2454 tom.houday 74 
	rm $ARCHIVE_LOCATION
2010 raphael.pi 75 
fi
76 
 
77 
 
78 
#get log information for each users
2412 tom.houday 79 
mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"
2010 raphael.pi 80 
 
81 
#Create HTML document which contains every informations about users
82 
echo "<!DOCTYPE html>" > $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
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
87 
echo "</HEAD>" >> $TMP_HTML
88 
echo "<body>" >> $TMP_HTML
89 
echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML
90 
 
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
93 
cat $TMP_SQL | while read LIGNE_SQL
94 
do
95 
	LOG_IP=$(echo $LIGNE_SQL | cut -d',' -f3)
96 
	LOG_DATE1=$(echo $LIGNE_SQL | cut -d',' -f4)
97 
	LOG_DATE2=$(echo $LIGNE_SQL | cut -d',' -f5)
98 
 
99 
	LOG_Y1=$(echo $LOG_DATE1 | cut -d'-' -f1)
100 
	LOG_M1=$(echo $LOG_DATE1 | cut -d'-' -f2)
101 
	LOG_D1=$(echo $LOG_DATE1 | cut -d'-' -f3 | cut -d' ' -f1)
102 
	LOG_H1=$(echo $LOG_DATE1 | cut -d'-' -f3 | cut -d' ' -f2)
2454 tom.houday 103 
 
2010 raphael.pi 104 
	LOG_Y2=$(echo $LOG_DATE2 | cut -d'-' -f1)
105 
	LOG_M2=$(echo $LOG_DATE2 | cut -d'-' -f2)
106 
	LOG_D2=$(echo $LOG_DATE2 | cut -d'-' -f3 | cut -d' ' -f1)
107 
	LOG_H2=$(echo $LOG_DATE2 | cut -d'-' -f3 | cut -d' ' -f2)
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")
110 
	if [ ! -z "$DUMP" ]
111 
	then
2454 tom.houday 112 
		echo "<div class='container'> " >> $TMP_HTML
2010 raphael.pi 113 
		echo "<table class='table table-striped'>" >> $TMP_HTML
114 
		echo "<thead>" >> $TMP_HTML
115 
		echo "<tr>" >> $TMP_HTML
116 
		echo "<th>Username</th>" >> $TMP_HTML
117 
		echo "<th>Client @MAC</th>" >> $TMP_HTML
118 
		echo "<th>Client @IP</th>" >> $TMP_HTML
119 
		echo "<th>Login Time</th>" >> $TMP_HTML
120 
		echo "<th>Logout Time</th>" >> $TMP_HTML
121 
		echo "<th>Upload</th>" >> $TMP_HTML
122 
		echo "<th>Download</th>" >> $TMP_HTML
123 
		echo "<th>Cause</th>" >> $TMP_HTML
124 
		echo "</tr></thead><tbody><tr>" >> $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
127 
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f3) "</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
2079 raphael.pi 130 
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f7) "</td>" >> $TMP_HTML
2010 raphael.pi 131 
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f6) "</td>" >> $TMP_HTML
132 
		echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f8) "</td>" >> $TMP_HTML
133 
		echo "</tr></tbody></table></div>" >> $TMP_HTML
2454 tom.houday 134 
		echo "<div class='container mySpace'> " >> $TMP_HTML
2010 raphael.pi 135 
		echo "<table class='table table-striped'>" >> $TMP_HTML
136 
		echo "<thead>" >> $TMP_HTML
137 
		echo "<tr>" >> $TMP_HTML
138 
		echo "<th>N°</th>" >> $TMP_HTML
139 
		echo "<th>@IP src</th>" >> $TMP_HTML
140 
		echo "<th>Port src</th>" >> $TMP_HTML
141 
		echo "<th>@IP dst</th>" >> $TMP_HTML
142 
		echo "<th>Port dst</th>" >> $TMP_HTML
143 
		echo "<th>Date</th>" >> $TMP_HTML
144 
		echo "</tr></thead><tbody>" >> $TMP_HTML
145 
		echo $DUMP >> $TMP_HTML
146 
		echo "</tbody></table></div>" >> $TMP_HTML
147 
	fi
148 
done
149 
echo "</font>" >> $TMP_HTML
150 
echo "</body>" >> $TMP_HTML
151 
echo "</HTML>" >> $TMP_HTML
152 
 
2501 tom.houday 153 
# inform users about that by setting the Alcasar-Imputability-Warning attribute
2506 tom.houday 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;"
2412 tom.houday 155 
mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"
2010 raphael.pi 156 
 
157 
/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF
158 
 
159 
 
2502 tom.houday 160 
/usr/bin/7za a -tzip -p"$1" -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF
2454 tom.houday 161 
chown apache:apache $ARCHIVE_LOCATION
2010 raphael.pi 162 
 
163 
 
164 
rm $TMP_HTML
165 
rm $TMP_SQL
166 
rm $TMP_PDF