Can I just drop the Calls Table? I really need a more efficient way to
consolidate. If I wait more than a week, consolidation takes HOURS on a
64Mb p200 with nothing else running.
Duane.
At 11:30 PM 2/22/98 -0800, you wrote:
>iml@interconnect.net wrote:
>>
>> 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
>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
>
> ----------------------------------------------------------
> Emerald Mailing List listserver@emerald.iea.com
>
==========================================================================
Duane Schaub, President |Terra World, Inc - Connecting The Planet
Terra World, Inc. |Southeast Kansas' Leading Provider
200 Arco Place, Suite 252 |Flat Fee - Never an hourly Charge
Independence, Kansas 67301 |Where Service is Top Priority!
Voice (316) 332-1616 |http://www.terraworld.net
FAX: (316) 332-1451 |dschaub@terraworld.net
===========================================================================