Thanks for the tip, and the reply. I took your advice, although this is
easier said than done. Fortunately, after a lot of grueling mind-bending, I
found a quick and fairly efficient way of accomplishing this.
I let Radiator create the detail file in its default format. Then, as my VB
app reads the data, rather than INSERTing the row into the database
directly (which would fail since the data is not numeric), the VB app
passes the data to a Stored Procedure, which does the insertion.
The SP handles this as follows:
1) Accept as parameters all the values of the accounting record that
correspond to columns in the (Emerald-style) Calls database table,
including "dictionary" text values.
2) The SP performs the following SQL structure:
INSERT INTO Calls (col1, col2, col3...)
SELECT "col1"=(subquery1), "col2"=@col2, "col3"=(subquery2)...
As such, the INSERT will insert the row that is selected by the SELECT
statement. The trick is formatting the SELECT statement to do the transl
ations. For a parameter like NASIdentifier, which is a static value not
looked up in the dictionary, you would simply pass the @parameter as for
col2, shown above. For parameters that use dictionary names (such as
AcctStatusType=Stop), you need to convert Stop back into a number. For this
you use the Subquery form shown. The Subquery consist of a SELECT statement
which is an INNER JOIN between RadAttributes and Radvalues, with a WHERE
clause corresponding to that column.
Then, magically, the row that is given to the SP in text form, is inserted
in numeric form. This, I'm sure, results in a performance hit over a direct
INSERT, but it's pretty fast and allows complete compatibility.
Thanks for the help!
Dave
On Friday, February 11, 2000 10:31 PM, Hugh Irvine [SMTP:[EMAIL PROTECTED]]
wrote:
>
> Hello Dave -
>
> This got lost in my inbox because of the wrong date.....(or it was stuck
> somewhere for a *long* time...).
>
> On Thu, 06 Jan 2000, Dave Kitabjian wrote:
> > Currently, a typical detail entry from Radiator looks like:
> >
> > Thu Dec 16 16:30:33 1999
> > User-Name = "dave"
> > Service-Type = Framed-User
> > NAS-IP-Address = 253.63.154.1
> > NAS-Port = 1234
> > NAS-Port-Type = Async
> > Acct-Session-Id = "00001234"
> > Acct-Status-Type = Stop
> > Acct-Delay-Time = 0
> > Acct-Session-Time = 1000
> > Acct-Input-Octets = 20000
> > Acct-Output-Octets = 30000
> > Timestamp = 945379833
> >
> > However, our current Sql Server database is set up relationally, so
that in
> > the column for ServiceType, we have numerical entries rather than text
> > entries like "Framed-User". The same applies to NAS-Port-Type,
> > Acct-Status-Type, Acct-Authentic, User-Service, Login-Service,
> > Framed-Protocol, and others. The "dictionary" in our database consists
of
> > Emerald-style tables, RadAttributes and RadValues, to do the
appropriate
> > lookups.
> >
> > Now, we are using a custom accounting interface which will read the
detail
> > text files in standard Radiator format, parse them, and INSERT them
into
> > this table. I've tried several database tricks to try to coerce the
detail
> > data into our table, such as INSERT with JOIN, Views, etc, to no avail.
> >
> > So.......
> >
> > I'm assuming Radiator natively receives this data in the numeric form
> > rather than the text form. So, what would fix the problem, I think,
would
> > be if I could tell Radiator to stop looking up values in the
dictionary,
> > and instead just log the raw numeric values in the detail file.
> >
>
> It would be better to post-process the data as it is in text form, and
use the
> Radiator dictionary definitions to do the transformations back to numeric
form.
>
> hth
>
> Hugh
>
> --
> Radiator: the most portable, flexible and configurable RADIUS server
> anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
> Platypus, Freeside, TACACS+, PAM, external, etc etc on Unix, Win95/8,
> NT, Rhapsody
===
Archive at http://www.thesite.com.au/~radiator/
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
