Query returning info on project's Screens, tabs and fields

Hello.

My concern is exporting all custom fields linked to projects in which these fields are used. Purpose is to analyze custom field redundancy and proceed to a cleanup. I found a reference to such query but did not find the query itself (see link below). I would be very thankful if somebody could provide an example explaining how to write/run corresponding script.

reference to such query :
https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-ReturnProjectWorkflow

3 answers

1 accepted

3 votes

You've dived in at a SQL level here, but you can use Jira's reporting to work out some housekeeping.

I identify unused fields by defining a filter for "customfield X is not empty", and then using it in a gadget. Simple "filter statistics" with the "project" field chosen will give you a list of each project that is using it, along with the number of times it's actually populated (can be very very useful if you've got a project with 5,000 issues and the field is only populated twice - it's obvious they don't need it!)

As Christian says, doing it at a flat "field by project" level probably isn't a simple route - fields don't directly belong to projects as such. You've got to think about context, field configuration and four types of screen, and that can get messy quickly. I prefer to use the data to establish redundancy - it's far quicker for most of your data, although you'll still get the complicated cases where you need to dive in properly and slog through the relationships.

It's by no means comprehensive, but it's a quick and dirty way of finding suitable candidates for removal, or even just getting to know your data and usage patterns.

My other trick was a report that lists projects with the number of issues in them (plus the id for working out how old they are) - a project with 4 issues in it created 6 months ago probably isn't really a lot of use.

I keep meaning to dig that out and tidy it up and publish it (it doesn't give the date information I really want to display), but things like sleep and paid work keep getting in the way ;-)

That really neat @Nic Brough [Adaptavist]. Do you by any chance have a blog? Been following alot of your suggestion and tips and answers, Would be great if I can read some of your blogs regarding Atlassian if you have any :)

I don't blog often, but Adaptavist are asking me to do it more.  See https://www.adaptavist.com/blog/profile/nic-brough

I also plan to write Articles here when I get time

Hey,

you can't really map the fields to projects since they are used on screens, which are used in workflows etc. that can be mapped to projects.

Anyway, this statement will show you an overview aout your fields being used on screens:

SELECT F.ID, F.NAME as "screen name",F.DESCRIPTION as description, T.NAME as Tab, L.SEQUENCE as position, L.FIELDIDENTIFIER AS Identifier, C.cfname as "field name"
FROM fieldscreen F
LEFT OUTER JOIN fieldscreentab T  ON F.ID = T.FIELDSCREEN
LEFT OUTER JOIN fieldscreenlayoutitem L  ON T.ID = L.FIELDSCREENTAB
left outer join customfield C ON SUBSTRING(L.fieldidentifier,13,99) = CONVERT(C.id, CHAR(10))
order by F.NAME, Tab, Position;

Maybe you can use it as a starting point...

Cheers

Christian

Thanks a lot Nic & Christian - I now better know where I'm standing thanks to your suggestions/answers :)

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

360 views 4 10
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you