Security

last person joined: 41 minutes ago 

Enterprise security using ClearPass Policy Management, ClearPass Security Exchange, IntroSpect, VIA, 360 Security Exchange, Extensions and Policy Enforcement Firewall (PEF).
Expand all | Collapse all

Clearpass insightdb join auth & radius_acct tables with session_id

Jump to Best Answer
  • 1.  Clearpass insightdb join auth & radius_acct tables with session_id

    Posted Mar 24, 2020 02:48 PM

    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}



  • 2.  RE: Clearpass insightdb join auth & radius_acct tables with session_id
    Best Answer

    Posted Mar 25, 2020 01:39 PM

    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)



  • 3.  RE: Clearpass insightdb join auth & radius_acct tables with session_id

    Posted Mar 25, 2020 01:56 PM

    Thanks that did it, so easy



  • 4.  RE: Clearpass insightdb join auth & radius_acct tables with session_id

    Posted Apr 07, 2020 12:32 AM

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