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