Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Measure the refinenement in the description of a User Story

Hi all!!

 

I would like to know if it's possible to have a query to measure the amount of times that the field description is edited in a user story.

Thanks in advance,

 

 

Alex

1 answer

0 votes
Andy Heinzer Atlassian Team Dec 09, 2019

Hi Alex,

If I understand your request here, you're looking to find out the number of times that a description has been changed for an issue in Jira.  Within Jira itself, I don't believe this is something that the native JQL search can tell you.

You could bring up each issue and then click the History tab to manually review changes that happen to that issue. This is one way to find that information.  But there is not a quick way to see this for multiple issues in Jira at the same time.

That said, I think this is something that you can also find if you access to query the SQL database that Jira is using (Sorry Jira Cloud users, you can't do this).  We can start with the base SQL queries found in the KB Retrieve issue change history from database in Jira server.

I tweaked that query to be this:

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR as "user_key", cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id
inner join app_user au on cg.author = au.user_key
WHERE ci.field='description' AND cg.issueid=(select id from jiraissue where issuenum = 27 and project in (select id from project where pname = 'Sample Scrum Project'))
order by 1,3,4;

In this example, my project name is 'Sample Scrum Project' and the issue key was SSP-27.  You just need to enter the project name for the pname value, and the issuenum '27' in my case.   When I run this query in postgresql, I get back a list of all the times the description field was changed after the initial creation of the issue.  Just note that when the issue is first created, that initial creation is not shown by this query, only changes to the description field of that specific issue will be returned by this query.

If you're not worried about what the actual changes are, and just want to see the number of changes to that field for a specific issue, we can tweak the query further to just be:

SELECT count(*) as "change_count"
FROM changegroup cg
inner join jiraissue i on cg.issueid = i.id
inner join project p on i.project = p.id
inner join changeitem ci on ci.groupid = cg.id
WHERE ci.field='description' AND cg.issueid=(select id from jiraissue where issuenum = 28 and project in (select id from project where pname = 'Sample Scrum Project'));

Which just returns a count of the number of changes to that field after initial creation.

 

I hope this helps.

Cheers,

Andy

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you