Piping inm JQL.

[Ashok Bohra]

Is it possible to pipe the result of 1 query to another query in JQL. 

e.g. 1st query show all the issue for which work has been logged after day 1. 

2nd query Sum of the work logged only in the issue in result of query 1. 

[Earl McCutcheon]

Hello @Ashok Bohra ,

Thanks for reaching out and while this one seems on the surface like a straight forward question it is actually a very complicated answer with a lot of different approaches that need to be considered and can take different solutions. I believe I have a good workaround for you but I have outlined a lot of information below and please do let me know if you need clarification on ay of this as it is a giant wall of text to sort through.

Only a portion of this can be accomplished for what you are looking for natively in the application, as JQL results can only return a set of issues that meet a defined criteria in the search paramaters, but would not be able to do additional logic like sum up values from those results or compare two different fields agains one another.

First and foremost I believe the majority of what you are looking can be accomplished using the the third party application ScriptRunner as discussed in the following thread, as noted that JQL does not let you compare two date fields, but ScriptRunner add-on does via a built in script:

• https://community.atlassian.com/t5/Jira-Core-articles/How-to-find-issues-where-one-date-field-is-greater-smaller-equal/ba-p/1031391

Alternative functionality you can look into without a third party app would still be done externally requiring either custom scripting or importing data to a spreadsheet application. 

For custom scripting would be to write a custom script locally to call the data from the API and then parse the data from the results.

For spreadsheets you also have a few options.  One is to pump data from Jira into Google sheets using the Free add-on from Atlassian Jira Sheets for Cloud, OR  create a export of JQL results to use in a Excel or open office spread sheet and then you would be able to to use all the spreadsheet capabilities in the alternate platform. 

For both of these methods I gave some examples in the following posts to similar methods for pulling some worklog data in this manner, while it does not line up exactly with what you are looking for its good details on how to pull that data from the API or into the spreadsheets as a starting point for ideas on what is involved:

• https://community.atlassian.com/t5/Jira-questions/How-to-access-total-time-logged-per-release-outside-of-Jira/qaq-p/1185180
• https://community.atlassian.com/t5/Jira-questions/Use-the-API-get-the-sum-of-the-remaining-estimate-for-all-issues/qaq-p/1364702

But before going to far into the spreadsheet functions  lets take a step back to the first requirement you had which includes native functionality for linking multiple filters together.  This can be accomplished using Saved Filters and link them together to create multilevel filters. 

The process would be to create multiple saved filters, and for a super simple example The formatting would be:

Saved Filter as "Example Filter 1":

project = EXE

Saved Filter as "Example Filter 2":

Component = test

then you can link the two individual filters using:

filter = "Example Filter 1" AND filter = "Example Filter 2"

This would in effect build out a filter equivalent to:

(project = EXE) AND (Component = test)

This again could be saved as another filter and used in the same way.  However I do not believe this would help you in the long run looking at the requirements you are looking for as you should be able to accomplish at part of this natively but will require using either the google sheets add-on or another spreadsheet application in a different approach entirely to get everything you ar looking for, I will focus on the google spreadsheet method below.

For the first Requirement:

1st query show all the issue for which work has been logged after day 1. 

With the Worklog you cannot compare it directly against the created date field natively in Jira as discussed in the alternate post above where Scriptrunner was an option for a solution in this matter.  Natively in Jira  the JQL only allows for comparing dates to a hard set or relative date or date range based in the filter directly like the following:

workLogDate <= startOfDay(-10d)

OR

worklogDate <= "YYYY/MM/DD"

See the documentation at the following link for more details on the avaliable syntax:

• What is advanced searching in Jira Cloud?
  • Advanced search reference - JQL functions
  • Advanced search reference - JQL fields
  • Advanced search reference - JQL keywords
  • Advanced search reference - JQL operators
  • Advanced search reference - JQL developer status

So If we wanted to look at this in Google sheets capabilities, I would Recommend that you pull in all issue data for the project including all issues where the worklog is not empty (There is a limitation here as well i will explain below) then expand the subfields for worklog  using a combination of the following options as needed but primarily  Worklog.started and compare it against Created date using a spreadsheet formula in a secondary page:

• Log Work
  • worklog
    • authorDisplayName

      • worklog.authorDisplayName

    • authorEmail

      • worklog.authorEmail

    • comment

      • worklog.comment

    • created

      • worklog.created

    • started
      • worklog.started
    • timeSpent

      • worklog.timeSpent

    • timeSpentSeconds

      • worklog.timeSpentSeconds

    • updated

      • worklog.updated

The Worklog data is represented as a Log Work field, but is not a specific and direct field but rather meta data associated to time tracking combined in export data as if it were a field, and it can only be partially queried against as detailed above.  The Worklog cannot be queried against a "IS" or "IS NOT" empty value, but the following JQL can be used to bull in all issue where there is no worklog associated by simply covering everything in a relative date scope of all time:

(worklogDate <= -1d AND NOT worklogdate > -1d)

My associate gives a really good breakdown of this query and how it works in the following thread but the intent will show issue where worklogs are populated but not within the last 24hours covering the first one day gap you mentioned then identify the issue where no work has been done:

• https://community.atlassian.com/t5/Jira-questions/Return-true-if-work-has-NOT-been-logged-in-the-last-24-hours/qaq-p/673906

Next put that all together and you can pull the data to the sheet using Jira Sheets for Cloud with the following formula:

=jira("project = POTATO AND (worklogDate <= -1d AND NOT worklogdate > -1d)", "issuekey,issuetype,createddate,worklog,worklog.started,worklog.timespent,worklog.timespentseconds", 0, 10)

Here is a example with sample data of what that looks like:

Then Within the spreadsheet there are formatting limitation you will need to address on how the data comes over noting that the Created Date of the issue is formated as a date MM/DD/YYY, and the worklog dates are going to come through with all the meta data associated to it.  you can use the following formula in the sheet against the  worklog.started column in the formula as column E as pulled from the above query:

=DATE(LEFT(E2,4),mid(E2,6,2),mid(E2,9,2))

And from this point you can compare the worklog entries to the started dates in subsequent queries with boolene operators to discover additional details for youyr report,  with the example in the screenshot identifying where H (worklog.started) is greater than C (Created)  Noting POTATO-138 from that list was the only issue that carries a time entry on the creation date, and you can build out additional comparisons  and use =sumif queries to create the xummation against the worklog.timespentSeconds column to get the final time tally for issues meeting the necessary criteria you are looking for

Hope this helps and again let me know if you have additional questions in the functions avaliable in the tools.

Regards,
Earl