# cat /usr/local/etc/raddb/sqlcounter.conf 

sqlcounter noresetcounter {
            counter-name = Session-Timeout
            check-name = Session-Timeout
            reply-name = Session-Timeout
            sqlmod-inst = sql
            key = User-Name
            reset = never
            query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"

}


sqlcounter chillispot_max_bytes {
            counter-name = ChilliSpot-Max-Total-Octets
            check-name = ChilliSpot-Max-Total-Octets
            reply-name = ChilliSpot-Max-Total-Octets
            sqlmod-inst = sql
            key = User-Name
            reset = never
            query = "SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) FROM radacct WHERE UserName='%{%k}'"

}

sqlcounter chillispot_max_bytes_daily {
            counter-name = ChilliSpot-Max-Total-Octets
            check-name = ChilliSpot-Max-Total-Octets
            reply-name = ChilliSpot-Max-Total-Octets
            sqlmod-inst = sql
            key = User-Name
            reset = daily
            query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}


sqlcounter chillispot_max_bytes_weekly {
            counter-name = ChilliSpot-Max-Total-Octets
            check-name = ChilliSpot-Max-Total-Octets
            reply-name = ChilliSpot-Max-Total-Octets
            sqlmod-inst = sql
            key = User-Name
            reset = weekly
            query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}

sqlcounter chillispot_max_bytes_monthly {
            counter-name = ChilliSpot-Max-Total-Octets
            check-name = ChilliSpot-Max-Total-Octets
            reply-name = ChilliSpot-Max-Total-Octets
            sqlmod-inst = sql
            key = User-Name 
            reset = monthly
            query = "SELECT SUM(AcctInputOctets - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}