Database problems extracting users creation date and joined groups

[Salvatore Montana]

Hi there,

I need your help for two little things. I would like to extract from the db, information regarding the creation date of a given user and associate to him his authorization level (jira-administrator rather than jira-project-leader or jira-user or whatever...).

Here I am facing some problem: the first is that in my company we imported all users from an external directory, so the created_date on cwd_user is equal for everybody. Technically what I do when I "add someone" on JIRA is to assigne the persone to a specific group (see above jira-administrator, jira-project-leader...).

The second problem is related to the fact that I would like to extract the group (jira-administrator, jira-project-leader...) assigned to each person on JIRA.

Hoping it could help with the resolution, I paste the query I used till now for my purposes and that I would like to improve with your help.

Thanks to everybody for your precious suggestions

SELECT DISTINCT u.lower_user_name, 
u.first_name, 
u.last_name, 
u.email_address, 
d.directory_name,
/* coalesce(j.attribute_name, 'login.count'), */
coalesce(j.attribute_value, '0') as login_count,
from_unixtime( k.attribute_value/1000) as last_login

FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN licenserolesgroup lrg
ON Lower(m.parent_name) = Lower(lrg.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
LEFT JOIN ( select * from cwd_user_attributes where attribute_name = 'login.count' ) j
ON u.id = j.user_id
LEFT JOIN ( select * from cwd_user_attributes where attribute_name = 'login.lastLoginMillis' ) k
ON u.id = k.user_id
WHERE d.active = '1'
AND u.active = '1'
AND license_role_name = 'jira-software'