Jira Search for comment text in a given timeframe (Adaptavist ScriptRunner)

[Björn Schotte]

Hi,

I just want to search for specific issues which were commented in a given timeframe and where the comments from that timeframe contain a given string. We've got Jira Server with Adaptavist's ScriptRunner.

Current query is:

... and issueFunction in commented('after 2018-05-01 before 2018-09-30') and comment ~ "Appointment"

This gives tickets which were commented between first of May and 30th of September and where comments contain the string "Appointment".

Unfortunately, this also lists tickets where a comment from eg. 2018-03-13 contains the word Appointment.

So the task is: I want to restrict it only to the words which were given in the selected time frame.

Is there any solution to this?

[Tarun Sapra · Accepted solution]

Hello @Björn_Schotte

Adding to the answer provided by @Andy Heinzer , if you have few spare hours on your hand then you can implement this feature yourself by using script runner plugin.

Here's the step by step process

• Read the 2 examples here - https://scriptrunner.adaptavist.com/latest/jira/custom-jql-functions.html
• Create an custom JQL function which implements "JqlValuesFunction" (2nd example from above link)
• Basically your custom JQL function should look like something -

issuekey in commentInDateRange(startDate, endDate, String). You take 3 parameters startDate, endDate and String (like "Appointment" in your case)

• Now in your code, you perform a JQL search using SearchService and the jql Query in the code should be 

issueFunction in commented('after<startDate> before <endDate>') 

• Now, you get issue objects which satisfy above criteria. Now you use "CommentManager" to iterate through all comments on an issue which have createdDate after startDate and these comments should also contains the text "Appointment" in the comment body.  So, basically you check for 2 conditions on every issues returned in search result, comment created after startDate and comment having "Appointment" in getBody()
• And thus you will receive the final list of issues which satisfy your criteria of having a string as part of the comment in a date range.  The you just use QueryLiteral to collect and return the issueKeys from the final list of issues and that should do it!!!

[Andy Heinzer]

whoa, now there is something I haven't done before, custom jql functions.  Thanks for adding this @Tarun Sapra

[Olga Videc]

Hello @Tarun Sapra 

Is there a way to add time function?

I need all the issues that were commented before 8 am and after 5pm in last 30 days

BR, Olga

[Andy Heinzer]

I can understand why you would expect that Jira's JQL would return the results differently here.  However JQL is designed within Jira to return Jira issues.  Technically Jira is doing what we have told it here, return the issues that have this word in a comment, and all issues that have comments between this time period.  In short, it returned the issues, not the specific comments. JQL is not designed to return comments or other fields, but rather issues.   It might be possible if there was a way to nest these two queries inside each other somehow, but I cannot yet find any way to do this within Jira alone, or even with the added functions of scriptrunner.

The scriptrunner commented functionality is explained in https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_commented
You will notice that this function does not actually let you search for the content of the comment itself.  Because of that, there is not a means to nest one JQL query inside the other here.   I had thought perhaps the function in scriptrunner called issueFieldMatch might have been able to help here.  However alas, this only works for custom fields and system fields in Jira, which technically comments are neither.

What you are looking for is something I think could be done through SQL.   To that end, I came across a feature request for Jira to search based on comment date in https://jira.atlassian.com/browse/JRASERVER-44498 while this was closed as something that won't be implemented into Jira, the good part is that in the comments another user posted about the SQL for Jira plugin that can help you to use SQL search syntax in JQL queries:

Example with SQL for JIRA.

issue in sql("
select i.key
from issues i inner join iissuecomments c on i.id= c.issueid and jql='reporter=currentUser()'
where c.created between PARSEDATETIME('2015-06-13','yyyy-MM-dd') and PARSEDATETIME('2015-06-14','yyyy-MM-dd')
")

Granted that user that made the comment might very well be the vendor of that plugin (full disclosure is appreciated), but it does look more promising.  It's also possible there are other plugins for Jira that might help in this regard.

[Tarun Sapra]

Thanks @Andy Heinzer for such an nice explanation !