Measure the refinenement in the description of a User Story

[Alex]

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

[Andy Heinzer]

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