On Thu, 7 Apr 2022 at 16:44, Max James <xxedlxx101@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@deployingradius.com> wrote:
On Apr 4, 2022, at 12:33 AM, Max James <xxedlxx101@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-confi...
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.