Create a report with the list of custom fields where field is being used by only one pr

[Dhanyasri]

We are cleaning up the custom fields on the instance, as a part of it, we are looking for help to Create a report with the list of custom fields where field is being used by only one project.

[Anna Hryhoruk _Appfire_]

Hello @Dhanyasri ,

Speaking about Power Scripts, you can use a similar approach as described above but using SIL. You can create a script.sil file in the SIL Manager and run SQL query from there using SQL routine. Please find more info here . Also in order to use SQL routine you would need to configure data source as described here 

Also if you are interested in a nice and "user-friendly" view of the report, I may suggest checking Power Dashboard Reports and Gadgets app

Hope it helps!
Anna

[Vikrant Yadav]

Hi @Dhanyasri For getting a list of low usage custom field, you need to run database query. If you have Script runner plugin, you can run directly in Script Console or you can go to database and run query to get empty or low usage custom field. 

Kindly check SQL query mentioned in this doc for cleaning custom field :- 

https://confluence.atlassian.com/enterprise/managing-custom-fields-in-jira-effectively-945523781.html

https://confluence.atlassian.com/adminjiraserver/analyzing-the-usage-of-custom-fields-1047552722.html

Thanks,

V.Y

[Dhanyasri]

Thank you for the quick response.

Could you help if there is a way to pull the result using Powerscripts for Jira.

[Vikrant Yadav]

@Dhanyasri  I have never used this plugin.
If it support groovy scripting and have an option to write/run script, then you can try below script :'- 

Kindly modify SQL query according to your database. 

import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface

import java.sql.Connection

def delegator = (DelegatorInterface) ComponentAccessor.getComponent(DelegatorInterface)
String helperName = delegator.getGroupHelperName("default")

def sqlStmt = """
select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfieldvalue.stringvalue is null 
and customfieldvalue.numbervalue is null 
and customfieldvalue.textvalue is null
and customfieldvalue.datevalue is null
group by customfield.id, customfield.cfname, customfield.description;
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)

String result = """
<table>
<tr>
<th>Customfield Name</th>
<th>Customfield Id</th>
</tr>
"""

sql.eachRow(sqlStmt){ it ->
result += """
<tr>
<td>${it.getAt("cfname")}</td>
<td>${it.getAt("id")}</td> 
</tr>
"""

}

 

return result+"</table>"
sql.close()