Run some statistics about which projects are used versus which ones are not used

Question

I would like to run some statistics about my Jira projects. For example, I would like to know if a project has almost no issues or if all issues have been created more than 1 year ago and the project is no longer used. I would like to know these statistics, that way I can clean up my Jira and remove all the projects that are nor really needed. Anyone has a suggestion on how to run this analysis? Should I use a specfic Jira gadget or should I export everything into Excel and run some analysis using Excel? Is there a JQL query that could make my life easier? What do you recommend?

Answer 1

I will answer that question from my experiences. In practice, there is no perfect way to understand this but you can check following things:

1. lastUpdate date of issues inside project
2. created date of the last n issue.
3. Status of the Project lead / Administrator role users (To check inactive users; or sometimes the user can forget the project and ask for new projects)

So, for that purpose, my suggestion is using a Groovy script inside ScriptRunner console; which returns a structure like:

Project | max(lastUpdated) | max(created) | # of issues updated older than 1 year | Project Lead | Admins

and then export that to Excel or create an endpoint and return them as JSON in order to report it on PowerBI or Tableau etc.

That will help you to understand the trending projects in addition to giving insights of the not used projects.

Answer 2

Here is the code I have written in the script console, but I had troubles for # of issues updated older than 1 year, I have commented those lines out.

    import com.atlassian.jira.component.ComponentAccessor
import org.apache.log4j.Logger;
import com.atlassian.jira.bc.issue.search.SearchService
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.jql.builder.JqlQueryBuilder
import com.atlassian.jira.web.bean.PagerFilter
import java.text.SimpleDateFormat
import com.atlassian.jira.project.Project
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.user.DelegatingApplicationUser
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.project.ProjectManager
import com.atlassian.jira.security.roles.ProjectRole
import com.atlassian.jira.security.roles.ProjectRoleActors
import com.atlassian.jira.security.roles.ProjectRoleManager
import com.atlassian.jira.issue.search.SearchException

def log = Logger.getLogger("atlassian-jira.log")

List<Project> prList = ComponentAccessor.getProjectManager().getProjectObjects()
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def issueManager = ComponentAccessor.getIssueManager()
def projectManager = ComponentAccessor.projectManager
def projectRoleManager = ComponentAccessor.getComponent(ProjectRoleManager)

log.warn("Project category|Project name|Last Date Updated| Number of issues|Project Lead|Admin List")
def searchService = ComponentAccessor.getOSGiComponentInstanceOfType(SearchService.class)
ApplicationUser  user = ComponentAccessor.jiraAuthenticationContext.getLoggedInUser()

def emptyList = []

for(Project myproject: prList){
    def lastUpdatedDate = new Date(Long.MIN_VALUE)
    def builder = JqlQueryBuilder.newBuilder()
    builder.where().project(myproject.id)
    def query = builder.buildQuery()
    def results = searchService.search(user, query, PagerFilter.getUnlimitedFilter())
  
    def lastUpdated =""
    if (results.getTotal() == 0) {
        emptyList.add(myproject.getName())
        log.warn("Empty project")
    }
    else {
       
        def i = 0
        for (Issue issue : results.getResults())
        {
             lastUpdated = issue.getUpdated()
            if (i == 0 || lastUpdated > lastUpdatedDate)
                lastUpdatedDate = lastUpdated
            i++
        }
        log.warn("project key: "+myproject.getKey())
        def query2 = ""
        try{
         query2 = jqlQueryParser.parseQuery("project = "+myproject.getKey()+" and updated < 2021-08-25 ")

        }catch(Exception e){
            log.warn("MounaException "+e)
        }

        def search = searchService.search(user, query2, PagerFilter.getUnlimitedFilter())
        log.warn("Updated issues in the last year: ${search.total}"+" project "+myproject.getKey()+"/"+results.getTotal() )

    


    
    }
    
    def projectCategory=""
    if(myproject.getProjectCategory()!=null){
        projectCategory=myproject.getProjectCategory().getName()
    }

       def admins= getAdmins(projectRoleManager, myproject, user, projectManager)
        def lastDate= new SimpleDateFormat("dd/MMM/yy").format(lastUpdatedDate)
        log.warn("oo|"+projectCategory+"|"+myproject.getName()+"|"+lastDate + "|"+results.getResults().size() + "|"+ myproject.getProjectLead().getName() +"|"+admins)
       
    
}
     log.warn("Empty projects: "+ emptyList.size()+"/"+prList.size() +" "+emptyList)


def getAdmins(ProjectRoleManager projectRoleManager, Project myproject, ApplicationUser user,  ProjectManager projectManager ){


def adminProjects = []

ProjectRole projectRole = projectRoleManager.getProjectRole("Administrators")

 def projectRoles = projectRoleManager.getProjectRoles(user, myproject)
  Project project = projectManager.getProjectObjByKey(myproject.getKey())
   def result =""
    if (project) {
        ProjectRoleActors actors = projectRoleManager.getProjectRoleActors(projectRole, project)
        result += " ${actors.getUsers()*.name}\n"
    }
    return result

}