Oracle DB - Last Login By Active User Edited

I am running JIRA 6.1.7 with an Oracle backend.  

I'm almost at the limit when it comes to licenses, so I need to clear up some users who've not logged in for a long time.  This can be done on the GUI but it's very slow and long. 

Is there a way to do this on the DB?   I ran the following:

SELECT d.directory_name,

    u.user_name,

        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date

FROM cwd_user u

JOIN (

    SELECT DISTINCT child_name

    FROM cwd_membership m

    JOIN globalpermissionentry gp ON m.parent_name = gp.GROUP_ID

    WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')

    ) m ON m.child_name = u.user_name

LEFT JOIN (

    SELECT *

    FROM cwd_user_attributes ca

    WHERE attribute_name = 'login.lastLoginMillis'

    ) a ON a.user_id = u.ID

JOIN  cwd_directory d ON u.directory_id = d.ID

order by last_login_date desc;

 

I get an error that the table doesn't exist.

 

Can someone tell me which query will work?  Note that I only want to see active users, as the inactive ones are not counting towards my licence limit.   So how do I find (for example) users who are 1. active but who 2. have not logged into the system in the past six months?

Thanks.

1 answer

0 vote

Hi David,

  • You stated the query doesn't find the table.  Which table are you referring to?

Per How to Get a List of Active Users Counting Towards the JIRA application License the following should get you the active users in JIRA 6.1.7:

SELECT DISTINCT u.lower_user_name 
FROM   cwd_user u 
       JOIN cwd_membership m 
         ON u.id = m.child_id 
            AND u.directory_id = m.directory_id 
       JOIN schemepermissions sp 
         ON Lower(m.parent_name) = Lower(sp.perm_parameter) 
       JOIN cwd_directory d 
         ON m.directory_id = d.id 
WHERE  sp.permission IN ( '0', '1', '44' ) 
       AND d.active = '1' 
       AND u.active = '1';

You'll most likely need to modify it slightly for Oracle.

To get the last logged in times in Oracle you would run the following per Retrieve last login dates for users from the database:

SELECT cwd_user.*,
to_date('01.01.1970','dd.mm.yyyy') + to_number(cwd_user_attributes.attribute_value)/1000/60/60/24 AS last_login
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id
AND cwd_user_attributes.attribute_name ='login.lastLoginMillis' AND cwd_user.ACTIVE ='1'

Try those and see if it works for you.

Cheers,

Branden

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira

5 ways you can make the most of Jira Software and Bitbucket Cloud

As part of the Bitbucket product team I'm always interested in better understanding what kind of impact the use of our tools have on the way you work. In a recent study we conducted of software devel...

82 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you