Extracting group members with SQL

[Jakob KN]

Hi Community

This may be more of a SQL question then a Jira question, but here I am since someone may have encountered the same need for extracting users. 

I'm trying to extract all members of the groups "jira-stage-users" and "Casino", while also excluding users from the extraction which are in the "atlassian-addons", "External" and "Consultants" groups. 

Users in the groups to exclude can also be in the jira-stage-users and Casino groups, so i can't simply take all users from those two groups.

The users I'm extracting must also be active. 

With my current script, I'm getting all users from all 5 groups and the ones who's members of the 5 groups that are to be excluded and not removed from the results. 

The users can be members of both the jira-stage-users and Casino groups at the same time as being members of the groups that's to be excluded. Those users should not be included in the returned result.

My current script is:

SELECT DISTINCT
cwd_user.lower_user_name, cwd_user.display_name, cwd_user.lower_email_address
FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name = 'jira-stage-users' or cwd_group.group_name = 'Casino')
AND (cwd_group.group_name != 'atlassian-addons' AND cwd_group.group_name != 'External' AND cwd_group.group_name != 'Consultants')
AND cwd_user.active = 1

We're on a PostgreSQL database. 

I'm probably missing a simple method in order to make it work, if anyone has any ideas it's much appreciated. 
Thanks in advance.

[Kian Stack Mumo Systems · Accepted solution]

@Jakob KN, 

I'm not exactly a "Sql" guy, so there may be a cleaner way to do what you want, but I think this will give you the result you're looking for.

SELECT DISTINCT
cwd_user.lower_user_name, cwd_user.display_name, cwd_user.lower_email_address, cwd_group.group_name
FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name = 'jira-stage-users' or cwd_group.group_name = 'Casino')
and cwd_user.lower_user_name not in
(select lower_user_name FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name != 'atlassian-addons' 
AND cwd_group.group_name != 'External' 
AND cwd_group.group_name != 'Consultants'))
AND cwd_user.active = 1

I hope this helps!

Thanks,

Kian

[Jakob KN]

Hi Kian, thanks for pitching in, much appreciated! 

I've tested out the query, and unfortunately it returns no results at all. 
The error seems to be in the following block of the query:

---
and cwd_user.lower_user_name not in
(select lower_user_name FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name != 'atlassian-addons'
AND cwd_group.group_name != 'External'
AND cwd_group.group_name != 'Consultants'))
---

When the above is removed and the query run again, it returns a list of users as expected.

I'm suspecting that the issue may be with how the two statements are joined together but I'm not sure how else to join them together and filter the retuned user list.
(1: include jira-stage-users & Casino, and then 2:  exclude users in atlassian-addons, External, Consultants)

Any ideas why this could be? 

[Kian Stack Mumo Systems]

@Jakob KN, 

Can you try this query? I think perhaps my logic was just messed up in the query I supplied previously! I thought I had tested it, but perhaps I hadn't!

SELECT DISTINCT
cwd_user.lower_user_name, cwd_user.display_name, cwd_user.lower_email_address, cwd_group.group_name
FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name = 'jira-stage-users' or cwd_group.group_name = 'Casino')
and cwd_user.lower_user_name not in
(select lower_user_name FROM cwd_user
JOIN cwd_membership ON cwd_membership.child_id = cwd_user.id
JOIN cwd_group on cwd_membership.parent_id = cwd_group.id
WHERE (cwd_group.group_name = 'atlassian-addons' 
OR cwd_group.group_name = 'External' 
OR cwd_group.group_name = 'Consultants'))
AND cwd_user.active = 1

[Jakob KN]

Hi Kian, 

That seem to have done the trick. 
Users who are in both "jira-stage-users" and "casino" was duplicated in the results, but they're easily removed in a spreadsheet. 

Thank you!