[1] Create a text file called sqlcounter.conf in the same
directory where radiusd.conf resides (usually /usr/local/etc/raddb)
-with the following content:
+with the following content (for mysql):
#-----#
sqlcounter noresetcounter {
#-----#
+The respective lines for postgresql are:
+
+query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
+query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
+query = "SELECT SUM(AcctSessionTime - GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) FROM radacct WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
+
+If you are running postgres 7.x, you may not have a GREATER function.
+
+An example of one is:
+
+CREATE OR REPLACE FUNCTION "greater"(integer, integer) RETURNS integer AS '
+DECLARE
+ res INTEGER;
+ one INTEGER := 0;
+ two INTEGER := 0;
+BEGIN
+ one = $1;
+ two = $2;
+ IF one IS NULL THEN
+ one = 0;
+ END IF;
+ IF two IS NULL THEN
+ two = 0;
+ END IF;
+ IF one > two THEN
+ res := one;
+ ELSE
+ res := two;
+ END IF;
+ RETURN res;
+END;
+' LANGUAGE 'plpgsql';
+
[2] Include the above file to radiusd.conf by adding a line in
modules{ } section
# previous reset period into the current period but
# involves more work for the SQL server than those
# below
+ # For mysql:
query = "SELECT SUM(AcctSessionTime - \
GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
FROM radacct WHERE UserName='%{%k}' AND \
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
+ # For postgresql:
+# query = "SELECT SUM(AcctSessionTime - \
+# GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \
+# FROM radacct WHERE UserName='%{%k}' AND \
+# AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
+
# This query ignores calls that started in a previous
# reset period and continue into into this one. But it
# is a little easier on the SQL server
+ # For mysql:
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
# UserName='%{%k}' AND AcctStartTime > FROM_UNIXTIME('%b')"
+ # For postgresql:
+# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
+# UserName='%{%k}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
+
# This query is the same as above, but demonstrates an
# additional counter parameter '%e' which is the
# timestamp for the end of the period
+ # For mysql:
# query = "SELECT SUM(AcctSessionTime) FROM radacct \
# WHERE UserName='%{%k}' AND AcctStartTime BETWEEN \
# FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
+
+ # For postgresql:
+# query = "SELECT SUM(AcctSessionTime) FROM radacct \
+# WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \
+# BETWEEN '%b' AND '%e'"
}
sqlcounter monthlycounter {
# previous reset period into the current period but
# involves more work for the SQL server than those
# below
+ # The same notes above about the differences between mysql
+ # versus postgres queries apply here.
query = "SELECT SUM(AcctSessionTime - \
GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
FROM radacct WHERE UserName='%{%k}' AND \