Freeradius Interval Accounting or daily, hourly accounting

This is a simple solution for interval accounting or calculating the usage between intervals for a subscriber from accounting start, update and stop records.

Alternative method is inserting each session’s accounting record into the database and than calculating usage for each day. However you may face a problem where subscribers session is much more longer than intended interval, this leads to another logical problem for sessions which are longer than the desired internal.

I tried to use another approach and make this calculation in the radius with each accounting record.

Currently under test!

Definitions :

Current-Interval : current day, hour or minute what ever the interval we are using. Example : 2019-03-08 00:00:00 for daily, 2019-03-08 23:00:00 hourly.
Total Usage : Total usage in the interval.
Usage : Usage in the current session.

Calculation :

Goal is to have a single record in the database for each interval. Which will have at least sessionid, interval, totalusage ,usage.

I introduced another session variable by adding the current interval to the session-id of the accounting record. For example if the sessionid is AC3434545, newsessionid = AC3434545 + currentinterval like AC3434545_2019-03-08_10

NAS’s accounting session update period must be shorter than the intended interval! If your are tring to calculate hourly usage and the NAS is sending accounting update for the user’s session in every two hour, you can not calculate the hourly usage correctly (sampling period problem).

Method 1 :

Start Record : select sessionid,interval,totalusage,usage where interval = currentinterval ?
- not found : This is first record for the session in the current interval. Insert session sessionid = reguestsessionid, interval=currentinterval,totalusage=0,usage=0
- found : This must is another new session in the current interval. Just update the previous session in the current interval, sessionid = reguestsessionid, usage=0. totalusage is not updated!
Interim or Stop Record : As this a information is about an ongoing session, the start record of the session must be handled before. select sessionid,interval,totalusage,usage where sessionid = requestsessionid order by acctupdatime desc limit 1? Other wise this means we lost the start record, by network error etc. There is to different solution that I found for the sessions that last more than on interval.
- found :
- - interval = curreninteval ?
- - - yes : just update the record. update totalusage = totalusage + reguestusage – usage
- - - no : this is on going session from the previous interval. We must insert a new record in this interval. Insert the session sessionid = reguestsessionid, interval=currentinterval,totalusage=requestusage-prev-usage,usage=requestusage
- not found : lost a start record? error handling. You may discard the record, or just add the record as if its a start record. This may lead to understanding of interval usage of the subscriber. If you chose to insert, this help calculations for next interval if the sessin on goes more than one interval for this session. Insert session sessionid = reguestsessionid, interval=currentinterval,totalusage=0,usage=usage (you may set total usage = usage accourding to your report etc)

Method 2 :

Alternatively, I have created a second acctsessionid by adding current interval to the acctid. This makes the long sessions to have unique id during intervals. Which I found more interesting and useful as you track the ongoing sessions.

uniquesessionid = acctsessionid + currentinterval, for example AX4545554 + 2018121201 (which means 2018 12 12 01)

select sessionid,interval,totalusage,usage where uniquesessionid(which includes acctinterval) = requestuniqueid
- found : just update the record. update totalusage = totalusage + reguestusage – usage
- not found : this may be on going session from the previous interval. There must be a record for the previous interval. Try to find session by selecting with the accession which is the same during intervals for the session. select sessionid,interval,totalusage,usage where acctid=requestsessionid
  - found = insert new record totalusage = reguestusage – usage, usage = usage, interval = currentinterval

- - not found : lost a start record? error handling. You may discard the record, or just add the record as if its a start record. This may lead to understanding of interval usage of the subscriber. If you chose to insert this help calculations for next interval if the sessin on goes more than one interval for this session. Insert session sessionid = reguestsessionid, interval=currentinterval,totalusage=0,usage=usage (you may set total usage = usage accourding to your report etc)

As you decrease the accounting update periods on the NAS, calculations of the ongoing session between intervals will more accurate (sampling period). But as you decrease the update period, you may use more resources on the NAS and there will more request for radius to be handled. Try to find optimum value for your network.

Freeradius configuration :

This is for method 2 :

