fix radius radacct duplicate entries when using pptp

So the version radiusclient appears to make duplicate entries, when using it to authenticate PPTP.   The steps below outline the steps to fix this problem.  It is a bit of a hack, but quite a beautiful  solution at the same time.

What the radius database looks like white the client is connected.

mysql> select * from radacct;
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+————–+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
| radacctid | acctsessionid  | acctuniqueid     | username        | groupname | realm | nasipaddress | nasportid | nasporttype | acctstarttime       | acctstoptime | acctsessiontime | acctauthentic | connectinfo_start | connectinfo_stop | acctinputoctets | acctoutputoctets | calledstationid | callingstationid | acctterminatecause | servicetype | framedprotocol | framedipaddress | acctstartdelay | acctstopdelay | xascendsessionsvrkey |
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+————–+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
|         1 | 4F416D59154300 | 55f94b13a50e4069 | user@domain.com |           |       | 22.33.9.35  | 0         | Async       | 2012-02-19 22:44:57 | NULL         |               0 | RADIUS        |                   |                  |               0 |                0 |                 | 72.85.213.111    |                    | Framed-User | PPP            | 10.80.72.1      |              0 |             0 |                      |
|         2 | 4F416D59154301 | a2d784b0c15ca9a4 | user@domain.com |           |       | 22.33.9.35  | 0         | Async       | 2012-02-19 22:44:57 | NULL         |               0 | RADIUS        |                   |                  |               0 |                0 |                 | 72.85.213.111    |                    | Framed-User | PPP            | 10.80.72.1      |              0 |             0 |                      |
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+————–+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
2 rows in set (0.00 sec)

What the radius database looks like after the client disconnects.

mysql> select * from radacct;
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+———————+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
| radacctid | acctsessionid  | acctuniqueid     | username        | groupname | realm | nasipaddress | nasportid | nasporttype | acctstarttime       | acctstoptime        | acctsessiontime | acctauthentic | connectinfo_start | connectinfo_stop | acctinputoctets | acctoutputoctets | calledstationid | callingstationid | acctterminatecause | servicetype | framedprotocol | framedipaddress | acctstartdelay | acctstopdelay | xascendsessionsvrkey |
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+———————+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
|         1 | 4F416D59154300 | 55f94b13a50e4069 | user@domain.com |           |       | 22.33.9.35  | 0         | Async       | 2012-02-19 22:44:57 | NULL                |               0 | RADIUS        |                   |                  |               0 |                0 |                 | 72.85.213.111    |                    | Framed-User | PPP            | 10.80.72.1      |              0 |             0 |                      |
|         2 | 4F416D59154301 | a2d784b0c15ca9a4 | user@domain.com |           |       | 22.33.9.35  | 0         | Async       | 2012-02-19 22:44:57 | 2012-02-19 22:47:51 |             174 | RADIUS        |                   |                  |            1120 |                0 |                 | 72.85.213.111    | User-Request       | Framed-User | PPP            | 10.80.72.1      |              0 |             0 |                      |
+———–+—————-+——————+—————–+———–+——-+————–+———–+————-+———————+———————+—————–+—————+——————-+——————+—————–+——————+—————–+——————+——————–+————-+—————-+—————–+—————-+—————+———————-+
2 rows in set (0.00 sec)

So as you might or might not be able to tell, is that for some reason the radiuscliet program actually created two entries when it is suppose to only make one. Then one of then entries the acctstoptime was properly updated. Then the problem is that the second entry the acctstoptime entry was left as NULL. So no if you try to log in again, you log in you might get access denied if you are only allowing one log in per user on your freeradius server.

So what the script below does is first looks for all the lines entries that have a NULL entry in order to not gather too much extra information. Once we know which users have a NULL value next to their name, the script will check to see if acctterminatecause = ‘User-Request’, because only clients that have logged out have User-Request in the acctterminatecause column of the radacct table.

If we do not have any entries returned, we will not continue (only 4 lines are returned at a minimum), but if we do have values returned we figure out what the acctstoptime is or the time that the client logged out, and add it to the other duplicate entry.

#script to figure out which pptp client has logged out and fix the entry.
# MySQL details
HOST=”localhost”
USER=”root”
DATABASE=”radius”
PASSWORD=”passowrd”
PWD=/root

#start by clearing out the staleSessions file
echo “” $PWD > staleSessions

#first figure out which rows have a null value that also has a acctstoptime in the table
mysql -u$USER -p$PASSWORD –database=$DATABASE -X   –execute=”select * from  radacct where acctstoptime is NULL” |grep acctstarttime| grep acctstarttime|cut -d “<” -f2|cut -d “>” -f2  |sort -u> staleSessions

#has to figure out if the user has logged out of the pptp session or not

cat $PWD/staleSessions  | while read ACCTSTARTTIME ; do

MYSQLLINES=`mysql -u$USER -p$PASSWORD –database=$DATABASE -X –execute=”select * from radacct where acctterminatecause = ‘User-Request’ and acctstarttime = ‘$ACCTSTARTTIME’”  | wc -l`
echo $MYSQLLINES

if [ “4” = “$MYSQLLINES” ]; then
echo only 4 lines present, not time yet
else

#Figures out what the stop time is
STOPTIME=`mysql -u$USER -p$PASSWORD –database=$DATABASE -X   –execute=”select * from  radacct where  acctterminatecause = ‘User-Request’ and acctstarttime =  ‘$ACCTSTARTTIME’” |grep acctstoptime|grep -v true|cut -d “<” -f2|cut -d “>” -f2   `

#writes the stop time to the NULL locaiton
mysql -u$USER -p$PASSWORD –database=$DATABASE -X   –execute=”UPDATE radacct SET acctstoptime = ‘$STOPTIME’ where acctstarttime = ‘$ACCTSTARTTIME’”

fi
done