Exporting Comments only for the Status Change for all Issues.

Santhosh March 13, 2024

Hi Team,

I need to export the History of Issues in some Data from Jira to Sheet, csv or xlsx format.Screenshot 2024-03-13 181341.pngScreenshot 2024-03-13 181535.png

 

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 ?

2 answers

1 vote
Miguel Ramon March 13, 2024

Hello @Santhosh! 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!

Santhosh March 13, 2024

Time in Status for Jira - Reports | Charts | Dashboard 

Is this be plugin be useful @Miguel Ramon 

Also I need some suggestions like What all can be achieved via Script runner plug-in ?

Miguel Ramon March 13, 2024

Hey @Santhosh - I think what you need for this is the Export Issue history app I listed above. You can also achieve what you're looking for with scriptrunner, since you'll also be able to run requests against the Jira Cloud REST API, just depends what's your preferred scripting method.

Yuliia_Borivets__SaaSJet_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 13, 2024

Hi @Miguel Ramon 

Thanks for mentioning the Issue History for Jira app from my team.

Hi @Santhosh, you can read about export in the documentation. It's easy to do with two button clicks.

Also, you can check this article: How to export issue history from Jira?

0 votes
Amay Purohit_RVS
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 13, 2024

Hi @Santhosh 

As an alternative , you can try out our add-on for better reporting capabilities and viewing multiple issues history at once, 

Issue History Reports

You can view the complete change log for your filtered issues, including changes to comments. Also this report can be exported to a csv file.

Disclaimer : I am part of the team which developed this app

Issue History.PNG

Santhosh March 13, 2024

Our expectation is to find the Date/Time Differences from one Status to Another Status and we need all those tickets to be exported as excel/csv. 

Just alone the Time/Date difference would be enough.

By that we can capture Jira Dashboards and Power BI Dashboards

@Yuliia_Borivets__SaaSJet_ @Amay Purohit_RVS  @Miguel Ramon 

Amay Purohit_RVS
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 14, 2024

Hi @Santhosh 

I would suggest to try out our other add-on for status changes details.

Time in Status Reports 

You can easily export this data to Power BI from our app itself.

We can schedule a demo call if needed, support@rvsoftwares.in 

TIS - Status Transition Dates.PNG

 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events