Re: Consolidation

iml@interconnect.net
Wed, 25 Feb 1998 15:50:42 -0600

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....

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.

Is anyone else using any other methods to save the username and ip and
call time of users for past history?

On Sun, 22 Feb 1998 23:30:12 -0800, Dale E. Reed Jr. wrote:

>iml@interconnect.net wrote:
>>=20
>> We just got Emerald up this month and we are fixing to actually start
>> billing. I am looking for feedback on the Consolidation process...how
>> long it takes, how often should it be done, etc.....
>
>You should run it atleast once a month before you do your invoicing.
>Depending on the size of your SQL Server (memory is a key), it can take
>from 30 mins to several hours. I recommend having your primary
>RadiusNT server ONLY do authentication, and then you will not have
>authentication problems during the consolidation (but concurrency
>control will not work while you do authentication).
>
>> Also, what exactly does the Consolidation process do? Will I lose
>> customer call in records. I need to keep a history of each users dial
>> in times and ip addresses for security reasons etc..... We get asked
>> for this sort of info regarding legal issues with IPs from our network
>> flooding hacking etc...from time to time. Also, I would like to have
>> a history of the users calls for the previous month so we can ref it
>> when they come back on us.
>
>The consolidation process consolidates user call in records into=20
>a single history record. It does remove old records afterwards.
>It will also add cahrges to an account according to the account
>usage and rate the account is associated to.
>
>I created a table/trigger last month for those sites who wanted to
>create a paralell (trimmed) table containing many of the calls
>table fields, but not all of them.=20
>
>If you understand SQL Server, it should be fairly simple to
>add the following to create the paralell table.
>
>----------------------------------------------------------------
>
>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=20
>)
>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=20
>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
>
>=20
> 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
>
>----------------------------------------------------------------