OptiGold, Radiator, & MySQL

[nanoburn]

We have a new install of OptiGold which we have setup using the SQL events that Shawn was nice enough to send us, but we have ran into a weird problem. We have a basic Linux RPM installation of Radiator that we have running with the default sql config file. We also used the default mysqlcreate.sql file to create our Radius database. Radiator will authenticate the test user no problem at all.

My problem comes in that the SQL events I was given refer to a table named RADUSERS, and all searches through the forums and lists refer to this same RADUSERS table. This table doesn't exist with the default Radiator/MySQL installation. Instead I have a table called SUBSCRIBERS, but it has different fields.

Is this due to a change in Radiator, and how do I work around it?

[digitalpoint]

If the table structure is the same, you could simply change the name of the table in the SQL statements...

[nanoburn]

The SUBSCIBERS table has the following fields:

USERNAME
PASSWORD
ENCRYPTEDPASSWORD
CHECKATTR
REPLYATTR
TIMELEFT

The RADUSERS table should have the following:

ADDEDDATE
BADLOGINS
EMAIL
FULLNAME
MAXLOGINS
PASS_WORD
USERNAME
VALIDFROM
VALIDTO

These aren't even close. I can't even find the RADUSERS structure anywhere in any of the sql setup files or in any of the tables that I've seen with Radiator. I assumed these would be compatible programs since I purchased them together through DP. Am I wrong?

[digitalpoint]

What's the SQL statement you are trying to use that references RADUSERS? It does sound like maybe they changed their default table structures.

[nanoburn]

Here is their Default MySQL DB Structure:

ACCOUNTING
USERNAME
TIME_STAMP
ACCTSTATUSTYPE
ACCTDELAYTIME
ACCTINPUTOCTETS
ACCTOUTPUTOCTETS
ACCTSESSIONID
ACCTSESSIONTIME
ACCTTERMINATECAUSE
NASIDENTIFIER
NASPORT
FRAMEDIPADDRESS

RADAUTHLOG
TIME_STAMP
USERNAME
TYPE
REASON

RADCLIENTLIST
NASIDENTIFIER
SECRET
IGNOREACCTSIGNATURE
DUPINTERVAL
DEFAULTREALM
NASTYPE
SNMPCOMMUNITY
LIVINGSTONOFFS
LIVINGSTONHOLE
FRAMEDGROUPBASEADDRESS
FRAMEDGROUPMAXPORTSPERCLASSC
REWRITEUSERNAME
NOIGNOREDUPLICATES
PREHANDLERHOOK

RADLASTAUTH
USERNAME
NASIDENTIFIER
NASPORT
ACCTSESSIONID
TIME_STAMP

RADLOG
TIME_STAMP
PRIORITY
MESSAGE

RADONLINE
USERNAME
NASIDENTIFIER
NASPORT
ACCTSESSIONID
TIME_STAMP
FRAMEDIPADDRESS
NASPORTTYPE
SERVICETYPE

RADPOOL
STATE
TIME_STAMP
EXPIRY
USERNAME
POOL
YIADDR
SUBNETMASK
DNSSERVER

RADSQLRADIUS
TARGETNAME
HOST1
HOST2
SECRET
AUTHPORT
ACCTPORT
RETRIES
RETRYTIMEOUT
USEOLDASCENDPASSWORDS
SERVERHASBROKENPORTNUMBERS
SERVERHASBROKENADDRESSES
IGNOREREPLYSIGNATURE
FAILUREPOLICY

RADSQLRADIUSINDIRECT
SOURCENAME
TARGETNAME

RADSTATSLOG
TIME_STAMP
TYPE
IDENTIFIER
ACCESSACCEPTS
ACCESSCHALLENGES
ACCESSREJECTS
ACCESSREQUESTS
ACCOUNTINGREQUESTS
ACCOUNTINGRESPONSES
BADAUTHACCESSREQUESTS
BADAUTHACCOUNTINGREQUESTS
BADAUTHREQUESTS
DROPPEDACCESSREQUESTS
DROPPEDACCOUNTINGREQUESTS
DROPPEDREQUESTS
DUPACCESSREQUESTS
DUPACCOUNTINGREQUESTS
DUPLICATEREQUESTS
MALFORMEDACCESSREQUESTS
MALFORMEDACCOUNTINGREQUESTS
PROXIEDNOREPLY
PROXIEDREQUESTS
REQUESTS
RESPONSETIME

SUBSCRIBERS
USERNAME
PASSWORD
ENCRYPTEDPASSWORD
CHECKATTR
REPLYATTR
TIMELEFT

The SQL Events I'm trying to use (the ones you gave me) are as follows:

Monthly Usage:

