0,0 → 1,168 |
#Corrélation et Generation des logs au format PDF |
#Il est possible de demander les logs : |
#-depuis le début (pas d'argument) |
#-à partir d'une date (un seul argument) |
#-en spécifiant un intervale (deux arguments correspondant aux bornes respectives) |
#Par Raphaël Pion |
|
|
|
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' })" |
nb_args=$# |
DIR='/var/www/html/acc/backup/' |
TMP_SQL="/tmp/log_sql.csv" |
TMP_USERS="/tmp/log_users" |
TMP_HTML="$DIR/log_nf.html" |
TMP_PDF="$DIR/imputabilities_logs-$(date +%F).pdf" |
PASSWD_FILE="/root/ALCASAR-passwords.txt" |
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 |
exit |
fi |
|
|
if [ $nb_args -gt 3 ] |
then |
echo $usage |
exit |
fi |
|
if [ -e $TMP_SQL ] |
then |
rm $TMP_SQL |
fi |
|
if [ -e $TMP_PDF ] |
then |
rm $TMP_PDF |
fi |
|
if [ -e $ARCHIVE_LOCATION ] |
then |
rm $ARCHIVE_LOCATION |
fi |
|
|
#get log information for each users |
mysql -D radius -u root -p$(cat $PASSWD_FILE | grep "root /" | rev | cut -d' ' -f1 | rev) -e "$QUERY" |
|
#Create HTML document which contains every informations about users |
echo "<!DOCTYPE html>" > $TMP_HTML |
echo "<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>" >> $TMP_HTML |
echo "<TITLE>ALCASAR Report</TITLE>" >> $TMP_HTML |
echo "<link rel='stylesheet' type='text/css' href='../../css/bootstrap.min.css'>" >> $TMP_HTML |
echo "<link rel='stylesheet' type='text/css' href='../../css/report.css'>" >> $TMP_HTML |
echo "</HEAD>" >> $TMP_HTML |
echo "<body>" >> $TMP_HTML |
echo "<h1>$SECTION_LOG</h1>" >> $TMP_HTML |
|
echo "<i><p style='text-align: right;'>Date de création $(date +%F)</p></i>" >> $TMP_HTML |
echo "<font size='1'>" >> $TMP_HTML |
cat $TMP_SQL | while read LIGNE_SQL |
do |
LOG_IP=$(echo $LIGNE_SQL | cut -d',' -f3) |
LOG_DATE1=$(echo $LIGNE_SQL | cut -d',' -f4) |
LOG_DATE2=$(echo $LIGNE_SQL | cut -d',' -f5) |
|
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 "<th>Client @IP</th>" >> $TMP_HTML |
echo "<th>Login Time</th>" >> $TMP_HTML |
echo "<th>Logout Time</th>" >> $TMP_HTML |
echo "<th>Upload</th>" >> $TMP_HTML |
echo "<th>Download</th>" >> $TMP_HTML |
echo "<th>Cause</th>" >> $TMP_HTML |
echo "</tr></thead><tbody><tr>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f1) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f2) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f3) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f4) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f5) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f6) "</td>" >> $TMP_HTML |
echo "<td>" $(echo $LIGNE_SQL | cut -d',' -f7) "</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 |
echo "<th>Port src</th>" >> $TMP_HTML |
echo "<th>@IP dst</th>" >> $TMP_HTML |
echo "<th>Port dst</th>" >> $TMP_HTML |
echo "<th>Date</th>" >> $TMP_HTML |
echo "</tr></thead><tbody>" >> $TMP_HTML |
echo $DUMP >> $TMP_HTML |
echo "</tbody></table></div>" >> $TMP_HTML |
fi |
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 -D radius -u root -p$(cat $PASSWD_FILE | grep "root /" | rev | cut -d' ' -f1 | rev) -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 -D radius -u root -p$(cat $PASSWD_FILE | grep "root /" | rev | cut -d' ' -f1 | rev) -e "$QUERY" |
done |
fi |
rm $TMP_USERS |
|
|
/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 |