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

[Julian von Hildebrand]

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

[Nic Brough -Adaptavist- · Accepted solution]

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.

[Christian Czaia [Decadis AG]]

Neat trick...

[Nic Brough -Adaptavist-]

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 ;-)

[Azfar Masut]

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 :)

[Nic Brough -Adaptavist-]

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

[Azfar Masut]

Cool, Thanks!

[Christian Czaia [Decadis AG]]

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

[Julian von Hildebrand]

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