To extract all JIRA users and their last access date

[lester Kacprzak]

I need to create an extract all the JIRA users and their last access date. 

[Ravi Sagar _Sparxsys_]

Hi @lester Kacprzak 

You can do that easily using ScriptRunner for Jira. You can LoginManager to get this information.

ComponentAccessor.getComponent(LoginManager)

Ravi

[RAJESH]

Hi Ravi,

Could you please provide full script here.

Thank you,

Rajesh

[kdickason]

I would love to see this script too. I'm trying to do this in both Jira and Confluence @Ravi Sagar _Sparxsys_ .  

[Mo Eb]

@Ravi Sagar _Sparxsys_

I would love to see this script as well if you could please share it that would help a lot 

Thanks

[Connor Bartal]

The following script can be run in the scriptrunner console and will output a list of users who have either:

- never logged in

- not logged on in *threshold* days (feel free to change this)

** no promises on this one, it seemed to work decently well for me*

import com.atlassian.jira.user.util.UserManager

import com.atlassian.jira.component.ComponentAccessor

import com.atlassian.jira.security.login.LoginManager

def userManager = ComponentAccessor.getUserManager()

def loginManager = ComponentAccessor.getComponent(LoginManager)

ArrayList oldUsers = new ArrayList()

Date today = new Date()

//CHANGE TO DESIRED AMOUNT

int threshold = 150

userManager.allApplicationUsers.toList().each { user ->

    def loginfo = loginManager.getLoginInfo(user.username)

    if (loginfo.getLastLoginTime() == null) {

        oldUsers.add(user.displayName)

    }

    else {

        def lastLogOn = new Date(loginfo.getLastLoginTime())

        def difference = today.minus(lastLogOn)

        if (difference >= threshold) {

            oldUsers.add(user.displayName)

        }

    }

 }

return oldUsers

[Dave Liao]

You rock!

• what version of Jira are you running?
• what version of ScriptRunner?

…just for the record.

[kdickason]

Any way we can do this on Confluence?

[Dave Liao]

@kdickason - for Confluence Cloud or Data Center?

I recommend starting a new question on Community (unless the question has been asked before, of course).

[Connor Bartal]

@Dave Liao 

Jira - 9.4.2

Scriptrunner - 7.8.0

[Dave Liao]

@Connor Bartal - thankya! Confirmed it works in Jira 9.5.0 and ScriptRunner 7.10.0. 🙌Now to get a last login date printed next to those names...

[akshay.kg]

@lester Kacprzak One given by Atlassian have a join with cwd_membership will will remove non licensed users I have removed it in below query try this. 

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 *
                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

[KGrandhi]

This worked. We use AD auth, 

Was trying to get USER info along with AD groups associated to users. Following is my query

 SELECT
u.user_name,
u.LOWER_DISPLAY_NAME,
m.LOWER_PARENT_NAME,
u.EMAIL_ADDRESS,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM JIRA.CWD_USER u
JOIN
(
SELECT *
FROM JIRA.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN JIRA.CWD_MEMBERSHIP m ON m.child_id = u.ID
order by last_login_date desc

[Strong Tractor]

@Connor Bartal  _ worked great with additional filters and statements modified it gives the total no. of logged in users per today with threshold = 0

and this script covers lot of use cases. 

[KGrandhi]

SELECT
u.user_name,
u.LOWER_DISPLAY_NAME,
m.LOWER_PARENT_NAME,
u.EMAIL_ADDRESS,
TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM JIRA.CWD_USER u
JOIN
(
SELECT *
FROM JIRA.cwd_user_attributes ca
WHERE attribute_name = 'login.lastLoginMillis'
) a ON a.user_id = u.ID
JOIN JIRA.CWD_MEMBERSHIP m ON m.child_id = u.ID
order by last_login_date desc

[mogavenasan]

Hi @lester Kacprzak,

This is a free app and I have used this quite often on different occasions; Jira User Export.

Thanks,
Moga

[Dave Liao]

Unfortunately this app isn't free (at least as of January 2023).

[Kurt Klinner]

@lester Kacprzak 

Hi Iester

if you have access to the used db you can use the sql query provided at https://confluence.atlassian.com/jirakb/find-the-last-login-date-for-a-user-in-jira-server-363364638.html

All the best

Kurt

[lester Kacprzak]

SQL for JIRA Users