1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

OptiGold, Radiator, & MySQL

Discussion in 'Optigold ISP' started by nanoburn, Jul 29, 2004.

0
  1. #1
    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?
     
    nanoburn, Jul 29, 2004 IP
  2. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #2
    If the table structure is the same, you could simply change the name of the table in the SQL statements...
     
    digitalpoint, Jul 29, 2004 IP
  3. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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?
     
    nanoburn, Jul 29, 2004 IP
  4. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #4
    What's the SQL statement you are trying to use that references RADUSERS? It does sound like maybe they changed their default table structures.
     
    digitalpoint, Jul 29, 2004 IP
  5. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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>'
     
    nanoburn, Jul 29, 2004 IP
  6. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #6
    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.
     
    digitalpoint, Jul 29, 2004 IP
  7. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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?
     
    nanoburn, Jul 30, 2004 IP
  8. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #8
    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.
     
    digitalpoint, Jul 30, 2004 IP
  9. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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, Jul 30, 2004 IP
  10. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #10
    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.
     
    nanoburn, Aug 2, 2004 IP
  11. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #11
    Optigold does not require a specific data structure. The SQL events are not specifically for Radiator, but rather for any SQL compliant database.
     
    digitalpoint, Aug 2, 2004 IP
  12. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #12
    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?
     
    nanoburn, Aug 2, 2004 IP
    STVP likes this.
  13. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #13
    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).
     
    digitalpoint, Aug 2, 2004 IP
  14. nanoburn

    nanoburn Peon

    Messages:
    42
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #14
    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.
     
    nanoburn, Aug 3, 2004 IP
  15. silicon_dt

    silicon_dt Guest

    Messages:
    26
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #15
    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?
     
    silicon_dt, Nov 5, 2005 IP
    STVP likes this.
  16. Zack

    Zack Guest

    Messages:
    52
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #16
    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
     
    Zack, Dec 11, 2005 IP