We want list of licensed users details

Prasad_Biddika September 22, 2024

 

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

Please help with the same.

Thanks

2 answers

0 votes
Radek Dostál
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 23, 2024

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.

0 votes
Vronik
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 23, 2024

Suggest an answer

Log in or Sign up to answer