Exporting Comments only for the Status Change for all Issues.

Santhosh Raj_ur59s4

Hi Team,

I need to export the History of Issues in some Data from Jira to Sheet, csv or xlsx format.

 

In here this shows history of what are all being updated and status changed.
So we need to specifically take the Data and the date if possible, So we can create a Dashboard in Power BI.

I'm not able to find any idea for this as comment is showing in filters.
If we need to take all the comment history and the date of history changes, How can we do it ?

If it helps then it would be useful ?
Also we are using "Power BI connector for Jira" Plug-in, but not able to get the Comment history.

Please let me know if there is any way ?

Miguel Ramon

Hello @Santhosh Raj! Miguel from ServiceRocket here. To my knowledge there is no built-in way to export all comments and issue history to a csv / excel without leveraging a third party app like this one, for example (Not associated to that vendor).

The other approach you could do is use the REST API and a script that can populate a csv file for you.

You can build a script that queries the /rest/api/3/issue/{issuekey} endpoint. "Use the ?expand=changelog" parameter to reach the history section of the issue. 

This is a python script that may be helpful, I used this to log certain info from issues given a certain status ID was present in the issue history. Create a csv file with a list of all issues you want to run through the script (It's not really a csv, just put each issue key and then a newline). NOTE: You will need to tinker around with it to get to the info you need!

 

import json
import requests
from requests.auth import HTTPBasicAuth
import csv

# User Auth Info and Base URL goes here

user = "<your_user_email>"
token = '<your_api_token>'
auth = HTTPBasicAuth(user, token)
baseurl = "<instance_base_url>"

## Specify the ID of the "Done" Status you're looking for

donestatusid = '10053'

######################################################################
######################################################################

# Enter the name of the CSV file with the issuekeys you want to fetch
issues_csv = 'testbulkeditorigissuekeys.csv'
######################################################################

# Open csv file with list of issues
file = open(issues_csv, newline = '')

# Pass list of issues to variable
issuekeys = csv.reader(file, delimiter=',')

# Create csv file to store issue info
csv_file = open("issuestatuschange_AAN_Canceled.csv", "a", encoding="utf-8")

# These are the CSV columns - edit as needed.
# Note that for CSV imports, issuekey, project key, project type and summary are essential
csv_file.write("issuekey;ProjectKey;ProjectName;ProjectType;Summary;Resolution;StatusChangeDate")
csv_file.write("\n")

# Establish API session
s = requests.Session()
s.auth = HTTPBasicAuth(user, token)
s.headers.update({
    'Accept': 'application/json',
    'Content-Type': 'application/json'
})

# Search for issue information. In this case, we expand the changelog section to look for
# status change information
for row in issuekeys:
## ', '.join(row) adds in the issue key from the csv file. URL will look like:
## <base_url>/rest/api/2/issue/SSD-123?expand=changelog>

    url_max = baseurl + "/rest/api/2/issue/" + ', '.join(row) + "?expand=changelog"
    print ('Fetching history for ' + ', '.join(row))
    response = s.request(
            "GET",
            url_max,
            auth=auth
        )

    # Store response in a variable
    d = response.json()

    # In this particular case, we need to search the issue history for a particular status change.
    # Use this as needed. "10014" is the ID of the status we're looking for
    # "Done" is the status name and is a second check after finding the ID, "just in case"
    # After finding that, we store the "created" date which is what we need
    # This block can be commented out if you do not need to find values within json lists

    for i in range(len(d["changelog"]["histories"])):
        if (d["changelog"]["histories"][i]["items"][0]["to"] == donestatusid
        and d["changelog"]["histories"][i]["items"][0]["toString"] == "Canceled"):

            dateChanged = d["changelog"]["histories"][i]["created"]
 
    # Print for debug purposes
    ## print(f'{d["key"]},{d["fields"]["project"]["key"]},{d["fields"]["project"]["name"]},{d["fields"]["project"]["projectTypeKey"]},"{d["fields"]["summary"]}",{dateChanged}')

    # Write to CSV file
    csv_file.write(f'{d["key"]};{d["fields"]["project"]["key"]};'
        f'{d["fields"]["project"]["name"]};{d["fields"]["project"]["projectTypeKey"]};'
        f'"{d["fields"]["summary"]}";Answered;{dateChanged}')

    csv_file.write("\n")

    print(f'{d["key"]};{d["fields"]["project"]["key"]};'
        f'{d["fields"]["project"]["name"]};{dateChanged}')

csv_file.close() 

 Considerations:

- I used semicolons as the delimiters in this particular script for the csv but you can switch back to commas.

- The comments are found under d["comments"].

Hope this helps!