Periodic Data Usage Reporting
Terry Burton
tez at terryburton.co.uk
Mon Apr 11 00:36:26 UTC 2022
On Thu, 7 Apr 2022 at 16:44, Max James <xxedlxx101 at gmail.com> wrote:
> > Yes, it sets the total usage time for all closed sessions, and adds in
> the time for the current session.
>
> If a session ends, upon the script's next execution it includes all the
> data consumed throughout the lifetime of that session. The expected result
> is for only the data consumed since the last script's execution should be
> inserted into the "data_usage_by_period" table.
Please demonstrate the problem rather than leave it to others to
attempt to reproduce it.
The fr_new_data_usage_period() SP appears to be behaving correctly...
--
-- New session for "a"
--
INSERT INTO radacct (acctsessionid, acctuniqueid, nasipaddress,
username, acctinputoctets, acctoutputoctets, acctstarttime) VALUES
('1', '1', '1.1.1.1', 'a', 100, 0, NOW());
SELECT fr_new_data_usage_period();
SELECT * from data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | |
-100 | 0
select pg_sleep(1);
--
-- More data for "a"
-- New session for "b"
--
UPDATE radacct SET acctinputoctets = 150 WHERE acctuniqueid = '1';
INSERT INTO radacct (acctsessionid, acctuniqueid, nasipaddress,
username, acctinputoctets, acctoutputoctets, acctstarttime) VALUES
('2', '2', '2.2.2.2', 'b', 1000, 0, NOW());
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:01+00 | |
-150 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | |
-1000 | 0
select pg_sleep(1);
--
-- Yet more data
--
UPDATE radacct SET acctinputoctets = 175 WHERE acctuniqueid = '1';
UPDATE radacct SET acctinputoctets = 1750 WHERE acctuniqueid = '2';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- a | 2022-04-11 00:06:22+00 | |
-175 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | |
-1750 | 0
select pg_sleep(1);
--
-- Close session "a"
-- More data for "b"
--
UPDATE radacct SET acctinputoctets = 180, acctstoptime=NOW() WHERE
acctuniqueid = '1';
UPDATE radacct SET acctinputoctets = 1800 WHERE acctuniqueid = '2';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- a | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
5 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
50 | 0
-- b | 2022-04-11 00:06:39+00 | |
-1800 | 0
select pg_sleep(1);
--
-- Close session "b"
--
UPDATE radacct SET acctinputoctets = 2000, acctstoptime=NOW() WHERE
acctuniqueid = '2';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- a | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
5 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
50 | 0
-- b | 2022-04-11 00:06:39+00 | 2022-04-11 00:06:50+00 |
200 | 0
select pg_sleep(1);
--
-- New session for "a"
--
INSERT INTO radacct (acctsessionid, acctuniqueid, nasipaddress,
username, acctinputoctets, acctoutputoctets, acctstarttime) VALUES
('11', '11', '11.11.11.11', 'a', 123, 0, NOW());
SELECT fr_new_data_usage_period();
SELECT * from data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- a | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
5 | 0
-- a | 2022-04-11 00:06:51+00 | 2022-04-11 00:13:55+00 |
123 | 0
-- a | 2022-04-11 00:13:56+00 | |
-123 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
50 | 0
-- b | 2022-04-11 00:06:39+00 | 2022-04-11 00:06:50+00 |
200 | 0
select pg_sleep(1);
--
-- More data for "a"
-- New session for "b"
--
UPDATE radacct SET acctinputoctets = 234 WHERE acctuniqueid = '11';
INSERT INTO radacct (acctsessionid, acctuniqueid, nasipaddress,
username, acctinputoctets, acctoutputoctets, acctstarttime) VALUES
('22', '22', '22.22.22.22', 'b', 1234, 0, NOW());
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- a | 2022-04-11 00:06:51+00 | 2022-04-11 00:13:55+00 |
123 | 0
-- a | 2022-04-11 00:13:56+00 | 2022-04-11 00:15:33+00 |
111 | 0
-- a | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
5 | 0
-- a | 2022-04-11 00:15:34+00 | |
-234 | 0
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- b | 2022-04-11 00:15:34+00 | |
-1234 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
50 | 0
-- b | 2022-04-11 00:06:39+00 | 2022-04-11 00:06:50+00 |
200 | 0
-- b | 2022-04-11 00:13:56+00 | 2022-04-11 00:15:33+00 |
1234 | 0
select pg_sleep(1);
--
-- Yet more data
--
UPDATE radacct SET acctinputoctets = 555 WHERE acctuniqueid = '11';
UPDATE radacct SET acctinputoctets = 5555 WHERE acctuniqueid = '22';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 2022-04-11 00:06:51+00 | 2022-04-11 00:13:55+00 |
123 | 0
-- a | 2022-04-11 00:13:56+00 | 2022-04-11 00:15:33+00 |
111 | 0
-- a | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
5 | 0
-- a | 2022-04-11 00:15:34+00 | 2022-04-11 00:17:31+00 |
321 | 0
-- a | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
25 | 0
-- a | 2022-04-11 00:17:32+00 | |
-555 | 0
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:05:46+00 |
100 | 0
-- a | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
50 | 0
-- b | 2022-04-11 00:17:32+00 | |
-5555 | 0
-- b | 2022-04-11 00:05:47+00 | 2022-04-11 00:06:00+00 |
1000 | 0
-- b | 2022-04-11 00:06:01+00 | 2022-04-11 00:06:21+00 |
750 | 0
-- b | 2022-04-11 00:06:22+00 | 2022-04-11 00:06:38+00 |
50 | 0
-- b | 2022-04-11 00:06:39+00 | 2022-04-11 00:06:50+00 |
200 | 0
-- b | 2022-04-11 00:13:56+00 | 2022-04-11 00:15:33+00 |
1234 | 0
-- b | 2022-04-11 00:15:34+00 | 2022-04-11 00:17:31+00 |
4321 | 0
select pg_sleep(1);
--
-- Close session "a"
-- More data for "b"
--
UPDATE radacct SET acctinputoctets = 777, acctstoptime=NOW() WHERE
acctuniqueid = '11';
UPDATE radacct SET acctinputoctets = 7777 WHERE acctuniqueid = '22';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:31:17+00 |
100 | 0
-- a | 2022-04-11 00:31:18+00 | 2022-04-11 00:31:18+00 |
50 | 0
-- a | 2022-04-11 00:31:19+00 | 2022-04-11 00:31:19+00 |
25 | 0
-- a | 2022-04-11 00:31:20+00 | 2022-04-11 00:31:20+00 |
5 | 0
-- a | 2022-04-11 00:31:22+00 | 2022-04-11 00:31:22+00 |
123 | 0
-- a | 2022-04-11 00:31:23+00 | 2022-04-11 00:31:23+00 |
111 | 0
-- a | 2022-04-11 00:31:24+00 | 2022-04-11 00:31:24+00 |
321 | 0
-- a | 2022-04-11 00:31:25+00 | 2022-04-11 00:31:25+00 |
222 | 0
-- b | 2022-04-11 00:31:18+00 | 2022-04-11 00:31:18+00 |
1000 | 0
-- b | 2022-04-11 00:31:19+00 | 2022-04-11 00:31:19+00 |
750 | 0
-- b | 2022-04-11 00:31:20+00 | 2022-04-11 00:31:20+00 |
50 | 0
-- b | 2022-04-11 00:31:21+00 | 2022-04-11 00:31:21+00 |
200 | 0
-- b | 2022-04-11 00:31:23+00 | 2022-04-11 00:31:23+00 |
1234 | 0
-- b | 2022-04-11 00:31:24+00 | 2022-04-11 00:31:24+00 |
4321 | 0
-- b | 2022-04-11 00:31:25+00 | 2022-04-11 00:31:25+00 |
2222 | 0
-- b | 2022-04-11 00:31:26+00 | |
-7777 | 0
select pg_sleep(1);
--
-- Close session "b"
--
UPDATE radacct SET acctinputoctets = 10000, acctstoptime=NOW() WHERE
acctuniqueid = '22';
SELECT fr_new_data_usage_period();
SELECT * FROM data_usage_by_period ORDER BY username, period_start;
-- username | period_start | period_end |
acctinputoctets | acctoutputoctets
-- ----------+------------------------+------------------------+-----------------+------------------
-- a | 1970-01-01 00:00:00+00 | 2022-04-11 00:31:17+00 |
100 | 0
-- a | 2022-04-11 00:31:18+00 | 2022-04-11 00:31:18+00 |
50 | 0
-- a | 2022-04-11 00:31:19+00 | 2022-04-11 00:31:19+00 |
25 | 0
-- a | 2022-04-11 00:31:20+00 | 2022-04-11 00:31:20+00 |
5 | 0
-- a | 2022-04-11 00:31:22+00 | 2022-04-11 00:31:22+00 |
123 | 0
-- a | 2022-04-11 00:31:23+00 | 2022-04-11 00:31:23+00 |
111 | 0
-- a | 2022-04-11 00:31:24+00 | 2022-04-11 00:31:24+00 |
321 | 0
-- a | 2022-04-11 00:31:25+00 | 2022-04-11 00:31:25+00 |
222 | 0
-- b | 2022-04-11 00:31:18+00 | 2022-04-11 00:31:18+00 |
1000 | 0
-- b | 2022-04-11 00:31:19+00 | 2022-04-11 00:31:19+00 |
750 | 0
-- b | 2022-04-11 00:31:20+00 | 2022-04-11 00:31:20+00 |
50 | 0
-- b | 2022-04-11 00:31:21+00 | 2022-04-11 00:31:21+00 |
200 | 0
-- b | 2022-04-11 00:31:23+00 | 2022-04-11 00:31:23+00 |
1234 | 0
-- b | 2022-04-11 00:31:24+00 | 2022-04-11 00:31:24+00 |
4321 | 0
-- b | 2022-04-11 00:31:25+00 | 2022-04-11 00:31:25+00 |
2222 | 0
-- b | 2022-04-11 00:31:26+00 | 2022-04-11 00:31:26+00 |
2223 | 0
> On Thu, Apr 7, 2022 at 9:33 PM Alan DeKok <aland at deployingradius.com> wrote:
>
> > On Apr 4, 2022, at 12:33 AM, Max James <xxedlxx101 at gmail.com> wrote:
> > > I have followed the below "Periodic Data Usage Reporting" article found
> > on
> > > the freeradius wiki.
> > > https://wiki.freeradius.org/guide/Data-Usage-Reporting
> > >
> > > I am using the postgresql process-radacct.sql script which can be found
> > on
> > > the freeradius-server github.
> > >
> > https://github.com/FreeRADIUS/freeradius-server/blob/master/raddb/mods-config/sql/main/postgresql/process-radacct.sql
> >
> > That's from the "master" branch, but I suppose it should work for v3,
> > too.
> >
> > > The implementation was successful and works as expected, however I did
> > > notice an issue that I can't seem to figure out myself.
> > >
> > > If a users session ends and a new one begins, the next time the
> > > fr_new_data_usage_period() function is executed, it seems to be inserting
> > > the data usage for that entire session instead of only the data usage
> > that
> > > was accrued since the last time the function was executed.
> >
> > Yes, it sets the total usage time for all closed sessions, and adds in
> > the time for the current session.
> >
> > > The function reports usage as expected until it comes across users that
> > > have a new session since the function was last executed. I've spent a few
> > > hours trying to figure out the cause but I've had no luck. Any advice or
> > > help is much appreciated. Thanks in advance.
> >
> > I'm not sure what's wrong here. The table is updated with the total
> > value of session time. That's what it's supposed to do?
> >
> > Alan DeKok.
More information about the Freeradius-Devel
mailing list