Extracting Info

[Andrew Scharhag]

Version: (v5.2.9#852-sha1:71473fa)

I am tying to pull data out for some analysis. How might I pull out the dates that specific tickets paseed through a specific point in our JIRA workflow? Thanks!

[CEDRIC ZABEL]

You probably need to be a little more specific about what you want. For example, if the number of specific tickets is small enough, you could just look at the Jira issue pages and copy the data you want. But I’m sure you knew that, so you must have a lot of them. But do they change all the time? Do you need this automated periodically?

You could also gather the data through the REST API. For example

rest/api/2/search?jql=key=ISSUE-12345&expand=changelog

will get you a JSON formatted list of the change log of ISSUE-12345, and then you can write some script or something to find the date of the transistion you want.

You could also directly access the database to get the changelog. You’d find the internal issue ID from the jiraissue table, link to the changeitem table to find the transition you want, and link from there to the changegroup table to find the date.

[Andrew Scharhag]

When executing the below search I recive the following error:

Search: "Key=ISSUE-12345 AND expand=changlog"

Error: "Field 'expand' does not exist or you do not have permission to view it."

I belive the data exists in changelog. Any advice? is 'expand' proper wording?

Thanks!

[CEDRIC ZABEL]

No, that’s not how it works. The “expand=changelog” is not part of the JQL. It’s an argument to the REST resource.

Perhaps a more concrete example will help. We can use https://jira.atlassian.com/as our Jira instance. Let’s say that you want to get the changelog for JRA-222 and JRA-333. We need a search that will return those issues. A simple search that will work is

key in (JRA-222,JRA-333)

You can actually use any sort of search. So, we plug that into our REST resource and get

https://jira.atlassian.com/rest/api/2/search?jql=key%20in%20(JRA-222,JRA-333)&expand=changelog

You’ll notice that we had to URI encode the JQL.

If you access the contents of that URL, you get a bunch of text back. You can copy that URL into your browser and see it. The text is in JSON format, which is machine parseable. It contains all the information Jira has about our two issues, including the complete changelog.

I have a Mac, so I can go into the terminal and type

curl -o - 'https://jira.atlassian.com/rest/api/2/search?jql=key%20in%20(JRA-222,JRA-333)&expand=changelog'  | python -m json.tool

and that will output the JSON in a nicer format.

[Andrew Scharhag]

I do not have access to DB however in below case can we get the information out of JQL or some other way from advanced search.

We have a workflow designed in our project in which issues flow as below.

Open >> In Progress >> Resolved >> Release Pending >> Release Scheduled >> Closed.

Now at given point of time we like to know the date on which issue is moved to “Release Scheduled”. Note that currently issue could be in either “Release Scheduled” or “Closed” status.

We can get “Last Closed Date” but we wanted to know the date on which issue moved in “Release Scheduled” status.

Is there a way in JQL we can do it from advanced search filter of JIRA?

[CEDRIC ZABEL]

If you want to do it with a JQL query, you’d have to have some field that would have the date on which the issue is moved to “Release Scheduled.” There isn’t such a field in the standard Jira.

If you can add custom fields, then you could create such a field. You could code the Java yourself, or you could use the Script Runner plugin to script it.