How to deny access for authentication request based on session limit.?

By Arunkumar Unpublished


When we use post auth session limit enforcement profile to limit user authentications based on session limit, users will be allowed to authenticate. However post auth check executed periodically will perform the session limit checks and initiate disconnect (Radius CoA) for client devices which are more than the configured session limit.


In this case, the same disconnected client can connect back even after disconnect, which will be disconnected during next periodic post auth check.

By default post auth check performed every 5 minutes, this can be modified from Administration->Server Manager->Server Configuration->Service Parameters->Async Network Services->Polling Frequency (allowed values are 3-10 minutes).


rtaImage (8).png


However, if we want to reject user authentications based on session limit, we can use Insight DB as authorization source with custom SQL query to retrieve the active sessions count, then we can define enforcement policy rule to reject access if the user has already reached session limit.


For example:
We can use the below SQL query to check the session count and create a Insight DB as authorization source:




select count(*) as sessions from radius_acct where (username = '%{Authentication:Username}') AND end_time is null AND termination_cause is null AND (updated_at BETWEEN (now() - interval '1 hour') AND now());


rtaImage (9).png


We can modify the Enforcement policy and add a first rule to check the session count and deny access based on session count.


rtaImage (10).png






Nov 08, 2018 01:38 PM

Not quite sure where you are going wrong.  If your SELECT statement is returning anything with the criteria you are supplying, then the logic in your SELECT statement is either wrong...or the record doesn't exist.


I've modified the SELECT statement a bit in the intervening time and now use the below for determining the number of user auth'd devices on a network:


[Insight Repository]
New filter "Custom-ConcurrentSessions-User" to find concurrent sessions currently active through interim accounting updates
To be used in role mappings where %{Authentication:Username} will exist
SELECT count(distinct calling_station_id) as active_sessions
FROM radius_acct
WHERE end_time IS null
AND username = '%{Authentication:Username}'
AND ssid = '%{Connection:SSID}'
AND calling_station_id != '%{Connection:Client-Mac-Address-NoDelim}'
AND updated_at > now() - interval '12 minutes'
- active_sessions: ActiveSessions-User, Integer

Nov 01, 2018 09:42 AM



One of our customers (Hospital) allow access to its patients after authentication on a Captive Portal.


Authentication is done an external SQL Database with information like Patient Hospital ID and another field from the same database. This is working fine.


This patient can share the access with 2 other people that visit him.


The same patient receive around 8 visitors every day.


Now customer wants to limit each patient to only 3 connections.


First I tried to check the Endpoint Repository using a Enforcement to allow 3 devices using a condition below:


 (Authorization:[Endpoints Repository]:Unique-Device-Count  GREATER_THAN  3)


The problem to use this kind of Enforcement is that I would need to clear the Endpoint Repository all the time because the same patient will have around 8 different visitors and only the first 2 will be allowed because unique-device-count will limit to 3 devices (patient device + 2 first visitors).


So  I have decided to limit the session count instead of devices.


I have tried to follow the steps of the Solution above but it is not working.


Customer has a cluster with two ClearPass with version 6.7.5 .


I created a new authentication source using insightdb.


Authentication Source .jpg

I have tested using the following Filters


select count(*) as sessions  from radius_acct
where (username = ‘%{Authentication:Full-Username}’)
 AND end_time is null AND termination_cause is null
 AND (updated_at BETWEEN (now() – interval ‘1 hour’) AND now());


