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).
> 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).
> 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 = object_id('dbo.calls_insert') and
> >sysstat & 0xf = 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 = i.UserName,
> > sp.AcctStatusType = i.AcctStatusType,
> > sp.CallDate = DateAdd(Second, 0-i.AcctDelayTime, i.CallDate),
> > sp.FramedAddress = i.FramedAddress ,
> > sp.ConnectInfo = i.ConnectInfo ,
> > sp.CallerID = i.CallerID
> > FROM Servers s, ServerPorts sp, inserted i
> > WHERE s.IPAddress = i.NASIdentifier AND
> > s.ServerID = sp.ServerID AND
> > sp.Port = i.NASPort AND
> > (sp.CallDate <= DateAdd(Second, 0-i.AcctDelayTime, i.CallDate) OR
> > sp.CallDate IS NULL)
> >
> > Select @ast=i.AcctStatusType From inserted i
> > IF @ast = 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 = sa.TimeLeft - (i.AcctSessionTime/60 + 1)
> > FROM Emerald..SubAccounts sa, inserted i
> > WHERE sa.login = i.UserName
> > and sa.TimeLeft <> NULL
> > and i.AcctStatusType = 2
> >GO
> >
> >----------------------------------------------------------------
-- Dale E. Reed Jr. (daler@iea-software.com)_________________________________________________________________ IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs Internet Solutions for Today | http://www.iea-software.com