JQL on a given date does not return the same number of issues as in the Burn-up chart (Sprint start)

David Leal_28ge8x

We are executing the following query to obtain the issues pending at the beginning (2019-02-5) of the Sprint with ID: 1185 (Sprint 12):

project = ATHP AND issuetype in (story, feature, task) AND Sprint = 1185 AND (status was not in  (DONE, "BUILD COMPLETE") ON "2019-02-05" ) ORDER BY created ASC

We got 79 issues pending and 22 of them are in DONE status. So it shows the current status and not the status at a given time. Q1: Is there a way to have the status on a given day?

Now when I am trying to double check the result from the Burn-up chart I got: 78 issues (not 79). Looking into more details: we don't get the same issues. Here is the detail:

• We have 19 issues in the Burn-up chart that are not in the Query
• All of them assigned to a future Sprint.
• In the burn-up they are represented like this: "XYZ-XXXX undefined", so instead of having a summary it says: undefined. When I click on all of them, they are assigned into a future sprint, and there is no information they have been part of the Sprint 12 in the Sprint field. Looking at the history they were in the Sprint 12 and then moved into future Sprint, but the Sprint field does not have traceability about previous Sprints. I guess that information only shows up if the issue was carried over into the next Sprint once the Sprint closed, but not for issues moved into a future Sprint while the Sprint is still open. Based on the burn-up information the issue was moved in the middle of the sprint. Q2: Why those issues are not extracted from the query since they were at a given day part of this Sprint not an in DONE or BUILD COMPLETE status?
• We have 20 issues as the result of the query that is not part of the initial list of issues in burn-up.
• Those issues have been part of the Sprint 12, but they were added in the middle of the sprint. Q3: Why those issues were returned since they were not part of the beginning of the Sprint.

I guess I am missing something in the query in order to get exactly the same information as it is in the Burn-up chart once the sprint started.

In summary the query:

• Includes issues added to Sprint 12, that were not part of Sprint start
• Excludes issues that were part of the sprint start, and then moved.

Any help to update the query so I am getting the same number of issues as in the burn-up chart.

David Leal_28ge8x

I am trying to respond to myself: Reading again the JQL specification for operators about when to use ON clause. It seems that we need to use it only in combination with other operators to identify changes for example: WAS, WAS IN, WAS NOT, WAS NOT IN or CHANGE. To identify changes in the status for example, but not alone as I was  trying to use it.

Therefore it does not capture the exact situation at the beginning of the Sprint. Instead it includes issues added later on to the Sprint or it excludes issues that were at the beginning of the Sprint but were removed later on. Probably in order to use for the purpose I wanted it will require a New Jira request (enhancement) to include it.

Here is the solution I found in order to find all issues at the beginning of the Sprint using the JIRA REST API.

The following URL allows to obtain all the information related to a board with $BOARD_ID$ and for a given Sprint with ID: $SPRINT_ID$:

"/rest/greenhopper/1.0/rapid/charts/scopechangeburndownchart?rapidViewId=$BOARD_ID$&sprintId=$SPRINT_ID$"

The Sprint ID and board Id can be found in the URL of the burndown/burnup charts in JIRA. For each board created in the project will have an ID for type of report. This number is the same for all sprint of the same board.

The above end point for greenhopper API is not formally documented by JIRA. There is a suggestion here for JSWSERVER-12877  about to document it since 2015! it has more than 28 votes, it seems they need more.. Therefore this end point can change in the future without notice.

Here is an example of the JSON string returned by JIRA REST API:

The starTime has the time the Sprint was initiated since epoch (1970/1/1) in milliseconds. Therefore we know when the Sprint started. Then the node: changes has the list of changes occurred in the Sprint, including when the issue was added to the sprint. The changes nodes has a map of different changes. For issues added the form is like this for example:

 "1548166624000": [

      {

        "key": "XYZ-3846",

        "added": true

      }

    ],

Where: 1548166624000 represents the timestamp in milliseconds since epoch when the change occurred. When added is true the issue was added to the Sprint and if it false the issue was removed from the Sprint. This property does not have to present in all changes, only for issues added or removed.

The condition for finding all issues at the beginning of the Sprint is finding all changes where the timestamp of change is lower or equal the startTime and the property: added is true. The reason the timestamp of change can be lower than startTime of the sprint, is because it considers the time when the issue was added and it could happened before the sprint started.

Parsing the JSON string allows to find all issues at the beginning of the sprint and then we can invoke a query: "key in (XYZ-3846, XY-0010…)" with all issue keys for getting the issue information.

I parsed it in VBA for loading the information in excel file. I used the VBA-JSON parser from github. The JsonObject("changes") returns a list of changes in a dictionary object, where the key is the timestamp.

• Each timestamp key has as value a Collection (it can be more than one issue that has changed at a given timeStamp)
• Each element of the Collection represents a another dictionary reporting the information that has changed using pairs of: [key, value].
• If the issue was added one of the key of the given dictionary will be "added" and its value can be true or false.

Parsing all the above element allows to identify all issues at the beginning of the sprint. The status of the issue will be the current status. I was not able to find a way to obtain the status the issue had at the time the Sprint started. 

I hope it helps other member of this community with a similar problem.