We want list of licensed users details

Question

We want list of licensed users details like Fullname| UserID|email ID|Lat login date

Please help with the same.

Thanks

Radek Dostál · Answer

https://confluence.atlassian.com/jirakb/get-list-of-licensed-users-in-jira-server-and-data-center-278695452.html is the starting point.

Adding to that, login date is stored in cwd_user_attributes, so you can do a left join to it (because there won't be any records for users that have never logged in), and you get something similar to:

SELECT DISTINCT
    u.lower_user_name AS "Username",
    u.display_name AS "Display Name",  
    u.email_address AS "Email Address", 
    cua.attribute_value AS "Last Login Date"
FROM
    cwd_user u
LEFT JOIN
    cwd_user_attributes cua ON cua.user_id = u.id AND cua.attribute_name = 'login.lastLoginMillis'
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
WHERE
    d.active = '1' AND u.active = '1' AND license_role_name = 'jira-software'

DB is the only way to get such a list. There are some plugins on the marketplace that can generate user lists, or this could be also scripted through groovy, but if we are talking vanilla Jira without plugins, that would be the database.

Vronik · Answer

Hi

Read this post, maybe can help you

https://confluence.atlassian.com/jirakb/get-list-of-licensed-users-in-jira-server-and-data-center-278695452.html

Regards