Subversion Repositories ALCASAR

Rev

Rev 2079 | Go to most recent revision | Details | Last modification | View Log

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