Using the changelog to return date of a status change

[Sandy King]

I'm writing a slack bot that will notify users in the morning of their pending items in review and how log they've been in that status. 

The use case is for me to highlight when things have been sitting in review for too long and to prompt them gently to get them closed down. 

I've hit a bit of a wall to actually retrieve the date of the last status change into a smart value so I can use a date diff check 

Using pythin I know I can request the information from the API for a specific tickets info. I can see the created date being returned, but having trouble trying to access this through JIRA automation? 

There is a work around where I can log the date in a hidden field when an Item goes to review and automate the clearing it should it pass /fail. However I have thousands of tickets in without that field so would prefer to use the changelog and be a little more dynamic. 

Currently I've got: "{{changelog.status.last.created}} = varStatusDate" but can't get it to return anything in the log

The final calculation would be: {{now.diff(varStatusDate).businessDays}} to return the difference between now and the timethe issue was last moved to that status. 

Any help would be appreciated

[Kalyan Sattaluri]

Hello @Sandy King 

Can you share what is the trigger for your rule?

{{changelog}} smart value will return data if your rule trigger is tracking if there are changes to issues, like Field value changed or issue transitioned..

If you are running a scheduled trigger, {{changelog}} smart value will not return any info. So this may be what you are observing.

Also, Yes, you can make a REST API call for each issue, but, at least in Data center, I have not been able to access the "date" field when a status change has happened.. For example, I made a rest call to this end point for sample issue:

https://mydomain.net/rest/api/2/issue/KPMS-281?expand=changelog

and based on the structure of the changelog JSON, I tried to log below smart value:

{{#webhookResponse.body.changelog}}

{{#histories}}

{{#if(and(equals(items.field,"status"),equals(items.fromString,"To Do")))}}

{{created}}

{{/}}

{{/}}

{{/}}

and I expected above value to return me the date issue was transitioned from To Do, but I was not able to get the date field. I will check the cloud behavior later today but I am expecting the same..

Either way, even if it works in Cloud, do you really want to make an API call for each issue to get that date? Reason I say is, issue can be transitioned forward and back... its just unnecessarily complex...  As you have said, maybe storing the transition date in an unused field is the simpler approach..

In the mean time, I think instead of trying to capture exact days since issue wasnt updated, you can use JQL (as discussed here - LINK) to get a list of issues of interest every day

PROJECT = X AND NOT Status Changed AFTER -10d AND Created < -10d AND Status in (X, Y, X)

 and you can send this list out and you know the # of days its been since you are using it in your query..  I will come back if I find a way (which I doubt cause I have looked into this before.)

Hope it helps.

[Kalyan Sattaluri]

I meant, use a scheduled trigger, give a similar JQL like above and then you can send your slack notification.. Please search for forums on similar enhancements to the JQL.