curl versus Python versus Export: no simple solution?

[jef]

I need to connect Jira data to an external data source to get a complete picture of all the efforts underway.  I have tried multiple solutions just to get the Jira data, each ending up overly complicated.

1. Build a filter & dashboard, then export to CSV - I can create a dashboard with the records and columns that I want, but I can only export 1,000 records at a time.  Given that there can be over 10,000, this is too manual to do on a routine basis as the filtering to break it into small bits is too cumbersome.
2. I tried to read the data via Python.  No matter what I try, though, I get the dreaded SSL error "max retries exceeded" that I've seen posted in a variety of places.  None of those solutions ever eliminates the error.
3. I've successfully connected and retrieved information via curl, saved the resulting response to a json file then read it into Python ... sort of.  The resulting data gives way too many columns which are mostly empty and several columns yield a dictionary within a dictionary and sometimes within a dictionary.  This is in contrast to the various curl examples I've seen where the example shows data that is fine for an example: less than 5 records with less than 10 columns and no nested dictionaries.  I've been able to slowly build out a curl that limits the data but is still no where near as simple as the CSV export I can get with #1.

I don't mind using curl to retrieve the data but it would be nice to just keep everything in Python.  Unfortunately the system admins aren't expert in the security intricacies so they pointed me here, to the Atlassian community boards.  If I have to use curl, that's fine, but I find I'm building it out by trial and error by googling a question, trying something, googling a different question, getting more info and refining, repeat until done ... which seems a long way off.  Even then, I'll probably have to do the same thing in Python to read the JSON output.

There has got to be a better way, I hope.  If it helps, below is the curl request I've built up so far (minus anything proprietary, of course).  Any wisdom or advice is greatly appreciated.

curl -D- -H "Authorization: Basic xxxxxxxxxxxxxx==" -X POST -d "{\"jql\": \"'project' = 'MYPROJ' AND 'issuetype' = 'Milestone'\",\"fields\":[\"key\"]}" -H "Content-Type: application/json" --url "https://theprocess.thecompany.com/rest/api/2/search"

[Prince Nyeche]

You have to build your output data structure first if you're going to connect Jira to an external data source. If you want a flat CSV file, you will need to know the exact fields you want to be placed on that file. An easy way to do this in python would be

1. Define your output data structure. i.e. name of fields you want present.
2. Run a JQL API search for the issues and retrieve the payload from each issue of the search.
3. Curate this list of data from each issue and add them into the above structure. i.e. putting it in a list is better in an ordered sequence.
4. After the search is complete, sort the data (if needed) and send it as output to create the data into a CSV file as needed.

[Prince Nyeche]

Just thinking about this, I came up with this script as an example which is completely expandable based on what fields you want.

[Nic Brough -Adaptavist-]

So, on point 3, there's a simple answer - tell it what fields you want to fetch (if you don't, Jira assumes "everything").  See https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-get and look for the "fields" parameter.

For the "keep it in python", that's not really an Atlassian question, but I have "learn python" on my to-do list, and I've wandered through several sites that try to explain how to do some things in python.  I can't say this is "the best" or even that I've tried to follow it, I've just bookmarked it for later, but https://www.nylas.com/blog/use-python-requests-module-rest-apis/ looks like a quick way to get started using REST APIs in Python.

[jef]

Thanks, Nic, for the reply.  I tried the suggestion.

Here is what I used:

curl -D- -H "Authorization: Basic xxxxxxxxxxxxxxxx==" -X POST -d "{\"jql\": \"'project' = 'MYPROJ' AND 'issuetype' = 'Milestone'\",\"fields\":[\"key\", \"status\"] }" -H "Content-Type: application/json" --url "https://xxxx.yyyy.com/rest/api/2/search"

What I got back was more than I hoped.  Here is the beginning of the response:

{"expand":"schema,names","startAt":0,"maxResults":50,"total":11364,"issues":[{"expand":"operations,versionedRepresentations,editmeta,changelog,renderedFields","id":"541422","self":"https://xxxx.yyyy.com/rest/api/2/issue/541422","key":"MYPROJ-12345","fields":{"status":{"self":"https://xxxx.yyyy.com/rest/api/2/status/10000","description":"","iconUrl":"https://xxxx.yyyy.com/images/icons/subtask.gif","name":"To Do","id":"10000","statusCategory":{"self":"https://xxxx.yyyy.com/rest/api/2/statuscategory/2","id":2,"key":"new","colorName":"blue-gray","name":"To Do"}}}}

Any thoughts on how to get this down to just 

"key":"MYPROJ-12345","status":"To Do"

Your time and patience is greatly appreciated.

[Nic Brough -Adaptavist-]

Have you tried adding the fields parameter as described in the first link I gave?

It will still return the metadata in the JSON, but you can easily trim that out in your code.  I often work with shell scripts rather than python, using a simple executable for extracting raw data from json responses - it's called "jq" and I understand there's a library for python that does the same thing (pip install jq)