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

[deepusrp]

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

[Paul_Butenko]

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

[deepusrp]

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

[Paul_Butenko]

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';

[deepusrp]

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

[deepusrp]

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.

[Paul_Butenko · Accepted solution]

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

[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