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 :
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 ;-)
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...
As a Belgian, beer-lover and home brewer, beer is one of my great passions. I love the fact that with just a few ingredients (usually just water, hop and malt) you can create so many different tastes...
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!
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
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot