Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

Getting all the Users and their EmailIDs of a Group from database

Avatar

Level 3

Hello All,

I'm using LC 8.2.  My requirement is like i need to send email to all the members of a particular group.  I dont want to hardcode any email ids.

I think in the workbench, there is no way to get all the email ids of member of a particular group (if any please let me know).  So I think the better approach is to get the data from the database.

I have written a query which is able to get the email ids from the database, but the thing is i'm not able to restrict it to a particular group; as i'm not finding any table having group name in string format.  Please help in completing the query.

select lcuser.canonicalname, lcuser.email
from edcprincipalentity as lcuser, EDCPRINCIPALGRPCTMNTENTI as lcgroup
where lcuser.id = lcgroup.refchildprincipalid

Thanks

Deepak

1 Accepted Solution

Avatar

Correct answer by
Level 6

Hello,

Group id stored in DB in this format - CD4B792D-06CA-6A82-416A-7C1758B0F839. You can directly put this id to query.

This is query for taking data by group name:

SELECT  prin.COMMONNAME, prin.EMAIL FROM EDCPRINCIPALENTITY prin
INNER JOIN EDCPRINCIPALGRPCTMNTENTI gr ON prin.id = gr.REFCHILDPRINCIPALID

AND gr.REFPARENTPRINCIPALID = (SELECT prinGr.id from EDCPRINCIPALENTITY prinGr where pringr.commonname ='Group name');

Best regards

View solution in original post

7 Replies

Avatar

Level 6

Hello,

edcprincipalentity table has field named principaltype where group has paramater GROUP.

Try this SQL:

select lcuser.commonname, lcuser.email
from edcprincipalentity lcuser
where lcuser.principaltype = 'GROUP' and lcuser.email is not null

Best regards,

Paul Butenko

Avatar

Level 3

Hello Paul,

Thanks for the assistance.

I tried your query.  But I'm not getting what i want.  For a particular user, the commonname is his name only.

See my requirement is like this: (I'm putting my query in words)

     Select all member's email ids of a particular group.

Please help me with the query.

Thanks

Deepak

Avatar

Level 6

Hello,

I understood what do you need.

Here is query:

SELECT  prin.COMMONNAME, prin.EMAIL FROM EDCPRINCIPALENTITY prin
INNER JOIN EDCPRINCIPALGRPCTMNTENTI gr ON prin.id = gr.REFCHILDPRINCIPALID
AND REFPARENTPRINCIPALID = 'Type here group id';

Avatar

Level 3

Hello Paul,

Thanks a lot for your reply.

With Group ID, I'm able to get all the email ids.  But when we move the code to production it will be different.  So if it is a string value it will be good.

Group ID will be an encoded string.  How can i give that.  Cant we have a query where we can specify the string value of the group name?

Thanks

Deepak

Avatar

Level 3

Hello All,

I found the Query:

SELECT prin.commonname,
       prin.email,
       refparentprincipalid
FROM   edcprincipalentity AS prin,
       edcprincipalgrpctmntenti AS gr
WHERE  prin.id = gr.refchildprincipalid
  AND  refparentprincipalid =
            (SELECT id
             FROM edcprincipalentity lcuser
             WHERE lcuser.principaltype = 'GROUP'
                  AND lcuser.commonname = '<String Name of the Group>')

Thanks a lot to Paul for helping.

Deepak.

Avatar

Correct answer by
Level 6

Hello,

Group id stored in DB in this format - CD4B792D-06CA-6A82-416A-7C1758B0F839. You can directly put this id to query.

This is query for taking data by group name:

SELECT  prin.COMMONNAME, prin.EMAIL FROM EDCPRINCIPALENTITY prin
INNER JOIN EDCPRINCIPALGRPCTMNTENTI gr ON prin.id = gr.REFCHILDPRINCIPALID

AND gr.REFPARENTPRINCIPALID = (SELECT prinGr.id from EDCPRINCIPALENTITY prinGr where pringr.commonname ='Group name');

Best regards

Avatar

Former Community Member

Hello,

Thanks for posting this useful queries. From the query that Paul posted, how can I find the users from that query based on their division or dept within an organization? e.g. I got 60 rows from the query above but that total for the whole organization of 12 divisions or departments like IT, FIN, ACCT, ect. How can I find the users for the IT or FIN department only? Please advise.

Thanks,

Han Dao