Menu ▾ ▴

MonthEndCleanup

Anonymous

Month end cleanup

Back story

It seems out of everyone using YFi, I seem to be the only one having issues with the radius accounting records rolling over from one month to the next. Or at least, I'm the only one noticing it.
Dirk has been a huge help, sorting out a lot of the issues to such a point where I only had one client's session going over from one month to the next this last month. Despite all that I am still required to make sure this doesn't happen again as it was wreaking havoc with usage counting and our clients was not very happy about it.

So here is a script that will automate the process. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
#!/bin/bash
## This script attempts to catch and correct any records YFi may have missed when doing the month end cut off

## Check and log if any sessions from last month is still active
mysql -pPASSWORD yfi -e "select username, acctstarttime, acctstoptime from radacct where acctstarttime like '`date -d'yesterday' +'%Y-%m-'`%' and acctstoptime is NULL;" | tee /tmp/yfi_monthend_`date +'%Y-%m-%d'`.log1

## Check and log if any sessions from last month ended in the new month
mysql -pPASSWORD yfi -e "select username, acctstarttime, acctstoptime from radacct where acctstarttime like '`date -d'yesterday' +'%Y-%m-'`%' and acctstoptime like '`date +'%Y-%m-'`%';" | tee /tmp/yfi_monthend_`date +'%Y-%m-%d'`.log2

## This shifts the sessions that went over into the new month back to the previous month.
mysql -pPASSWORD yfi -e "UPDATE radacct set acctstoptime='`date -d'yesterday' +'%Y-%m-%d'` 23:59:59' WHERE acctstarttime LIKE '`date -d'yesterday' +'%Y-%m-'`%' AND acctstoptime LIKE '`date +'%Y-%m-'`%';"
mysql -pPASSWORD yfi -e "UPDATE radacct set acctstoptime='`date -d'yesterday' +'%Y-%m-%d'` 23:59:59' WHERE acctstarttime LIKE '`date -d'yesterday' +'%Y-%m-'`%' AND acctstoptime IS NULL;"

You can also add this to cron to run on the first of every month: 

* * 1 * * /path/to/scripts/check_monthend.sh >/dev/null 2>&1

Related

Wiki: Home