Search in issue history

[Jesus Gemio Lopez]

Hi

Are there any manner to search in the issue history.

I would like launch queries like:

• Issues updated last year.
• Issues that had a custom field with a certain value. (like was operator)

I can see these information in the History of each issue, but I can't use them in searchs.

Any workaround?

Thank you!!

Jesús

[Andy Heinzer · Accepted solution]

Hi Jesus,

I understand that you are looking to find Jira issues that meet some criteria. 

You can use the issue navigator in Jira in order to see issues updated in the last year.  For example, you could use an advanced JQL statement such as

updated >= -365d ORDER BY updated ASC

in order to see all the issues updated within the last 365 days.  Or you could use

updated >= '2019-01-01' order by updated ASC

in order to see all the issues updated since the beginning of last year.

However for the request of finding issues with a specific custom field value historically, this is not something that JQL can do natively today.  There are a few system fields in Jira where you can use the WAS operator on them, but these are limited to fields such as Assignee, Fix Version, Priority, Reporter, Resolution, and Status fields only.  This does not extend itself to custom field types.

That said, since you're using Jira Server, it might be possible to find these issues if you can access the SQL database.  There is a guide for this over in Retrieve issue change history from database in Jira server.  That KB has an example to try to pull this data from the SQL database as such:

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg 
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='custom' AND ci.FIELD='Name of Custom Field'
inner join app_user au on cg.author = au.user_key
WHERE cg.issueid=(select id from jiraissue where issuenum = 115 and project in (select id from project where pname = 'Project name')) 
order by 1,3,4;

You would need to adjust this query though to input the name of the custom field, the issue number, and the project name.  However you could probably modify this SQL query to return to you all the issue keys that had a particular custom field value at some point.  Something more like

SELECT p.pkey || '-' || i.issuenum as IssueKey, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg 
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id AND ci.FIELDTYPE='custom' AND ci.FIELD='number1'
inner join app_user au on cg.author = au.user_key

might be more helpful here.  Let me know if you have any questions about this approach.

Andy

[Zailyn Letriz]

I can't use the was function on Projects.  We will get items from the Service Desk Portal and if an item becomes a bug for a developer to fix; we will move this item to their Development Team project.  I am trying to create a Filter that can bring items that where part of the Service Desk Project however, the JSQL tells me is not supported and I get this message : 'History searches do not support the 'project' field'.  Do you have a suggestions to this problem?

[Abhishek Kalikota]

I believe 'project' is either a custom field or a field its not supported to search for a history for.

refer : https://community.atlassian.com/t5/Jira-Software-questions/Custom-field-history-search-plug-in/qaq-p/1230341