nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to get the List of all active & inactive users with last login from Jira DB ?

Amol Dongare

Hi Community!

How to get a List of all users from my Jira PostgreSQL DB which shows the columns in result as,

1. username

2. Full name

3. email id

4. Is active or not

5. Last login date

Thanks

Accepted answers

DPKJ

In Jira Database there is a table called 'cwd_user' which contain information you are looking for.

You can easily find details like username, display name, active, email etc from this. But last login is not stored in this table.

For last login follow this link - https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

PS - Jira database design is can be found here - https://developer.atlassian.com/server/jira/platform/database-schema/

All comments

DPKJ

In Jira Database there is a table called 'cwd_user' which contain information you are looking for.

You can easily find details like username, display name, active, email etc from this. But last login is not stored in this table.

For last login follow this link - https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

PS - Jira database design is can be found here - https://developer.atlassian.com/server/jira/platform/database-schema/

Amol Dongare

Thanks for the Answer,
Could you please help me to modify the below query to add 3 new columns like Active, full name, email address?

<span>SELECT</span> <span>d</span><span>.</span>directory_name <span>AS</span> <span>"Directory"</span><span>,</span> 
    u<span>.</span>user_name <span>AS</span> <span>"Username"</span><span>,</span>
    to_timestamp<span>(</span>CAST<span>(</span>attribute_value <span>AS</span> <span>BIGINT</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>AS</span> <span>"Last Login"</span> 
<span>FROM</span> cwd_user u
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>DISTINCT</span> child_name
    <span>FROM</span> cwd_membership m
    <span>JOIN</span> licenserolesgroup gp <span>ON</span> m<span>.</span>parent_name <span>=</span> gp<span>.</span>GROUP_ID
    <span>)</span> <span>AS</span> m <span>ON</span> m<span>.</span>child_name <span>=</span> u<span>.</span>user_name
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>*</span> 
    <span>FROM</span> cwd_user_attributes <span>ca</span>
    <span>WHERE</span> attribute_name <span>=</span> <span>'login.lastLoginMillis'</span>
    <span>)</span> <span>AS</span> <span>a</span> <span>ON</span> <span>a</span><span>.</span>user_id <span>=</span> u<span>.</span>id
<span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id
<span>ORDER</span> <span>BY</span> <span>"Last Login"</span> <span>DESC</span><span>;</span>

DPKJ

Sure @Amol Dongare

<span>SELECT</span> <span>d</span><span>.</span>directory_name <span>AS</span> <span>"Directory"</span><span>,</span> 
    u<span>.</span>user_name <span>AS</span> <span>"Username"</span><span>,<br>   <strong> u.display_name AS "Full_Name",</strong><br><strong>    u.lower_email_address AS "Email_Address",</strong><br><strong>    u.active AS "Active",</strong></span>
    to_timestamp<span>(</span><span>CAST</span><span>(</span>attribute_value <span>AS</span> <span>BIGINT</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>AS</span> <span>"Last Login"</span> 
<span>FROM</span> cwd_user u
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>DISTINCT</span> child_name
    <span>FROM</span> cwd_membership m
    <span>JOIN</span> licenserolesgroup gp <span>ON</span> m<span>.</span>parent_name <span>=</span> gp<span>.</span>GROUP_ID
    <span>)</span> <span>AS</span> m <span>ON</span> m<span>.</span>child_name <span>=</span> u<span>.</span>user_name
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>*</span> 
    <span>FROM</span> cwd_user_attributes <span>ca</span>
    <span>WHERE</span> attribute_name <span>=</span> <span>'login.lastLoginMillis'</span>
    <span>)</span> <span>AS</span> <span>a</span> <span>ON</span> <span>a</span><span>.</span>user_id <span>=</span> u<span>.</span>id
<span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id
<span>ORDER</span> <span>BY</span> <span>"Last Login"</span> <span>DESC</span><span>;</span>

Viktor Pfafenrot

Thanks for the snippet. Is it possible to also display group membership?

Regards

Jorge Quintanilla

@DPKJ - One question I am running your query:

<span>SELECT</span> <span>d</span><span>.</span>directory_name <span>AS</span> <span>"Directory"</span><span>,</span> 
    u<span>.</span>user_name <span>AS</span> <span>"Username"</span><span>,<br>   <strong> u.display_name AS "Full_Name",</strong><br><strong>    u.lower_email_address AS "Email_Address",</strong><br><strong>    u.active AS "Active",</strong></span>
    to_timestamp<span>(</span><span>CAST</span><span>(</span>attribute_value <span>AS</span> <span>BIGINT</span><span>)</span><span>/</span><span>1000</span><span>)</span> <span>AS</span> <span>"Last Login"</span> 
<span>FROM</span> cwd_user u
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>DISTINCT</span> child_name
    <span>FROM</span> cwd_membership m
    <span>JOIN</span> licenserolesgroup gp <span>ON</span> m<span>.</span>parent_name <span>=</span> gp<span>.</span>GROUP_ID
    <span>)</span> <span>AS</span> m <span>ON</span> m<span>.</span>child_name <span>=</span> u<span>.</span>user_name
<span>JOIN</span> <span>(</span>
    <span>SELECT</span> <span>*</span> 
    <span>FROM</span> cwd_user_attributes <span>ca</span>
    <span>WHERE</span> attribute_name <span>=</span> <span>'login.lastLoginMillis'</span>
    <span>)</span> <span>AS</span> <span>a</span> <span>ON</span> <span>a</span><span>.</span>user_id <span>=</span> u<span>.</span>id
<span>JOIN</span> cwd_directory <span>d</span> <span>ON</span> u<span>.</span>directory_id <span>=</span> <span>d</span><span>.</span>id
<span>ORDER</span> <span>BY</span> <span>"Last Login"</span> <span>DESC</span><span>;</span>

But for some reason, It doesn't list all the users because it doesn't list from all the directories. Do you have an idea of why this is happening?

Thanks, in advance.

Jorge Quintanilla

@DPKJ - I think it is also not listing all of the users from the listed directories

Mahesh Kallepalli_u4afak

@Jorge Quintanilla Have you achieved this I need to extract only Inactive users from database.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-software

jira-software-server

atl-gruland