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

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

}