Looking for script or SQL to retrieve unuse custom fields

[Royce Wong]

JIRA 6.2.2

HI,

I have 600 custom fields and would like to do clean up.

Is there a script (script runner)/SQL that returns custom fields have not been used for the pass 6 months?

I am aware I can do filter like:
"My custom field" is not EMPTY and createdDate < endOfMonth(-6)

but I don't want to do that 600 times.

Also aware there is/are plug-ins but looking for script (script runner)/SQL.

Thanks.

[Royce Wong · Accepted solution]

Answered my own question.

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.CustomFieldManager
import com.atlassian.jira.issue.fields.CustomField
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def issueManager = ComponentAccessor.getIssueManager()
def user = ComponentAccessor.getJiraAuthenticationContext().getUser()def customFieldManager = ComponentAccessor.getComponent(CustomFieldManager)

List<CustomField> cfObjects = customFieldManager.getCustomFieldObjects()


for (cf in cfObjects) {
        def query = jqlQueryParser.parseQuery(cf.getClauseNames().getPrimaryName() + " is not EMPTY and created > -180d")
        def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())

       if (results.total == 0) {
            //these are the unused fields
            log.debug(cf.getName() + "," + cf.getClauseNames().getPrimaryName() + "," + cf.getCustomFieldType().name)
        }
}