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
120
121
122
123
124
|
#!/bin/sh
#
# main/sqlite/process-radacct-new-data-usage-period.sh -- Script for
# processing radacct entries to extract daily usage
#
# $Id$
#
# See process-radacct-schema.sql for details.
#
if [ "$#" -ne 1 ]; then
echo "Usage: process-radacct-new-data-usage-period.sh SQLITE_DB_FILE" 2>&1
exit 1
fi
if [ ! -r "$1" ]; then
echo "The SQLite database must exist: $1" 1>&2
exit 1
fi
cat <<EOF | sqlite3 "$1"
--
-- SQLite doesn't have a concept of session variables so we fake it.
--
DROP TABLE IF EXISTS vars;
CREATE TEMPORARY TABLE vars (
key text,
value text,
PRIMARY KEY (key)
);
INSERT INTO vars SELECT 'v_start', COALESCE(DATETIME(MAX(period_end), '+1 seconds'), DATETIME(0, 'unixepoch')) FROM data_usage_by_period;
INSERT INTO vars SELECT 'v_end', CURRENT_TIMESTAMP;
--
-- Make of copy of the sessions that were active during this period to
-- avoid having to execute a potentially long transaction that might hold a
-- global database lock.
--
DROP TABLE IF EXISTS radacct_sessions;
CREATE TEMPORARY TABLE radacct_sessions (
username text,
acctstarttime datetime,
acctstoptime datetime,
acctinputoctets bigint,
acctoutputoctets bigint
);
CREATE INDEX temp.idx_radacct_sessions_username ON radacct_sessions(username);
CREATE INDEX temp.idx_radacct_sessions_acctstoptime ON radacct_sessions(acctstoptime);
INSERT INTO radacct_sessions
SELECT
username,
acctstarttime,
acctstoptime,
acctinputoctets,
acctoutputoctets
FROM
radacct
WHERE
acctstoptime > (SELECT value FROM vars WHERE key='v_start');
INSERT INTO radacct_sessions
SELECT
username,
acctstarttime,
acctstoptime,
acctinputoctets,
acctoutputoctets
FROM
radacct
WHERE
acctstoptime IS NULL;
--
-- Add the data usage for the sessions that were active in the current
-- period to the table. Include all sessions that finished since the start
-- of this period as well as those still ongoing.
--
INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets)
SELECT
username,
(SELECT value FROM vars WHERE key='v_start'),
(SELECT value FROM vars WHERE key='v_end'),
SUM(acctinputoctets) AS acctinputoctets,
SUM(acctoutputoctets) AS acctoutputoctets
FROM
radacct_sessions
GROUP BY
username
ON CONFLICT(username,period_start) DO UPDATE
SET
acctinputoctets = data_usage_by_period.acctinputoctets + EXCLUDED.acctinputoctets,
acctoutputoctets = data_usage_by_period.acctoutputoctets + EXCLUDED.acctoutputoctets,
period_end = (SELECT value FROM vars WHERE key='v_end');
--
-- Create an open-ended "next period" for all ongoing sessions and carry a
-- negative value of their data usage to avoid double-accounting when we
-- process the next period. Their current data usage has already been
-- allocated to the current and possibly previous periods.
--
INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets)
SELECT
username,
(SELECT DATETIME(value, '+1 seconds') FROM vars WHERE key='v_end'),
NULL,
0 - SUM(acctinputoctets),
0 - SUM(acctoutputoctets)
FROM
radacct_sessions
WHERE
acctstoptime IS NULL
GROUP BY
username;
DROP TABLE vars;
DROP TABLE radacct_sessions;
EOF
|
