On Wed, 25 Feb 1998 16:26:55 -0800, Dale E. Reed Jr. wrote:
>iml@interconnect.net wrote:
>>=20
>> Dale we are pretty fresh on SQL do you have a hardcopy of this sql
>> script like at an ftp site?? I would hate to run it and it have a
>> typo or something in it. From looking at it with my limited
>> understanding of SQL it appears it creates a CallDetails table with
>> those fields listed in the script.....then data is both copied to the
>> calldetails and calls table correct?? I imagine that the table might
>> grow quite rapidly, however, I assume we could delete records past a
>> certain date etc....
>
>I copied it right from a script. I didn't type it in. It creates
>a second table, and the trigger for the calls table copies a subset
>of the information to the new table. Its typically about 1/3 to 1/4
>the size of the calls table with the same information. You can change
>it to your requirements (more/less fields).
>=20
>> Also, currently we run one emerald server and that's it. I have a
>> backup server that I can use to do authentication in the event the
>> Emerald server is down, however, what is the best way to go about
>> this? Install emerald and radiusNT on the backup machine and then
>> have the database replicate over every night or something? Even an
>> export to a text file or access database would work if we just
>> installed radiusnt on the backup machine.
>
>There are a lot of ways. You can setup replication between the
>two, backup/restore on a nightly basis, or use the radusers program
>to dump to a users file and use that (without a database).
>=20
>> Is anyone else using any other methods to save the username and ip and
>> call time of users for past history?
>
>All of the large sites use the method I posted. Some are more
>extravagant and move the data with remote procs to another SQL Server
>(away from their main one) to keep everything seperated.
>
>> >----------------------------------------------------------------
>> >
>> >Use Emerald
>> >Go
>> >
>> >/****** Object: Table dbo.CallDetails Script Date: 12/8/97 =
1:03:50 PM
>> >******/
>> >CREATE TABLE CallDetails (
>> > CallDate smalldatetime NOT NULL ,
>> > UserName varchar (18) NOT NULL ,
>> > AcctSessionTime int NULL ,
>> > FramedAddress varchar (16) NULL ,
>> > NASIdentifier varchar (16) NULL ,
>> > CallerID varchar (12) NULL
>> >)
>> >GO
>> >
>> >if exists (select * from sysobjects where id =3D =
object_id('dbo.calls_insert') and
>> >sysstat & 0xf =3D 8)
>> > drop trigger dbo.calls_insert
>> >GO
>> >
>> >CREATE TRIGGER calls_insert ON dbo.Calls
>> >FOR INSERT
>> >AS
>> >
>> >DECLARE @ast int
>> >
>> > UPDATE ServerPorts
>> > Set sp.UserName =3D i.UserName,
>> > sp.AcctStatusType =3D i.AcctStatusType,
>> > sp.CallDate =3D DateAdd(Second, =
0-i.AcctDelayTime, i.CallDate),
>> > sp.FramedAddress =3D i.FramedAddress ,
>> > sp.ConnectInfo =3D i.ConnectInfo ,
>> > sp.CallerID =3D i.CallerID
>> > FROM Servers s, ServerPorts sp, inserted i
>> > WHERE s.IPAddress =3D i.NASIdentifier AND
>> > s.ServerID =3D sp.ServerID AND
>> > sp.Port =3D i.NASPort AND
>> > (sp.CallDate <=3D DateAdd(Second, =
0-i.AcctDelayTime, i.CallDate) OR
>> > sp.CallDate IS NULL)
>> >
>> > Select @ast=3Di.AcctStatusType From inserted i
>> > IF @ast =3D 2
>> > BEGIN
>> > Insert INTO CallDetails
>> > Select DateAdd(Second, 0-i.AcctDelayTime, =
i.CallDate), i.UserName,
>> >i.AcctSessionTime,
>> > i.FramedAddress, i.NASIdentifier, =
i.CallerID
>> > From inserted i
>> > Where i.UserName is not NULL and i.UserName <>=
'NULL'
>> > END
>> >
>> >
>> > UPDATE Emerald..SubAccounts
>> > Set sa.TimeLeft =3D sa.TimeLeft - =
(i.AcctSessionTime/60 + 1)
>> > FROM Emerald..SubAccounts sa, inserted i
>> > WHERE sa.login =3D i.UserName
>> > and sa.TimeLeft <> NULL
>> > and i.AcctStatusType =3D 2
>> >GO
>> >
>> >----------------------------------------------------------------