Query to get active users ,groups from Database

Question

Hi Team,

I require to fetch the details of active users from Database, which belong to different groups. I tried below query:

SELECT u.id AS UserId, user_name, lower_first_name, lower_last_name, parent_id AS GroupId, parent_name AS GroupName FROM cwd_user u LEFT JOIN cwd_membership m ON u.ID=m.child_id where u.active='1' ORDER BY parent_id ;

and

SELECT u.id AS UserId, u.active,user_name, lower_first_name, lower_last_name, parent_id AS GroupId, parent_name AS GroupName FROM cwd_user u LEFT JOIN cwd_membership m ON u.ID=m.child_id ORDER BY parent_id ;

But the result is not showing only active users, it shows active and inactive both,

Can you please help me with exact query?

I raised a query with Atlassian support but they are saying they do not support custom query.

Please suggest.

Nidhi

Nic Brough -Adaptavist- · Answer

Yes, the SQL in there is unreadable as well.  Can you tell us what the simple "show us active users" does as compared with "show all users"?

Prashant Mali_vx7l3g · Answer

https://confluence.atlassian.com/jirakb/how-to-get-a-list-of-active-users-counting-towards-the-jira-application-license-278695452.html

Nic Brough -Adaptavist- · Answer

Correct, they do not support any (ab)use of the database.

Your use of "as" confuses the query and I can't read it clearly enough to see what it's doing (I know as is useful, but plain SQL without it is far more readable when you're trying to work out what a query is doing, as it remains more like the way a human would phrase a question)

Select * from cwd_user where active = '1' ;

Should list users who are active.  In the sense that you have not clicked on the "deactivate" option for them in the user management.  Start with that query - does it do what you expect?  The list is shorter than when you use just "select * from cwd_user"?