Security

 View Only
last person joined: yesterday 

Forum to discuss Enterprise security using HPE Aruba Networking NAC solutions (ClearPass), Introspect, VIA, 360 Security Exchange, Extensions, and Policy Enforcement Firewall (PEF).
Expand all | Collapse all

ClearPass vlan pooling syntax error

This thread has been viewed 36 times
  • 1.  ClearPass vlan pooling syntax error

    Posted Feb 20, 2024 04:58 AM

    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



  • 2.  RE: ClearPass vlan pooling syntax error

    EMPLOYEE
    Posted Feb 20, 2024 07:03 AM

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



  • 3.  RE: ClearPass vlan pooling syntax error

    Posted Feb 20, 2024 07:29 AM

    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.




  • 4.  RE: ClearPass vlan pooling syntax error

    EMPLOYEE
    Posted Feb 21, 2024 08:26 AM

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



  • 5.  RE: ClearPass vlan pooling syntax error

    Posted Feb 21, 2024 09:59 AM

    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




  • 6.  RE: ClearPass vlan pooling syntax error

    EMPLOYEE
    Posted Feb 22, 2024 02:51 AM

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



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