select count(*) as sessions from radius_acct
where radius_acct.username = ‘%{Authentication:Username}’
and radius_acct.end_time is null and termination_cause is null
and (updated_at between (now() - i


SELECT COUNT(*) AS active_count FROM radius_acct
WHERE calling_station_id = '%{Connection:Client-Mac-Address-NoDelim}'
 AND start_time >=  NOW() - INTERVAL '2 day' AND  end_time is NULL;


All of them return 0.

Enforcement and Error.jpg


I have done tests with PGadmin and the query works fine.


I also replaced the Filter using a valid username. Doing that works fine too.


select count(*) as sessions from radius_acct
where radius_acct.username = '12345678'
and radius_acct.end_time is null and termination_cause is null
and (updated_at between (now() - interval '1 hour') and now());


 Request Details.jpgPGAdmin.png

 Please, I don't know where is the error.


It seems that something is wrong with the filter syntax when I add two ore more conditions.


For example if the filter has only the condition below it works fine.


  • where radius_acct.username = ‘%{Authentication:Username}’


The same thing happens when the condition is


  • radius_acct.end_time is null and termination_cause is null


But when I need to check both using an AND it does not work anymore.


Does someone know what is twrong or if there is another way to check active sessions ?




Luis Rodrigues






Nov 29, 2017 07:22 AM

This was really useful for me thanks. I did have to tweak things slightly to get it to work properly with my eduroam service - instead of

Authentication:Username in the SQL query, I used Authentication:Full-Username.

This is because we take the username+realm as input, and strip everything after the @ to authenticate, so all our sessions were showing as 0 because the usernames were not correct.

Working perfectly now.

Feb 07, 2017 02:55 AM



I have detected my mystake, When I declared the name of the attribute in sources, it was not the same name which was in the query.


I have changed "sesiones_activas" by "sessions" and now the query to InsightDb works fine.


Then my only question is why "Sessions limit" doesn't work in "ClearPass Guest". Does Anyone has the same problem?



Feb 06, 2017 06:16 AM

Hello Chulcher,


Thank very much for your fast reply.  Now, It works fine (I have modified a little bit your query, erasing "timestamp > now()-interval '12 minutes', because I want to apply this restriction always. But I don't undertand why my configuration doesn't work, in other posts (and in this one) people query InsightsDb and it works fine...

I have tried a lot of differents configuration, setting "sesiones-activas" as attribute or as rol or with nothing. But I always obtain the same error (ERROR ExtDB.DBQuery...).

On the other hand, I have tried to control simultaneus session for the same user from ClearPass Guest ("Session limit"), but it doesn't work either.

Cuentas Guest Manager.png



Regards and thanks in advanced.


Feb 03, 2017 10:04 AM

Don't use the InsightDB source, create a new authentication source of type "Generic SQL DB" and point it at the "tipsLogDb".


Use the query against that DB to create an attribute (and enable it as an attribute). I think your implementation is failing primarily because you haven't enabled the attribute as an attribute.





Feb 03, 2017 09:16 AM

Hello sirs,

I have a ClearPass 6.5 and I'm trying the limit session for the same username too, in the following screenshoot you can see check my configuration:






I think all is correct, but when Clearpass proccess a request I got the following message:

2017-02-03 14:38:51,194[AuthReqThreadPool-13-0x7ff7c664e700 r=R00000033-01-589487eb h=74] ERROR ExtDB.DBQuery - Failed to get value for attributes=Sesiones-Activas]




The query is correct and InsightsDB is working properly because I have checked them with PgAdmin:



Thank very much,



Jul 21, 2016 03:57 PM

The extra bit in the WHERE statement is designed to ignore abandoned or "lost" sessions.


This portion of the query will pull information for any sessions that have not been updated in the Insight database as closed:

select count(*) as sessions 
from radius_acct
where (username = '%{Authentication:Username}') AND
end_time is null AND
termination_cause is null;

I believe the idea behind adding the check for "updated_at" is to allow the query to weed out any sessions that don't appear to have any activity because the record in Insight hasn't been updated or that the session stop didn't get received.


select count(*) as sessions 
from radius_acct
where (username = '%{Authentication:Username}') AND
end_time is null AND
termination_cause is null AND
(updated_at BETWEEN (now() - interval '1 hour') AND now()); 


May 25, 2016 06:48 AM


Does somebody can explain why we need to add the following query: "AND (updated_at BETWEEN (now() - interval '1 hour') AND now());"


Why not just: select count(*) as sessions from radius_acct where (username = '%{Authentication:Username}') AND end_time is null AND termination_cause is null