JQL on a given date does not return the same number of issues as in the Burn-up chart (Sprint start)
1 answer
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.