Security

Reply
Highlighted
Frequent Contributor I

Clearpass insightdb join auth & radius_acct tables with session_id

I'm trying to access data in both the auth and radius_acct tables in the InsightDB however I am having an issue with the join.  In the auth table, the session_id is stored as a varchar however in the radius_acct the session is stored in a json array.

 

Does anyone know how to create a join between these two tables with these columns?

 

Example:

select session_id from auth limit 1

R00000000-08-5daac7ee

 

select session_id from radius_acct limit 1

{R01dfe002-12-5e7a3d5d,R03f6f208-11-5e7a3d21}


Accepted Solutions
Aruba Employee

Re: Clearpass insightdb join auth & radius_acct tables with session_id

The radius_acct.session_id is a text array. To reference the first element you can user radius_acct.session[1]. And the second using radius_acct.session[2], etc

 

I'm not great at SQL but this seems to work:

SELECT * FROM auth
JOIN radius_acct ON auth.session_id = ANY (radius_acct.session_id)

View solution in original post


All Replies
Aruba Employee

Re: Clearpass insightdb join auth & radius_acct tables with session_id

The radius_acct.session_id is a text array. To reference the first element you can user radius_acct.session[1]. And the second using radius_acct.session[2], etc

 

I'm not great at SQL but this seems to work:

SELECT * FROM auth
JOIN radius_acct ON auth.session_id = ANY (radius_acct.session_id)

View solution in original post

Highlighted
Frequent Contributor I

Re: Clearpass insightdb join auth & radius_acct tables with session_id

Thanks that did it, so easy

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