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



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

fi
 
 
if [ $nb_args -gt 3 ]
then
	echo $usage
	exit
fi
 
if [ -e $TMP_SQL ]
then
	rm $TMP_SQL

	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"

 
	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 "<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

done
echo "</font>" >> $TMP_HTML
echo "</body>" >> $TMP_HTML
echo "</HTML>" >> $TMP_HTML
 
#inform users about that by setting the fourth bit of Filter-Id at 1.
QUERY="SELECT username from radreply INTO OUTFILE '$TMP_USERS' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"
mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"
 
if [ -e $TMP_USERS ] && [ $(cat $TMP_USERS | wc -l) -gt 0 ]
then
	for user in $(cat $TMP_USERS)
	do
		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' ;"
		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 2412	Rev 2454
Line 33...	Line 33...
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`
Line 59...	Line 59...
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`
Line 75...	Line 75...
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"`
Line 104...	Line 104...
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`
Line 135...	Line 135...
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`
Line 154...	Line 154...
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 fourth bit of Filter-Id at 1.`
160	`QUERY="SELECT username from radreply INTO OUTFILE '$TMP_USERS' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`	160	`QUERY="SELECT username from radreply INTO OUTFILE '$TMP_USERS' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n';"`
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 ]`	163	`if [ -e $TMP_USERS ] && [ $(cat $TMP_USERS \| wc -l) -gt 0 ]`
164	`then`	164	`then`
165	`for user in $(cat $TMP_USERS)`	165	`for user in $(cat $TMP_USERS)`
166	`do`	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' ;"`	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"`	168	`mysql -u root -p"$DB_ROOT_PW" -D radius -e "$QUERY"`
Line 173...	Line 173...
173		173
174	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`	174	`/usr/bin/wkhtmltopdf $TMP_HTML $TMP_PDF`
175		175
176		176
177	`/usr/bin/7za a -tzip -p$1 -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`	177	`/usr/bin/7za a -tzip -p$1 -mem=AES256 $ARCHIVE_LOCATION $TMP_PDF`
178	`chown apache:apache $ARCHIVE_LOCATION`	178	`chown apache:apache $ARCHIVE_LOCATION`
179		179
180		180
181	`rm $TMP_HTML`	181	`rm $TMP_HTML`
182	`rm $TMP_SQL`	182	`rm $TMP_SQL`
183	`rm $TMP_PDF`	183	`rm $TMP_PDF`

Subversion Repositories ALCASAR

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