1 files changed, 119 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl b/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl
new file mode 100755
index 0000000..c43da06
--- /dev/null
+++ b/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl
@@ -0,0 +1,119 @@
+#!/usr/bin/perl -Tw
+
+#
+#  main/sqlite/process-radacct-close-after_reload.pl -- Script for
+#    processing radacct entries to close sessions interrupted by a NAS reload
+#
+#  Requires the DBD::SQLite module: perl-DBD-SQLite (RedHat); libdbd-sqlite3-perl (Debian)
+#
+#  $Id$
+#
+#  It may be desirable to periodically "close" radacct sessions belonging to a
+#  reloaded NAS, replicating the "bulk close" Accounting-On/Off behaviour,
+#  just not in real time.
+#
+#  This script will set radacct.acctstoptime to nasreload.reloadtime, calculate
+#  the corresponding radacct.acctsessiontime, and set acctterminatecause to
+#  "NAS reboot" for interrupted sessions. It does so in batches, which avoids a
+#  single long-lived lock on the table.
+#
+#  It can be invoked as follows:
+#
+#      ./process-radacct-close-after-reload.pl <sqlite_db_file>
+#
+#  Note: This script walks radacct in strides of v_batch_size. It will
+#  typically skip closed and ongoing sessions at a rate significantly faster
+#  than 10,000 rows per second and process batched updates faster than 5000
+#  orphaned sessions per second. If this isn't fast enough then you should
+#  really consider using a server-based database for accounting purposes.
+#
+
+use strict;
+use DBI;
+
+#
+#  Fine for most purposes
+#
+my $batch_size = 2500;
+
+if ($#ARGV != 0) {
+    print "Usage: process-radacct-close-after_reload.pl SQLITE_DB_FILE\n\n";
+    exit 1;
+}
+die "The SQLite database must exist: $ARGV[0]" unless -r $ARGV[0];
+
+
+my $dbh = DBI->connect("DBI:SQLite:dbname=$ARGV[0]", '', '', { RaiseError => 1 }) or die $DBI::errstr;
+
+#
+#  There is no UPDATE ... JOIN/FROM in SQLite, so we have to resort to this
+#  construction #  which does not provide an accurate rows updated count...
+#
+my $sth_upd = $dbh->prepare(<<'EOF');
+    UPDATE radacct
+    SET
+        acctstoptime = (
+            SELECT COALESCE(acctstoptime, CASE WHEN radacct.acctstarttime < reloadtime THEN reloadtime END)
+            FROM nasreload WHERE nasipaddress = radacct.nasipaddress
+        ),
+        acctsessiontime = (
+            SELECT COALESCE(acctsessiontime,
+                CASE WHEN radacct.acctstoptime IS NULL AND radacct.acctstarttime < reloadtime THEN
+                   CAST((julianday(reloadtime) - julianday(radacct.acctstarttime)) * 86400 AS integer)
+                END)
+            FROM nasreload WHERE nasipaddress = radacct.nasipaddress
+        ),
+        acctterminatecause = (
+            SELECT
+                CASE WHEN radacct.acctstoptime IS NULL AND radacct.acctstarttime < reloadtime THEN
+                    'NAS reboot'
+                ELSE
+                    acctterminatecause
+                END
+            FROM nasreload WHERE nasipaddress = radacct.nasipaddress
+        )
+    WHERE
+        radacctid BETWEEN ? AND ?
+        AND acctstoptime IS NULL
+EOF
+
+my $sth = $dbh->prepare('SELECT MIN(radacctid), MAX(radacctid) FROM radacct WHERE acctstoptime IS NULL');
+$sth->execute() or die $DBI::errstr;
+(my $a, my $m) = $sth->fetchrow_array();
+$sth->finish;
+
+my $sth_nxt = $dbh->prepare('SELECT radacctid FROM radacct WHERE radacctid > ? ORDER BY radacctid LIMIT ?,1');
+
+
+my $last = 0;
+my $last_report = 0;
+
+unless ($last) {
+
+    $sth_nxt->execute($a, $batch_size) or die $DBI::errstr;
+    (my $z) = $sth_nxt->fetchrow_array();
+
+    unless ($z) {
+        $z = $m;
+        $last = 1;
+    }
+
+    my $rc = $sth_upd->execute($a, $z) or die $DBI::errstr;
+
+    $a = $z + 1;
+
+    #
+    #  Periodically report how far we've got
+    #
+    my $now = time();
+    if ($last_report != $now || $last) {
+        print "RadAcctID: $z\n";
+        $last_report = $now;
+    }
+
+}
+
+$sth_upd->finish;
+$sth_nxt->finish;
+
+$dbh->disconnect;