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?
If the table structure is the same, you could simply change the name of the table in the SQL statements...
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?
What's the SQL statement you are trying to use that references RADUSERS? It does sound like maybe they changed their default table structures.
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>'
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.
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?
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.
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.
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.
Optigold does not require a specific data structure. The SQL events are not specifically for Radiator, but rather for any SQL compliant database.
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?
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).
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.
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?
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