radius-log

#! /bin/bash

. /etc/freeradius-bash-admin.conf

_last_logins() {
  _mysql "
    SELECT username, authdate, nasip 
    FROM radpostauth
    WHERE reply = 'Access-Accept'
      AND nasip <> ''
    ORDER BY authdate DESC
    LIMIT 100;
  "
}

_user_count() {

  INTERVAL="$@"

  if [ -z "$INTERVAL" ] ; then
    INTERVAL="1 WEEK"
  fi

  _mysql "
    SELECT
      CONCAT_WS(' ', u.firstname, u.lastname) as Name,
      r.username AS Username,
      COUNT(r.username) AS Count
    FROM radpostauth AS r
    JOIN infouser AS u
      ON u.username = r.username
    WHERE r.reply = 'Access-Accept'
      AND r.nasip <> ''
      AND r.authdate > CURDATE() - INTERVAL $INTERVAL
    GROUP BY r.username
    ORDER BY Count DESC;
  "
}

_user_log() {
  _mysql "
    SELECT *
    FROM radpostauth
    ORDER BY authdate DESC
    LIMIT 500;
  "
}

_nas_count() {
  _mysql "
    SELECT nas.shortname, COUNT(nas.id) AS logincount
    FROM nas
    LEFT JOIN radpostauth
    ON radpostauth.nasip = nas.nasname
    GROUP BY nas.id;
  "
}

_db_values() {
  TABLE="$1"

  if [ -z "$TABLE" ]; then
    echo "Select table!"
    _mysql "SHOW TABLES;"
    exit 1
  fi

  _mysql "
    SELECT *
    FROM $TABLE
    LIMIT 500;
  "
}

_db_layout() {
  TABLE="$1"

  if [ -z "$TABLE" ]; then
    echo "Select table!"
    _mysql "SHOW TABLES;"
    exit 1
  fi

  echo "

# Table '$TABLE'

"

  _mysql "DESCRIBE $TABLE;"
}

_log_file() {
  tail -n 100 -f /var/log/freeradius/radius.log
}

_usage() {
  echo "Usage: radius-log <options>

-> Show log informations.

Options:

  last-logins               : Show last user logins.
  user-count <interval>     : E. g.:  \"1 week\" or \"2 month\"
  user-log                  : Show user log.
  nas-count                 : Show NAS count.
  db-values <table>         : Show database values.
  db-layout <table>         : Show database table layout.
  log-file                  : Show log file.
"
}

case $1 in

  last-logins)
    _last_logins
  ;;

  user-count)
    shift
    _user_count "$@"
  ;;

  user-log)
    _user_log
  ;;

  nas-count)
    _nas_count
  ;;

  db-values)
    _db_values "$2"
  ;;

  db-layout)
    _db_layout "$2"
  ;;

  log-file)
    _log_file
  ;;

  *)
    _usage
  ;;

esac

# vim: set ts=2 sw=2 sts=2 et :