Subversion Repositories ALCASAR

Rev

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

Rev Author Line No. Line
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