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