Re: Consolidation

Dale E. Reed Jr. ( (no email) )
Sun, 22 Feb 1998 23:30:12 -0800 wrote:
> We just got Emerald up this month and we are fixing to actually start
> billing. I am looking for feedback on the Consolidation
> 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
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.

If you understand SQL Server, it should be fairly simple to
add the following to create the paralell table.


Use Emerald

/****** 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

if exists (select * from sysobjects where id = object_id('dbo.calls_insert') and
sysstat & 0xf = 8)
drop trigger dbo.calls_insert

CREATE TRIGGER calls_insert ON dbo.Calls

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
Insert INTO CallDetails
Select DateAdd(Second, 0-i.AcctDelayTime, i.CallDate), i.UserName,
i.FramedAddress, i.NASIdentifier, i.CallerID
From inserted i
Where i.UserName is not NULL and i.UserName <> 'NULL'

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


-- Dale E. Reed Jr.  (       IEA Software, Inc.      |  RadiusNT, Emerald, and NT FAQs Internet Solutions for Today  |