Is there any way to display a list of issues where a custom field has the same value?

Question

As part of my JIRA service desk install I have a custom field that holds a serial number.

What I need to get to is a way of being able to show a list of other issues with the same value in the custom field.

Obviously this can be done easily with a quick search, however I was wondering if a plugin similar to suggestimate exists which would allow for a list of issues to display in the issue based on having the same value in a specific custom field, or perhaps something which can use a post-function to check if that value in a custom field already exists in an open issue.

image2016-2-26 10:33:9.png

Vasiliy Zverev · Answer

If you need to get dublicates just one time and all - SQL will be enough.

But if you need to monitor it all time you could try Script Field provided by ScriptRunner plugin.

Nicholas O Brien · Answer

Hello and thanks for response,

If I have an issue in JIRA which relates to a specific serial number (Where the serial number is a value in a custom field), then what I want to do is then  display in JIRA a list of all issues related to that serial number - so any issue which has a duplicate value in that custom field. The idea being it gives a history of interactions with that serial number.

I found an addon called suggestimate which provides similar functionality but it appears that this is only based on the summary field.

https://marketplace.atlassian.com/plugins/com.suggestimate/server/overview

image2016-2-26 10:33:9.png

Does that make sense?

Vasiliy Zverev · Answer

I use this SQL to get dublicates:

Select
	project.pkey + '-' + CAST(jiraissue.issuenum as varchar(20))
	, customfieldvalue.*
from
	(Select
		customfield.ID
		, customfieldvalue.STRINGVALUE
	from
		customfield
		join customfieldvalue		on customfield.ID = customfieldvalue.CUSTOMFIELD
	where
		customfield.cfname = 'Разработчик' --type custom field name here
	group by
		customfield.ID
		, customfieldvalue.STRINGVALUE --change value type here
	having 
		COUNT(customfieldvalue.STRINGVALUE) > 1 --change value type here
	) as dublicates
	join customfieldvalue	
		join jiraissue	
			join project	on	jiraissue.PROJECT = project.ID
		on	customfieldvalue.ISSUE = jiraissue.ID
	on	dublicates.ID			= customfieldvalue.CUSTOMFIELD
	and dublicates.STRINGVALUE	= customfieldvalue.STRINGVALUE
order by
	customfieldvalue.STRINGVALUE