SQL2 query to list all users in a group | Community
Skip to main content
K
KhalidMomin1
January 10, 2023
Solved

SQL2 query to list all users in a group

  • January 10, 2023
  • 3 replies
  • 1765 views

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.
Best answer by Jagadeesh_Prakash

@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

 

3 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(); } }
Arun Patidar
    K
    KhalidMomin1Author
    January 10, 2023

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

    Thanks

      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

       

        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

          Terms & ConditionsAccessibility statement

          Loading footer...