Hello, We are experiancing many issues with dialup usage since we switched from cistron radius to freeradius, call detail is correct however the billed usage is WAY off, a user with a total calculated time in call detail of say 50 hours, will show up as like 900 hours in USAGE. Can anyone tell me what the deal is and how I can fix it ? Is there a different sql statment I need to use ? If so can anyone send me what they are using with the same setup ... Thanks Ryan
Your usage SQL query will most likely be different if you are using a different database structure for storing the usage.
Database structure is the same for the most part, except 2 fields, of which I've compinstated for. however I cannot get correct usage reports. Thanks Ryan
Do you have a sample of what the SQL statement you are running returns (if you run it outside of Optigold)?
the current statment is SELECT username,count(username),sum(acctsessiontime),(sum(acctoutputoctets)/1000000) FROM radacct WHERE acctstarttime > '<Y1>-<M1>-<D1>' AND acctstoptime <= '<Y2>-<M2>-<D2>' GROUP BY username; Thanks Ryan
username acctsessiontime ( sum( acctoutputoctets ) /1000000 ) aachiro 4286 101.51 aagesen 2678 77.40 abartlett 2001 65.85 abc123 4017 78.56 ablandscapesunl 1708 205.23 abrenzelman 2272 23.78 abschauer 1711 70.94 acdanhl 62 0.10 ada 231 45.60 adambriggs 2169 14.60 adan 1258 153.73 adeleray 742 152.73 aekcjk3@plains.net 129 31.89 aeloise 63 36.93 ahbm 2416 6.60 ahickman 86 54.14 ahoward 681 112.66 ahyzfr6 4212 36.60 ajcr62 1740 15.77 ajhowell@plains.net 1716 9.46 akelly 1205 24.55 akennison 2715 20.42 akjwoller 815 21.57 just a top sample of the DB, it looks coorect via this method however once in optigold it's not correct, I have users that averaged 15 hours over the last 6 months now are coming up with like 7000 hours.
I think it's missing a column... the column after username should be sessions, it looks like it's the time online instead.
It looks right, but wouldn't you agree the count(username) portion isn't being returned (not even an empty column)?
ack hold up let me redo this.. my origional copy of the query was incorrect. here is the full query.. username count( username ) sum( acctsessiontime ) ( sum( acctoutputoctets ) /1000000 ) aachiro 78 158928 101.51 aagesen 27 49188 77.40 abartlett 13 36086 65.85 abc123 516 604197 78.56 ablandscapesunl 68 79555 205.23 abrenzelman 3 13542 23.78 abschauer 46 59222 70.94 acdanhl 1 62 0.10 ada 47 51839 45.60 adambriggs 9 11777 14.60 an example the DB shows brahm 60 1443707 130.07 Optigold shows time online : 802:53:00 its double of what radius says. as when calculated its only 401 hours.
Well that looks like a problem with the SQL query then... or the data in the SQL database. The 2nd column should be the time online. 1443707 seconds works out to more than 400 hours, so...
thats 401.02972222222222222222222222222 hours calculated and verified by 8 people. and thats the correct usage for that user, so why does optigold show 802 hours ? with the SAME query! Ryan
Not sure... if you ran it twice though and have the option to merge same month usage data, you would get that figure since it's exactly double.
Hmmm... have you checked the SQL query results to make sure there is only one login for the user in the results?