How to query for users who have never logged in and whose accounts were added over 180 days ago?

[O. Funkhouser]

I have a groovy script that I use to query for a list of users that have not logged into JIRA for 180 days. I need to modify the script to create a list of users that have never logged in and whose account was created over 180 days ago. Is there a way to do that?

import com.atlassian.crowd.embedded.api.CrowdService
import com.atlassian.crowd.embedded.api.UserWithAttributes
import com.atlassian.crowd.embedded.impl.ImmutableUser
import com.atlassian.jira.bc.user.UserService
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.user.ApplicationUsers
import com.atlassian.jira.user.util.UserUtil
import org.apache.log4j.Level
import org.apache.log4j.Logger

def log = Logger.getLogger("Debug.Test")
log.setLevel(Level.DEBUG)

int numOfDays = 180 // Number of days the user was not logged in
Date dateLimit = (new Date())- numOfDays
 
UserUtil userUtil = ComponentAccessor.userUtil
CrowdService crowdService = ComponentAccessor.crowdService
UserService userService = ComponentAccessor.getComponent(UserService)
ApplicationUser updateUser
UserService.UpdateUserValidationResult updateUserValidationResult
 
long oldUserCount = 0
long totalUserCount = 0

userUtil.getUsers().findAll{it.isActive()}.each {
    totalUserCount++

    UserWithAttributes user = crowdService.getUserWithAttributes(it.getName())
    String lastLoginMillis = user.getValue('login.lastLoginMillis')
 
    if (lastLoginMillis?.isNumber()) {
        Date d = new Date(Long.parseLong(lastLoginMillis))
        if (d.before(dateLimit)) {
            oldUserCount++
        }
    }
    else {
        // Need code here to identify users whose accounts were created over 180 days ago.
    }
}

log.debug "${totalUserCount} active users\n"
log.debug "${oldUserCount} old users\n"

[MoroSystems Support · Accepted solution]

Hello O.,

it looks like this information is not available through JIRA Java API and you would need executing custom SQL script:

• https://jira.atlassian.com/browse/JRASERVER-13089
• https://confluence.atlassian.com/jirakb/list-of-users-showing-when-the-accounts-were-created-and-by-whom-695241575.html

Regards,
Martin

[O. Funkhouser]

Thank you! I was able to build on top of the following Groovy script that connects to the database and got it working.

https://scriptrunner.adaptavist.com/latest/jira/recipes/misc/connecting-to-databases.html