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