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

Highlighted
New Contributor

Re: Clearpass insightdb join auth & radius_acct tables with session_id

If the file is password protected, enter the password in the Enter secret for the file field TellPopeyes.

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