diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-28 09:49:46 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-28 09:49:46 +0000 |
commit | 50b37d4a27d3295a29afca2286f1a5a086142cec (patch) | |
tree | 9212f763934ee090ef72d823f559f52ce387f268 /raddb/mods-config/sql/main/mssql/process-radacct.sql | |
parent | Initial commit. (diff) | |
download | freeradius-50b37d4a27d3295a29afca2286f1a5a086142cec.tar.xz freeradius-50b37d4a27d3295a29afca2286f1a5a086142cec.zip |
Adding upstream version 3.2.1+dfsg.upstream/3.2.1+dfsgupstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'raddb/mods-config/sql/main/mssql/process-radacct.sql')
-rw-r--r-- | raddb/mods-config/sql/main/mssql/process-radacct.sql | 151 |
1 files changed, 151 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/mssql/process-radacct.sql b/raddb/mods-config/sql/main/mssql/process-radacct.sql new file mode 100644 index 0000000..a3a6445 --- /dev/null +++ b/raddb/mods-config/sql/main/mssql/process-radacct.sql @@ -0,0 +1,151 @@ +# -*- 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 + radacct + WHERE + acctstoptime > @v_start OR + acctstoptime=0 + 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 |