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

Dhanyasri May 7, 2023

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.

2 answers

1 vote
Anna Hryhoruk _Appfire_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 8, 2023

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

0 votes
Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 8, 2023

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 May 8, 2023

Thank you for the quick response.

 

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

Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 8, 2023

@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()

Suggest an answer

Log in or Sign up to answer