Calculate QAD System User Access Time by parsing DB log

First, let's take a look to progress database log content:

[2016/08/31 12:17:21.383+0800] Login sunw8 /dev/pts/16.
[2016/08/31 12:17:21.383+0800] Logout sunw8 /dev/pts/16.
[2016/08/31 12:52:33.114+0800] Logout liangl1 /dev/pts/29.
[2016/08/31 12:52:35.233+0800] Login liangl1 /dev/pts/29.
[2016/08/31 12:57:17.224+0800] Login wuq4 /dev/pts/36.
[2016/08/31 12:57:17.224+0800] Logout wuq4 /dev/pts/36.
[2016/08/31 12:57:20.348+0800] Login wuq4 /dev/pts/36.
[2016/08/31 13:00:59.231+0800] Login sunx1 /dev/pts/14.
...

Script Explore

Main Logic

#----------Define Variables----------
today=$(date +%Y%m%d)  
working_dir=/tmp/UATreport  
dblog=/PATH/TO/DB/dbname.lg  
exclude_list="mfg|dbadmin"  
temp_file=dbname.tmp  
mailto=admin@corporate.com

#----------Start Main Function----------
#Step 0:
#Initialize working environment
if [ ! -d $working_dir ]  
then  
   mkdir -p $working_dir
fi

cd $working_dir  
rm *

#Step 1:
#Filter DB log entries and group by each user
#Get today's db log items
grep -E "$(date +%Y/%m/%d).*/dev/pts" $dblog|grep -Ev $exclude_list|awk '{print $1,$8,$10,$12}' |sed 's/@/ /g' >> user_access_log.$today

#Step 2:
#Get distinct user list
awk '{print $4}' user_access_log.$today | sort | uniq >> userlist.$today

#Step 3:
#Calculate user access time by individual and create report
for user in `cat  userlist.$today`  
do  
   grep $user user_access_log.$today > $user.log
   create_user_report $user
done

#Setp 4:
#Sort the report data by user total access time and send email
sort -n -rk 2 -k 3 $temp_file -o $temp_file  
draw_table $temp_file

mutt -e 'set content_type=text/html' -s 'QAD User Access Time Daily Report' $mailto < report.html  

Functions

create_user_report

create_user_report()  
{
  #initialize the total access time
  total_time="0"
  while [ -s $1.log ]   # -s file is not empty
  do
        if head -1 $1.log | grep -q "Login"
        then
          get_login_info $1
          get_logout_time $1 $login_tty
        else
          #if only find logout record, means user login since yesterday 
          #then set today's login time as AM 00:00
          logout_time=$(head -1 $1.log|awk '{$a=substr($0,2,19);print $a}')
          login_time=$(date +"%Y/%m/%d")
          sed -i 1d $1.log
        fi
        get_total_time "$logout_time" "$login_time"
  done
  hour=`expr $total_time / 3600`
  minute=`expr $total_time % 3600 / 60`
  echo "$1 $hour $minute" >> $temp_file
  unset total_time logout_time login_time login_tty
}

get_login_info

#grep login time and tty from DB log
get_login_info()  
{
  login_time=$(grep "Login" $1.log|head -1|awk '{$a=substr($0,2,19);print $a}')
  login_tty=$(grep "Login" $1.log|head -1|awk '{print $5}')
  line="$(echo "$login_time.*Login.*$login_tty$"| sed 's/\//\\\//g')"
  sed -i "/$line/d" $1.log
}

get_logout_time

#find logout record and grep logout time
#parameters: $1-userID $2-tty
get_logout_time()  
{
  if grep -qE "Logout.*$2$" $1.log
  then
    logout_time=$(grep -E "Logout.*$2$" $1.log |head -1|awk '{$a=substr($0,2,19);print $a}')
    line="$(echo "$logout_time.*Logout.*$2$"| sed 's/\//\\\//g')"
    sed -i "/$line/d" $1.log
  else
    #if no logout record find, set logout time to current time
    logout_time=$(date +"%Y/%m/%d %H:%M:%S")
  fi
}

get_total_time

#calculate total access time in seconds
get_total_time()  
{
  end_time=`date +%s -d "$1"`
  start_time=`date +%s -d "$2"`
  total_time=$(($end_time - $start_time + $total_time))
}

draw_table

#draw colourful report in html format
#parameter: $1-temp_file
draw_table()  
{
  echo "<table border=\"1\">" >> report.html
  echo -e "\t<tr>\n\t\t<th>ID</th>\n\t\t<th>Hours</th>\n\t\t<th>Minutes</th>\n\t</tr>" >> report.html
  while read ID Hour Minute
  do
    if [ $Hour -gt 2 ] 
    then
      #if login time > 2hour, draw the cell in read color
      color_cell="<td bgcolor=\"red\">"
    else
      color_cell="<td>"
    fi
    echo -e "\t<tr>\n\t\t<td>$ID</td>\n\t\t$color_cell$Hour</td>\n\t\t<td>$Minute</td>\n\t</tr>" >> report.html
  done < $1
  echo "</table>" >> report.html
}