List users with initial password

[andrea schneider]

Hi

Is there a way to get a list of users who have never changed their initial password? It can also be a database query. Would be for Jira server on Version 6.4.11.

Thanks...

[Nir Haimov · Accepted solution]

One way that i think of.

By SQL, 

Find all users with attribute "login.count", will give you all users that ever logged in.

SELECT * FROM public.cwd_user_attributes
WHERE attribute_name = 'login.count'

 No get all users from

select * from cwd_user

Now, remove all users with attribute  "login.count" from the list of all users, you will get a list of all users that never logged in, which means, they never changed they initial password (changing the initial password is when you first time log in)

[andrea schneider]

Hi Nir

Thanks for you reply. The problem is, the users aren't forced to change their password on first login, so this wouldn't work.

[Nir Haimov]

Than do the same with attribute "passwordLastChanged" instead of "login.count"

and you will get all users who have changed they password (at list one time in their life).

remove this list from all users you will get all users who never changed their password

[andrea schneider]

Hi Nir

This makes more sense - perfect - thanks a lot!

[Jay Salvay]

Have you find a solution? Also, have you tried this with script runner? I have the same issue and I'm looking for a solution

[andrea schneider]

Hi

I have used a sql query as we're not having ScriptRunner. There might be more elagant versions but this one was ok for my needs:

SELECT display_name, user_name, lower_email_address 
FROM cwd_user
    where user_name in (
    select  cu.user_name 
    from  cwd_user cu
    where cu.active = 1
    and cu.email_address like '%ouremail.com%'
    minus
    select cu.user_name 
    from cwd_user_attributes cua
    join cwd_user cu on cu.id = cua.user_id 
    WHERE attribute_name = 'passwordLastChanged'
    and cu.email_address like '%ouremail.com%'
    and cu.active = 1
)
order by display_name

If you have different user directories you have alos to restrict to the directory_id in the table cwd_user_attributes.