Skip to main content
KhalidMomin1
Level 2
January 10, 2023
Solved

SQL2 query to list all users in a group

How to get the list of all users in a group using SQL2 query?

I tried this:

select * from [rep:User] as user inner join [rep:Group] as group on user.[jcr:uuid] = group.[rep:members] where group.[rep:principalName]="myGroup"

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Jagadeesh_Prakash
Jagadeesh_PrakashAccepted solution
Community Advisor
January 10, 2023

@khalidmomin1  I recommend to try the "Users to CSV Exporter" of ACS AEM Commons instead

 

https://adobe-consulting-services.github.io/acs-aem-commons/features/exporters/users/index.html

 

View in thread

4 replies

arunpatidar
Community Advisor
arunpatidarCommunity Advisor
Community Advisor
January 10, 2023

Hi,

You can do it this without a query.

 

Session session = getSubserviceSession(); JackrabbitSession jcrSession = (JackrabbitSession) session; UserManager uM = jcrSession.getUserManager(); Group group = (Group) uM.getAuthorizable("my-group"); terator<Authorizable> itr = group.getMembers(); while (itr.hasNext()) { Object obj = itr.next(); if (obj instanceof User) { User user = (User) obj; String uid = user.getID(); } }
Translate
Arun Patidar
    KhalidMomin1Author
    Level 2
    January 10, 2023

    I am looking for SQL2 query to generate user and group reports.

    Thanks

    Translate
      Jagadeesh_Prakash
      Community Advisor
      Jagadeesh_PrakashCommunity AdvisorAccepted solution
      Community Advisor
      January 10, 2023

      @khalidmomin1  I recommend to try the "Users to CSV Exporter" of ACS AEM Commons instead

       

      https://adobe-consulting-services.github.io/acs-aem-commons/features/exporters/users/index.html

       

      Translate
        Himanshu_Singhal
        Community Advisor
        Himanshu_SinghalCommunity Advisor
        Community Advisor
        January 10, 2023

        Hi @khalidmomin1 
        Getting group members via SQL2 query won't be possible because it doesn't store the actual path of members. Instead it does store as WeakReference[]. 



        As you can see, property holds the members references and not actual path and there's no way using SQL2 query to basically iterate and get each reference absolute path even using inner join IMO. So, the possible way is what described by @arunpatidar 

        Best regards,
        Himanshu Singhal

        Translate
          Terms & ConditionsAccessibility statement

          Loading footer...