Re: [Emerald] Fw: [isp-wireless] RE: OT: VPN/PPPOE/Wireless/What?

New Message Reply Date view Thread view Subject view Author view
Gulam Chagani (gulam@catsgroup.com)
Wed, 23 Apr 2003 09:37:35 -0800



Message-ID: <000701c309bf$06f244d0$04f601ac@gulam>
From: "Gulam Chagani" <gulam@catsgroup.com>
Subject: Re: [Emerald] Fw: [isp-wireless] RE: OT: VPN/PPPOE/Wireless/What?
Date: Wed, 23 Apr 2003 09:37:35 -0800

Hi Dale,

The following is our calls_insert trigger.

CREATE TRIGGER calls_insert ON Calls FOR INSERT AS DECLARE @rows INT UPDATE RadIPAddresses SET State = CASE WHEN i.AcctStatusType IN (1,3) THEN 2 WHEN i.AcctStatusType = 2 THEN 0 ELSE State END FROM RadIPAddresses ri, inserted i
 WHERE ri.NASPort = i.NASPort
 AND ri.NASIdentifier = i.NASIdentifier UPDATE RadConcurrency SET Connections = Connections + CASE WHEN i.AcctStatusType IN (1,3) THEN 1 WHEN i.AcctStatusType = 2 THEN -1 ELSE 0 END FROM MasterAccounts ma, SubAccounts sa, RadConcurrency rc, inserted i
 WHERE i.AccountID = sa.AccountID
 AND ma.CustomerID = sa.CustomerID
 AND rc.GroupID = ma.GroupID
 AND (rc.AccountTypeID IS NULL OR rc.AccountTypeID = sa.AccountTypeID) SELECT @rows = 0 SELECT @rows = @rows + 1 FROM inserted GROUP BY NASIdentifier, NASPort, ServerID UPDATE ServerPorts
        Set Username = i.Username,
  AccountID = i.AccountID,
                AcctStatusType = CASE WHEN i.AcctStatusType in (1,3) THEN 1 ELSE i.AcctStatusType END,
  CallDate = i.CallDate,
  FramedAddress = i.FramedAddress,
  CallerID = i.CallerID,
  ConnectInfo = i.ConnectInfo,
  AcctSessionID = i.AcctSessionID
        FROM ServerPorts sp, inserted i
        WHERE sp.NASIdentifier = i.NASIdentifier
 AND sp.Port = i.NASPort
 AND (i.CallDate >= sp.CallDate OR sp.CallDate IS NULL) IF (@@rowcount <> @rows)
 BEGIN
 /* SNMPUser should add the port for all except 10 (ascend) */
 INSERT INTO ServerPorts
(ServerID,Port,NASIdentifier,IPAddress,MaxSessionTime,CostPerMinute,Username
,AccountID,AcctSessionID,
 AcctStatusType,CallDate,FramedAddress,CallerID,ConnectInfo,SNMPUser)
 SELECT i.ServerID,i.NASPort,i.NASIdentifier,NULL,0,0,MIN(i.Username),MIN(i.AccountI D),MIN(i.AcctSessionID),CASE WHEN MAX(i.AcctStatusType) in (1,3) THEN 1 ELSE MAX(i.AcctStatusType) END,
 MAX(i.CallDate),MIN(i.FramedAddress),MIN(i.CallerID),MIN(i.ConnectInfo),
 Case when MIN(st.SNMPType)=1 then MIN(st.SNMPUser) else MIN(st.SNMPUser) + convert(varchar(9), i.NASPort) END
 FROM inserted i, Servers s, ServerTypes st
  WHERE s.ServerType = st.ServerType
  AND s.ServerID = COALESCE(i.ServerID,(SELECT MAX(s.ServerID) FROM Servers s, inserted ii WHERE s.IPAddress = ii.NASIdentifier))
  AND NOT EXISTS (SELECT * FROM ServerPorts sp, inserted ii WHERE sp.NASIdentifier = ii.NASIdentifier AND sp.Port = ii.NASPort)
 GROUP BY i.NASIdentifier, i.NASPort, i.ServerID
 END UPDATE SubAccounts Set TimeLeft = sa.TimeLeft - (i.AcctSessionTime/60 + 1) FROM SubAccounts sa, inserted i WHERE ((i.AccountID IS NULL AND sa.Login = i.Username) OR (i.AccountID IS NOT NULL AND sa.AccountID = i.AccountID))
 and sa.TimeLeft IS NOT NULL
 and i.AcctStatusType = 2

Could you kindly let us know what changes we need to do.

Gulam

----- Original Message ----- From: "Dale E. Reed Jr." <daler@iea-software.com> To: "To: "Emerald@Iea-Software. Com" <emerald@iea-software.com> Sent: Friday, April 18, 2003 12:06 PM Subject: FW: [Emerald] Fw: [isp-wireless] RE: OT: VPN/PPPOE/Wireless/What?

> > > We've been using the Mikrotik hotspot, quite sucessfully, for
> > a couple of
> > > months now, the one major problem we are seeing with our radius server
> > > (RadiusNT from IEA Softwares) is that we can only see one
> user (the last
> > > one) in the users online view. Has anyone faced this problem ??
>
> The problem is that the RADIUS Client (Mikrotik?) is sending the same
> port (or none?) for all users. This causes RADIUS to think that the
> user is a new user on that port, and replaces it.
>
> There are two ways to resolve this:
>
> 1) Edit the Calls table trigger and add additional intelligence to use
> unique ports for the update to the ServerPorts table. RadiusNT/X 4
> now supports character port names, so you can use the username or
> other unique fields to create the port.
>
> 2) Have Mikrotik send a unique port for each request.
>
>
> Also, we have a wireless hotspot authentication server for linux that
> is RADIUS compliant (works with RadiusNT/X) and does send the unique
> ports, including full accounting and management. It seemlessley
> integrated with Emerald as well.
>
> If you would like to try it, please contact our sales group for a
> demo at sales@iea-software.com or 509-444-2455.
>
> Dale
>
>
> ------------
>
> This is a user supported list. If you require assistance from IEA
Software's
> Support Engineers, please check out our Support resources at
> http://www.iea-software.com/support.
>
> For more information about this list (including removal) go to:
> http://www.iea-software.com/support/maillists/liststart
>
>



New Message Reply Date view Thread view Subject view Author view
This archive was generated on Wed Apr 23 2003 - 10:20:12 Pacific Daylight Time