1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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;
|