preacct {  preprocess  acct_counters64  acct_unique  }  accounting {   update control {   &request:My-Acct-Interval="%Y-%m-%d 00:00:00"   }   if (Acct-Status-Type == Start ) {   # Check if there is another session in this interval, all in   update control {   &request:My-Prev-Acc-Info := "%{%{sql:SELECT CONCAT(acctuniqueid,';',acctinterval,';',acctinputoctets,';',acctoutputoctets) as acctinfo \   FROM radius.radacct_daily \   WHERE  username = '%{User-Name}' and \   acctinterval = '%{My-Acct-Interval}'}:-notfound}"   }    if (&request:My-Prev-Acc-Info == 'notfound') {    # This is the first session in this interval, simply insert the start            %{sql:INSERT INTO radius.radacct_daily (`acctsessionid`, `acctuniqueid`, `username`,`acctinputoctets`,`acctoutputoctets`,`acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets`) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',0,0,'%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),0,0)}          } else {    # This is NOT the first session in this interval, just update the interval session values      %{sql:UPDATE `radius`.`radacct_daily` SET `acctsessionid` = '%{Acct-Session-Id}',`acctuniqueid` ='%{Acct-Unique-Session-Id}',`acctinputoctets` = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',`acctoutputoctets` = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', `acctupdatetime` = FROM_UNIXTIME(%{integer:Event-Timestamp}) WHERE username = '%{User-Name}' and acctinterval = '%{My-Acct-Interval}'}    }   } elsif (Acct-Status-Type == Interim-Update || Acct-Status-Type == Stop ) {    # Handle Accounting Stop and Interim (not accounint-on/off)    # if this a update about a session there must be a record before about this acctsession id in the database    # select the information about this session     update control {     &request:My-Prev-Acc-Info := "%{%{sql:\     SELECT CONCAT(acctuniqueid,';',acctinterval,';',acctinputoctets,';',acctoutputoctets) as acctinfo \     FROM radius.radacct_daily \     WHERE  username = '%{User-Name}' and \     acctuniqueid = '%{Acct-Unique-Session-Id}'}:-notfound}"     }       if (&request:My-Prev-Acc-Info =~ /(.*);(.*);(\d*);(\d*)/) {          if (My-Acct-Interval == "%{2}" ) {          # we are in the same interval we simply increase the totalcounters           %{sql:UPDATE `radius`.`radacct_daily` SET `acctsessionid` = '%{Acct-Session-Id}',`acctuniqueid` ='%{Acct-Unique-Session-Id}',`totalacctinputoctets` = `totalacctinputoctets` + ('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}') - %{3},`totalacctoutputoctets` = `totalacctoutputoctets` + ('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}') - %{4},`acctinputoctets` = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',`acctoutputoctets` = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', `acctupdatetime` = FROM_UNIXTIME(%{integer:Event-Timestamp}) WHERE username = '%{User-Name}' and acctinterval = '%{My-Acct-Interval}'\           }         } else {                # we are in a different interval, simply insert like a start with new interval info          %{sql:INSERT INTO radius.radacct_daily (`acctsessionid`, `acctuniqueid`, `username`, `acctinputoctets`,`acctoutputoctets`,`acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets`\          ) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',`acctinputoctets` = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',`acctoutputoctets` = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}','%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}') - %{3},('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}') - %{4})}         }       } else {       # this is error there should be some record with this acctsessionid as this is a update or stop, missing start record.       # handle like start and insert         %{sql:INSERT INTO radius.radacct_daily ( `acctsessionid`, `acctuniqueid`, `username`,`acctinputoctets`,`acctoutputoctets`, `acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets`) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}'),('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}'),'%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),0,0)}         }   }   handled  }

This is for method 1 :

server default {




listen {

type = auth

ipaddr = *

port = 0

limit {

     max_connections = 16

     lifetime = 0

     idle_timeout = 30

}

}




listen {

ipaddr = *

port = 0

type = acct




limit {

}

}

       modules {

acct_unique {

   key = "User-Name, Acct-Session-Id, NAS-IP-Addres"

}

       }




authorize {

ok

}










authenticate {

ok

}




preacct {

preprocess

acct_counters64

acct_unique

}

accounting {







    update control {

    &request:My-Acct-Interval="%Y-%m-%d 00:00:00"

    &request:Acct-Unique-Session-Id:="%{Acct-Unique-Session-Id}%Y-%m-%d"

    }

 if (Acct-Status-Type == Start ) {

 # Check if there is another session in this interval, all in

 update control {

 &request:My-Prev-Acc-Info := "%{%{sql:SELECT CONCAT(acctuniqueid,';',acctinterval,';',acctinputoctets,';',acctoutputoctets) as acctinfo \

 FROM radius.radacct_daily \

 WHERE  username = '%{User-Name}' and \

 acctinterval = '%{My-Acct-Interval}'}:-notfound}"

 }

  if (&request:My-Prev-Acc-Info == 'notfound') {

  # This is the first session in this interval, simply insert the start




    %{sql:INSERT INTO radius.radacct_daily (`acctsessionid`, `acctuniqueid`, `username`,`acctinputoctets`,`acctoutputoctets`,`acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets`) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',0,0,'%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),0,0)}




  } else {

  # This is NOT the first session in this interval, just update the interval session values

    %{sql:UPDATE `radius`.`radacct_daily` SET `acctsessionid` = '%{Acct-Session-Id}',`acctuniqueid` ='%{Acct-Unique-Session-Id}',`acctinputoctets` = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',`acctoutputoctets` = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', `acctupdatetime` = FROM_UNIXTIME(%{integer:Event-Timestamp}) WHERE username = '%{User-Name}' and acctinterval = '%{My-Acct-Interval}'}

  }

 } elsif (Acct-Status-Type == Interim-Update || Acct-Status-Type == Stop ) {

  # Handle Accounting Stop and Interim (not accounint-on/off)

  # if this a update about a session there must be a record before about this acctsession id in the database

  # select the information about this session, get the least one record

   update control {

   &request:My-Prev-Acc-Info := "%{%{sql:\

   SELECT CONCAT(acctuniqueid,';',acctinterval,';',acctinputoctets,';',acctoutputoctets) as acctinfo \

   FROM radius.radacct_daily \

   WHERE  username = '%{User-Name}' and \

   acctuniqueid = '%{Acct-Unique-Session-Id}' ORDER by acctinterval desc LIMIT 1 }:-notfound}"

   }

     if (&request:My-Prev-Acc-Info =~ /(.*);(.*);(\d*);(\d*)/) {

        if (My-Acct-Interval == "%{2}" ) {

        # we are in the same interval we simply increase the totalcounters

         %{sql:UPDATE `radius`.`radacct_daily` SET `acctsessionid` = '%{Acct-Session-Id}',`acctuniqueid` ='%{Acct-Unique-Session-Id}',`totalacctinputoctets` = `totalacctinputoctets` + ('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}') - %{3},`totalacctoutputoctets` = `totalacctoutputoctets` + ('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}') - %{4},`acctinputoctets` = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',`acctoutputoctets` = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', `acctupdatetime` = FROM_UNIXTIME(%{integer:Event-Timestamp}) WHERE username = '%{User-Name}' and acctinterval = '%{My-Acct-Interval}'\

         }

       } else {

       # we are in a different interval, simply insert like a start with new interval info

        %{sql:INSERT INTO radius.radacct_daily (`acctsessionid`, `acctuniqueid`, `username`, `acctinputoctets`,`acctoutputoctets`,`acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets` ) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}','%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}','%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}') - %{3},('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}') - %{4})}

       }

     } else {

     # this is error there should be some record with this acctsessionid as this is a update or stop, missing start record.

     # handle like start and insert

       %{sql:INSERT INTO radius.radacct_daily ( `acctsessionid`, `acctuniqueid`, `username`,`acctinputoctets`,`acctoutputoctets`, `acctinterval`,`acctupdatetime`,`totalacctoutputoctets`,`totalacctinputoctets`) VALUES ('%{Acct-Session-Id}','%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',('%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}'),('%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}'),'%{My-Acct-Interval}',FROM_UNIXTIME(%{integer:Event-Timestamp}),0,0)}

     }

 }

 handled

}

}