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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
# -*- text -*-
#
# main/oracle/process-radacct.sql -- Schema extensions for processing radacct entries
#
# $Id$
-- ---------------------------------
-- - Per-user data usage over time -
-- ---------------------------------
--
-- An extension to the standard schema to hold per-user data usage statistics
-- for arbitrary periods.
--
-- The data_usage_by_period table is populated by periodically calling the
-- fr_new_data_usage_period stored procedure.
--
-- This table can be queried in various ways to produce reports of aggregate
-- data use over time. For example, if the fr_new_data_usage_period SP is
-- invoked one per day just after midnight, to produce usage data with daily
-- granularity, then a reasonably accurate monthly bandwidth summary for a
-- given user could be obtained with:
--
-- SELECT
-- MIN(TO_CHAR(period_start, 'YYYY-Month')) AS month,
-- SUM(acctinputoctets)/1000/1000/1000 AS GB_in,
-- SUM(acctoutputoctets)/1000/1000/1000 AS GB_out
-- FROM
-- data_usage_by_period
-- WHERE
-- username='bob' AND
-- period_end IS NOT NULL
-- GROUP BY
-- TRUNC(period_start,'month');
--
-- +----------------+----------------+-----------------+
-- | MONTH | GB_IN | GB_OUT |
-- +----------------+----------------+-----------------+
-- | 2019-July | 5.782279230000 | 50.545664820000 |
-- | 2019-August | 4.230543340000 | 48.523096420000 |
-- | 2019-September | 4.847360590000 | 48.631835480000 |
-- | 2019-October | 6.456763250000 | 51.686231930000 |
-- | 2019-November | 6.362537730000 | 52.385710570000 |
-- | 2019-December | 4.301524440000 | 50.762240270000 |
-- | 2020-January | 5.436280540000 | 49.067775280000 |
-- +----------------+----------------+-----------------+
--
CREATE TABLE data_usage_by_period (
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
username VARCHAR(64) NOT NULL,
period_start TIMESTAMP WITH TIME ZONE NOT NULL,
period_end TIMESTAMP WITH TIME ZONE,
acctinputoctets NUMERIC(19),
acctoutputoctets NUMERIC(19),
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_data_usage_by_period_username_period_start ON data_usage_by_period (username,period_start);
CREATE INDEX idx_data_usage_by_period_period_start ON data_usage_by_period (period_start);
CREATE INDEX idx_data_usage_by_period_period_end ON data_usage_by_period (period_end);
--
-- Stored procedure that when run with some arbitrary frequency, say
-- once per day by cron, will process the recent radacct entries to extract
-- time-windowed data containing acct{input,output}octets ("data usage") per
-- username, per period.
--
-- Each invocation will create new rows in the data_usage_by_period tables
-- containing the data used by each user since the procedure was last invoked.
-- The intervals do not need to be identical but care should be taken to
-- ensure that the start/end of each period aligns well with any intended
-- reporting intervals.
--
-- It can be invoked by running:
--
-- CALL fr_new_data_usage_period();
--
--
CREATE OR REPLACE PROCEDURE fr_new_data_usage_period
AS
v_start TIMESTAMP WITH TIME ZONE;
v_end TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT COALESCE(MAX(period_end) + NUMTODSINTERVAL(1,'SECOND'), TO_DATE('1970-01-01','YYYY-MM-DD')) INTO v_start FROM data_usage_by_period;
SELECT CAST(CURRENT_TIMESTAMP AS DATE) INTO v_end FROM dual;
BEGIN
--
-- 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.
--
MERGE INTO data_usage_by_period d
USING (
SELECT
username,
MIN(v_start) period_start,
MIN(v_end) period_end,
SUM(acctinputoctets) AS acctinputoctets,
SUM(acctoutputoctets) AS acctoutputoctets
FROM
radacct
WHERE
acctstoptime > v_start OR
acctstoptime IS NULL
GROUP BY
username
) s
ON ( d.username = s.username AND d.period_start = s.period_start )
WHEN MATCHED THEN
UPDATE SET
acctinputoctets = d.acctinputoctets + s.acctinputoctets,
acctoutputoctets = d.acctoutputoctets + s.acctoutputoctets,
period_end = v_end
WHEN NOT MATCHED THEN
INSERT
(username, period_start, period_end, acctinputoctets, acctoutputoctets)
VALUES
(s.username, s.period_start, s.period_end, s.acctinputoctets, s.acctoutputoctets);
--
-- 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 *
FROM (
SELECT
username,
v_end + NUMTODSINTERVAL(1,'SECOND'),
NULL,
0 - SUM(acctinputoctets),
0 - SUM(acctoutputoctets)
FROM
radacct
WHERE
acctstoptime IS NULL
GROUP BY
username
) s;
END;
END;
/
|
