Security

Reply
Highlighted
All-Decade MVP 2020

MPSK - Return visitor_name as RADIUS UserName

Hi all,

 

I'm tinkering with MPSK and trying to change the default enforcement profiles to return the registered device name instead of the sponsor name as the RADIUS username. 

 

For example if i register a device in CPG as Camera1 and then i auth using MPSK, the access tracker shows the sponsor name (i.e. admin)

 

I can't figure out how to modify the SQL query to grab the visitor_name attribute out of Clearpass Guest to return as the username instead. 

 

Anybody done this ?  

 

Scott

 

 

 

 

 

Highlighted

Re: MPSK - Return visitor_name as RADIUS UserName

To your question, no I haven't personally tried this;-)

But perhaps a few pointers that might help to make it work for you.

 

The MPSK devices are stored as part of the tips_guest_users DB with guest_type = 'DEVICE'

owehrli_0-1581513743652.png

Attributes associated with a device are stored as nested JSON in the attribute column and that includes the "Visitor Name" that you are looking for.

 

{
    "airgroup_enable": "1",
    "no_password": "1",
    "Create Time": "2018-12-14T13:36:33+00:00",
    "expire_postlogin": "0",
    "airgroup_shared_role": "",
    "remote_addr": "192.168.1.10",
    "Role ID": "2",
    "no_portal": "1",
    "do_expire": "1",
    "airgroup_shared_user": "",
    "source": "mac_create",
    "mac": "AA-BB-CC-DD-EE-FF",
    "Visitor Name": "Chromecast",
    "mac_auth": "1",
    "airgroup_shared": "1",
    "airgroup_shared_time": "",
    "sponsor_profile_name": "Device Registration",
    "simultaneous_use": "1",
    "airgroup_shared_group": "shared-services",
    "airgroup_shared_location": ""
}

 

If you want to work with those values, you need to become familiar with extracting nested JSON from SQL. Personally, it took me a while, this link helped me a lot: http://www.wagonhq.com/sql-tutorial/values-from-nested-json

 

Also the [Guest Device Repository] authentication source includes already filters that extract values from nested JSON:

 

owehrli_1-1581515350873.png

 

 

Now you will need to modify your Authentication source and add a filter query that fetches your desired attribute. Something like this will actually fetch the visitor name "Chromecast" for the above MAC address

 

select attributes ->> 'Visitor Name' from tips_guest_users as tgu where tgu.guest_type ='DEVICE' AND tgu.user_id = 'AA-BB-CC-DD-EE-FF'

 

Disclaimer: there might be a more efficient way of doing this but that's what I could come up with;-)

Re: MPSK - Return visitor_name as RADIUS UserName

Hi Scott,

 

from my point of view, if you register the device, you need to enter a "Device Name". This device name is the visitor name. And you can use this one quite easily. Just return the "GuestUser:Visitor Name” as IETF:RADIUS Username. 

I have written a short post about this. It is not related to MPSK but uses the device database in ClearPass as well. So it should apply to your request as well:

https://www.flomain.de/2017/03/mac-authentication-with-username-using-clearpass/ 

 

Hope this helps.

 

BR

Florian


visit our Youtube Channel:
https://www.youtube.com/channel/UCFJCnuXFGfEbwEzfcgU_ERQ/featured
Please visit my personal blog as well:
https://www.flomain.de
Highlighted
All-Decade MVP 2020

Re: MPSK - Return visitor_name as RADIUS UserName

thanks so much for the extensive reply. whilst i don't immediately follow, you've given me a great start so i'll review and see how i go. 

 

many thanks!

 

Highlighted
All-Decade MVP 2020

Re: MPSK - Return visitor_name as RADIUS UserName

Hi Florian,

 

thanks for your reply. Whilst i initially thought the same as you, it seems the way the database is constructed is slightly different for devices. 

 

The MPSK workflow doesn't use the Guest Repository but rather the Device Repository. This new authentication source seems to have separate auth SQL search queries and visitor_name doesn't appear to be in the schema. 

 

When i initially mapped the variable as you suggested it simply returned a null value. Trying to modify the search query resulted in a table not found error. 

 

I also tried connecting via pgadmin to browse the table and found that visitor_name isn't part of that query. this is where my very basic knowledge of SQL / JSON falls apart!!

 

I've found this is similar to when you try to query endpoint records which are also stored in a different manner. 

 

Scott

 

Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: