#!/bin/bash

echo "[`date`] <$$> (${USER}) - command: $0 - cwd: ${PWD} - shell: $SHELL" >> /var/log/legacy/sec-bin.log

DEC_PLACES=2
# Now with more execute bit.
usage()
{
cat << EOF
usage: $0 options

Written by Jonathan G.
This shows the top 20 mysql users on the server.

OPTIONS:
   -h      Show this message

   -s      Sort by:
           Options are:
           cpu_time, table_rows_read, rows_fetched, rows_updated, total_connections, connected_time, busy_time, select_commands, update_commands, other_commands
           Example: topdbusers -s busy_time
           (Options are case sensitive)

   -f      Flush user statistics

   -e      Enable user statistics

EOF
}

OPT="CPU_TIME"
while getopts “hs:fed” OPTION
do
     case $OPTION in
         h)
             usage
             exit 1
             ;;
         s)
             OPT=$OPTARG
             if [[ "$OPT" = "cpu_time" || "$OPT" = "table_rows_read" || "$OPT" = "rows_fetched" || "$OPT" = "rows_updated" || "$OPT" = "total_connections" || "$OPT" = "connected_time" || "$OPT" = "busy_time" || "$OPT" = "select_commands" || "$OPT" = "update_commands" || "$OPT" = "other_commands" ]]
             then
             mysql -e "select USER, TRUNCATE(CPU_TIME, ${DEC_PLACES}) as CPU_TIME, TABLE_ROWS_READ, ROWS_FETCHED, ROWS_UPDATED, TOTAL_CONNECTIONS, CONNECTED_TIME, TRUNCATE(BUSY_TIME, ${DEC_PLACES}) as BUSY_TIME, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS from INFORMATION_SCHEMA.USER_STATISTICS order by $OPT desc limit 20;"
             else
             usage
             fi
             exit
             ;;
         f)
             mysql -e "FLUSH USER_STATISTICS;"
             exit
             ;;
         e)
             mysql -e "set global userstat=on;"
             exit
             ;;
         ?)
             usage
             exit
             ;;
     esac
done
mysql -e "select USER, TRUNCATE(CPU_TIME, ${DEC_PLACES}) as CPU_TIME, TABLE_ROWS_READ, ROWS_FETCHED, ROWS_UPDATED, TOTAL_CONNECTIONS, CONNECTED_TIME, TRUNCATE(BUSY_TIME, ${DEC_PLACES}) as BUSY_TIME, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS from INFORMATION_SCHEMA.USER_STATISTICS order by CPU_TIME desc limit 20;"