SELECT username,count(username)/2,sum(acctsessiontime),sum
(acctoutputoctets)/1024/1024 FROM RADUSAGE WHERE time_stamp >=
UNIX_TIMESTAMP('<Y1>-<M1>-<D1> 00:00:00') AND time_stamp <=
UNIX_TIMESTAMP('<Y2>-<M2>-<D2> 00:00:00') GROUP BY username

View History:

SELECT FROM_UNIXTIME(time_stamp),'',sum(acctsessiontime),sum
(acctoutputoctets),framedipaddress,'',nasport,acctsessionid FROM
RADUSAGE WHERE username = '<Login>' GROUP BY acctsessionid ORDER BY
time_stamp desc limit 100

Create Customer:

INSERT INTO RADUSERS
(ADDEDDATE, BADLOGINS, EMAIL, FULLNAME, MAXLOGINS, PASS_WORD, USERNAME,
VALIDFROM, VALIDTO)
VALUES (UNIX_TIMESTAMP(), '0', '<Login>@avci.net', '<FName>
<LName>', '1', '<Pass>', '<Login>', UNIX_TIMESTAMP(), UNIX_TIMESTAMP()
+ (86400 * 365))

Hold Customer:

UPDATE RADUSERS
SET MAXLOGINS = '0'
WHERE USERNAME = '<Login>'

Restore Held Customer:

UPDATE RADUSERS
SET MAXLOGINS = '1'
WHERE USERNAME = '<Login>'

Cancel Customer:

DELETE FROM RADUSERS
WHERE USERNAME = '<Login>'

Change Password:

UPDATE RADUSERS
SET PASS_WORD = '<Pass>'
WHERE USERNAME = '<Login>'

[digitalpoint]

Yeah.. definitely looks like it changed for whatever reason... I'm assuming SUBSCRIBERS is the new table for users. So you are going to want to change your SQL statements a little bit to conform to the data structure you are using.

[nanoburn]

the bad thing is.....I can't find anything in there where you set the maxlogin....if i can't do that then how am I going to hold and restore customers?

[digitalpoint]

You probably are going to want to ask Open Systems (if they don't give you a data model). There is no way they would drop support for concurrency.

[nanoburn]

Been trying to get ahold of anyone at Open Systems all morning and no luck so far. I can't even get a response to the mailing list. Have tried subscribing several times with several different email addresses and have yet to get a single response.

[nanoburn]

Finally got ahold of OSC about Radiator. They tell me I can make Radiator authenticate off of pretty much whatever structure I want to create within MySQL, so what I need right now is a proper sql db structure that is required for Optigold.

If someone out there that is using sql, radiator, and og could just show me their structure or give me a *.sql file of the structure i'd really appreciate it.

[digitalpoint]

Optigold does not require a specific data structure. The SQL events are not specifically for Radiator, but rather for any SQL compliant database.

[nanoburn]

Well I finally found out what one of the differences must be between the basic sql events you gave me and my version of Radiator. OSC finally let me know late tonight that Radiator doesn't come with a table named RADUSERS, but RAdmin does. Do you recommend I purchase RAdmin or just modify my sql events to fit what I have?

[digitalpoint]

Ah... okay. Nah.. you don't need to purchase RAdmin just for the table structure. I would simply change the SQL events to conform to whatever the data structure is. I could write the SQL statement for you, but I would need access to a Radiator database with your structure (which I don't have).

[nanoburn]

nah thats kewl dude....now that I know what I need to do it should be pretty easy...i'll take the basic events that you gave me and with the help of my webmaster and my many mysql books I should have it done by the end of the day...

osc also gave me a rundown on what the fields should be...there isn't a maxlogin field anymore but they suggested adding it or rather adding a STATUS field as a basic on/off switch to show whether they were active or onhold.

[silicon_dt]

So is Radmin seperate to Radiator? For example, can I buy Radmin and it acts as a stand alone radius server?

Or is it an addon that makes the radiator database different? (uses Radusers table instead of subscribors table), and I have to buy Radiator AND Radmin?

Either way did anyone come up with sql scripts for optigold for the radiator layout?

Whats confusing me is this "subscribors" table...

USERNAME
PASSWORD
ENCRYPTEDPASSWORD
CHECKATTR
REPLYATTR
TIMELEFT

What optigold variable should I use for ENCRYPTEDPASSWORD, CHECKATTR, REPLYATTR, and TIMELEFT?

[Zack]

you can write various scripts outside of optigold in php or asp on your server..(link the pages to the url thingy in opti for quick access) to run external commands on your auth database in Mysql. You can send clinets back ip addresses, timeoutes, check incoming phone numbers for your customer logins to a database limit total time online for any given period.... many many things.

I'm available for consulting if you are interested.

Zack Kneisley
Giga-Data LLC
740-432-3130 x29
749-255-7377 cell