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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
# -*- text -*-
#
# main/mssql/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
-- FORMAT(period_start, 'yyyy-MMMM') 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 <> 0
-- GROUP BY
-- FORMAT(period_start, 'yyyy-MMMM');
--
-- +----------------+----------+-----------+
-- | month | GB_in | GB_out |
-- +----------------+----------+-----------+
-- | 2019-July | 5.782279 | 50.545664 |
-- | 2019-August | 4.230543 | 48.523096 |
-- | 2019-September | 4.847360 | 48.631835 |
-- | 2019-October | 6.456763 | 51.686231 |
-- | 2019-November | 6.362537 | 52.385710 |
-- | 2019-December | 4.301524 | 50.762240 |
-- | 2020-January | 5.436280 | 49.067775 |
-- +----------------+----------+-----------+
--
CREATE TABLE data_usage_by_period (
username VARCHAR(64) NOT NULL,
period_start DATETIME NOT NULL,
period_end DATETIME NOT NULL,
acctinputoctets NUMERIC(19),
acctoutputoctets NUMERIC(19),
PRIMARY KEY (username, period_start)
);
GO
CREATE INDEX idx_data_usage_by_period_period_end ON data_usage_by_period(period_end);
GO
--
-- 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:
--
-- EXEC fr_new_data_usage_period;
--
--
CREATE OR ALTER PROCEDURE fr_new_data_usage_period
AS
BEGIN
DECLARE @v_start DATETIME;
DECLARE @v_end DATETIME;
SELECT @v_start = COALESCE(DATEADD(ss, 1, MAX(period_end)), CAST('1970-01-01' AS DATETIME)) FROM data_usage_by_period;
SELECT @v_end = CAST(CURRENT_TIMESTAMP AS DATETIME2(0));
BEGIN TRAN;
--
-- 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,
@v_start AS period_start,
@v_end AS period_end,
SUM(acctinputoctets) AS acctinputoctets,
SUM(acctoutputoctets) AS acctoutputoctets
FROM ((
SELECT
username, acctinputoctets, acctoutputoctets
FROM
radacct
WHERE
acctstoptime > @v_start
) UNION ALL (
SELECT
username, acctinputoctets, acctoutputoctets
FROM
radacct
WHERE
acctstoptime=0
)) a
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.
--
-- MSSQL doesn't allow a DATETIME to be NULL so we use "0" (1900-01-01) to
-- indicate the open-ended interval.
--
INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets)
SELECT *
FROM (
SELECT
username,
DATEADD(ss,1,@v_end) AS period_start,
0 AS period_end,
0 - SUM(acctinputoctets) AS acctinputoctets,
0 - SUM(acctoutputoctets) AS acctoutputoctets
FROM
radacct
WHERE
acctstoptime=0
GROUP BY
username
) s;
COMMIT;
END
GO
|
