Ah, I didn't know that solution... In the solution there is a mapping between the number 1-X to a role, to a VLAN enforcement:
Think that makes sense as you can then map any number to any VLAN; told already there probably are smarter ways of doing things ;-)
If you have urgent issues, always contact your Aruba partner, distributor, or Aruba TAC Support. Check
for how to contact Aruba TAC. Any opinions expressed here are solely my own and not necessarily that of Hewlett Packard Enterprise or Aruba Networks.
In case your problem is solved, please invest the time to post a follow-up with the information on how you solved it. Others can benefit from that.
Original Message:
Sent: Feb 21, 2024 09:59 AM
From: erik.boss
Subject: ClearPass vlan pooling syntax error
Hi Herman
This XML file generated from the ASE page...
https://ase.arubanetworks.com/solutions/id/177
Was tested in 6.6.5.
Your query works, now receiving vlan 501 back to my client. Going to finetune it.
Regards,
Erik
Original Message:
Sent: Feb 21, 2024 08:25 AM
From: Herman Robers
Subject: ClearPass vlan pooling syntax error
Again, I don't know where you got this code, nor if VLAN pooling is a good idea, but what happens in the code is that the MAC address is taken as a hexadecimal value resulting in a big number (bigint), which then is 'modulo' the number of VLANs, and then +1. Modulo is the remainder value for a division, so 10 MOD 8 is 2, because if you divide 10 by 8 you have 1x8 plus a remainder of 2.
The code that I posted, with a num_vlans of 10, would give out a number between 1-10 (the remainder of division by 10 is always from 0 to 9, and +1 makes it 1-10).
The sample code will not allow non-contiguous VLAN numbering, for example if you would have:
MOD(x'%{Connection:Client-Mac-Address-NoDelim}'::bigint,8)+500
You will get a number in the range 500-507, and I would not see how to exclude 505.
With some further SQL knowledge (I know how to read it, not how to write it), you may be able to adapt this to a VLAN name, such that it will output POOL-VLAN1 through POOL-VLAN7, where in the switch you can map that to the actual VLAN in your switch.
Another option would be to do this 'manually' like:
Connection:Client-Mac-Address-NoDelim ENDS_WITH '0' => VLAN500
Connection:Client-Mac-Address-NoDelim ENDS_WITH '1' => VLAN501
Connection:Client-Mac-Address-NoDelim ENDS_WITH '2' => VLAN502
Connection:Client-Mac-Address-NoDelim ENDS_WITH '3' => VLAN503
Connection:Client-Mac-Address-NoDelim ENDS_WITH '4' => VLAN504
Connection:Client-Mac-Address-NoDelim ENDS_WITH '5' => VLAN506
Connection:Client-Mac-Address-NoDelim ENDS_WITH '6' => VLAN507
Connection:Client-Mac-Address-NoDelim ENDS_WITH '7' => VLAN500
Connection:Client-Mac-Address-NoDelim ENDS_WITH '8' => VLAN501
Connection:Client-Mac-Address-NoDelim ENDS_WITH '9' => VLAN502
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'a' => VLAN503
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'b' => VLAN504
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'c' => VLAN506
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'd' => VLAN507
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'e' => VLAN500
Connection:Client-Mac-Address-NoDelim ENDS_WITH 'f' => VLAN501
There probably are even more ways to achieve this... probably even smarter than what I came up with.
------------------------------
Herman Robers
------------------------
If you have urgent issues, always contact your Aruba partner, distributor, or Aruba TAC Support. Check https://www.arubanetworks.com/support-services/contact-support/ for how to contact Aruba TAC. Any opinions expressed here are solely my own and not necessarily that of Hewlett Packard Enterprise or Aruba Networks.
In case your problem is solved, please invest the time to post a follow-up with the information on how you solved it. Others can benefit from that.
Original Message:
Sent: Feb 20, 2024 07:28 AM
From: erik.boss
Subject: ClearPass vlan pooling syntax error
error is gone Herman, many thanks.
I have 7 vlans, 500-504, 506,507. Any idea how to fill this into this syntax?
When I enter 500, after authenticating, it gave me vlan number 394.
Original Message:
Sent: Feb 20, 2024 07:02 AM
From: Herman Robers
Subject: ClearPass vlan pooling syntax error
Unsure where you got this from, but the error seems to be in %num_vlans% which seems not defined anywhere. You can try to change that to 10 if you have 10 VLANs:
filterQuery="SELECT (MOD(x'%{Connection:Client-Mac-Address-NoDelim}'::bigint,10)+1) as VLANNUM;"
------------------------------
Herman Robers
------------------------
If you have urgent issues, always contact your Aruba partner, distributor, or Aruba TAC Support. Check https://www.arubanetworks.com/support-services/contact-support/ for how to contact Aruba TAC. Any opinions expressed here are solely my own and not necessarily that of Hewlett Packard Enterprise or Aruba Networks.
In case your problem is solved, please invest the time to post a follow-up with the information on how you solved it. Others can benefit from that.
Original Message:
Sent: Feb 20, 2024 04:58 AM
From: erik.boss
Subject: ClearPass vlan pooling syntax error
Hi Guys,
I'm currently struggling with vlan pooling on ClearPass.
I have access to the isight database but receiving an error:
Session failed for Host=x.x.x.x, Reason=[Error executing "SELECT (MOD(x'5081408ffc91'::bigint,%num_vlans%)+1) as VLANNUM;": ERROR: syntax error at or near "%";
I was wondering where I can find the nam_vlans
I'm using this XML file from the ASE page
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<TipsContents xmlns="http://www.avendasys.com/tipsapiDefs/1.0">
<TipsHeader exportTime="Wed Jun 28 15:21:40 EDT 2017" version="6.6"/>
<AuthSources>
<AuthSource description="Randomize VLAN assignment based on MAC address" name="ClearPass VLAN Pooling" isAuthorizationSource="true" type="Sql">
<NVPair value="0" name="cache_timeout"/>
<NVPair value="PostgreSQL" name="sql_driver"/>
<NVPair value="%Insight_IP%" name="server"/>
<NVPair value="5432" name="port"/>
<NVPair value="insightdb" name="db_name"/>
<NVPair value="appexternal" name="login"/>
<NVPair value="%password%" name="password"/>
<NVPair value="10" name="timeout"/>
<NVPair value="cleartext" name="password_type"/>
<Filters>
<Filter paramValues="" filterQuery="SELECT (MOD(x'%{Connection:Client-Mac-Address-NoDelim}'::bigint,%num_vlans%)+1) as VLANNUM;" filterName="VLAN Pooling">
<Attributes>
<Attribute isUserAttr="true" isRole="false" attrDataType="Integer" aliasName="VLAN-Num" attrName="VLANNUM"/>
</Attributes>
</Filter>
</Filters>
</AuthSource>
</AuthSources>
</TipsContents>
I can't find it.
Thanks.
Erik