[DORA Metrics][Median Lead time for changes] configurable statuses for start and end time

[dwaghulade]

Search before asking

• [X] I had searched in the issues and found no similar feature requirement.

Use case

Our report is showing very high number and when reviewed query and results, found that it is considering time between JIRA ticket created and when ticket is resolved/closed. In our case, we are looking for time between status changed to In Dev and changed to In QA/IN UAT.

Description

Need below considerations for "Median Lead time for changes" Dora metrics

• the start time of Change lead time should be: the time a ticket changed to In Dev status?
• while the end time of Change lead time is: the time a ticket changed to QA/IN UAT status?

Related issues

No response

Are you willing to submit a PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[Startrekzky]

Hi @dwaghulade , the change lead time defined in DORA is between

• start time: PR's first commit's authored date
• end time: commit gets deployed to the production environment

Please refer to this doc: https://devlake.apache.org/docs/Metrics/LeadTimeForChanges#how-is-it-calculated

The example you wrote is the definition of another metric in DevLake, which is Issue Lead Time.

My understanding is that you want a metric that measures the duration between the time a ticket changed to In Dev status and the time a ticket changed to QA/IN UAT status. I think it's doable if you have the data collected.

May I ask how can we get the two time? Through issue changelogs, or do you have two fields to log these?

[dwaghulade]

Thanks @Startrekzky for the reply. Let us check Lead Time for changes metric.

We are also looking for Issue Lead time metric. We are using Jira for issue tracking where we have different statuses like New, In Dev, Analysis & Design , In QA, IN UAT, Ready for deployment, Closed, Done etc. We would like to consider start time when ticket moved to In Dev status in Jira and end time as when status moved to In QA. Currently it is considering ticket creation time as a start time and resolution (closed/Done) as end time.

Not sure in which table JIRA status change logs are being stored.

[Startrekzky]

@dwaghulade If you already have Jira issues collected, can you query:

select * from issue_changelogs where issue_id = 'An id in table issues'

to check if a Jira issue's entire status transition logs are collected?

[dwaghulade]

Yes, Jira issues are collected which can be confirmed on the dashboard. Please refer to below screenshot. However "select * from issue_changelogs where issue_id = 'An id in table issues'" query is returning empty result.

@.***

@.***

Regards, Dhiraj Waghulade| Project Manager, LTIMindtree-EA | Desk +1 512 687 9758 | Cell +1 512-665-9513 | email: @.**@.>

From: Louis.z @.> Sent: Friday, December 8, 2023 8:44 AM To: apache/incubator-devlake @.> Cc: Dhiraj Waghulade @.>; Mention @.> Subject: Re: [apache/incubator-devlake] [DORA Metrics][Median Lead time for changes] configurable statuses for start and end time (Issue #6263)

@dwaghuladehttps://github.com/dwaghulade If you already have Jira issues collected, can you query:

select * from issue_changelogs where issue_id = 'An id in table issues'

to check if a Jira issue's entire status transition logs are collected?

- Reply to this email directly, view it on GitHubhttps://github.com/apache/incubator-devlake/issues/6263#issuecomment-1847301898, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BDLG5Q4VUE3VNBQ72CGDRMTYIMRTJAVCNFSM6AAAAAA6EI5TL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNBXGMYDCOBZHA. You are receiving this because you were mentioned.Message ID: @.**@.>>

[Startrekzky]

@dwaghulade It should be collected, did you replace the 'An id in table issues' with the real value of the issue id?

[dwaghulade]

Thanks; my mistake, I didn't notice query require replacing value with actual id.

Yes, issue logs are getting collected. In below example, we would like to consider time between when original_to_value changed to In Dev and changed to In QA. Current report is referring time between ticket created and to_value changed to DONE. When we refer status it is just considering TODO, IN_PROGRESS & DONE.

@.***

Regards, Dhiraj Waghulade| Project Manager, LTIMindtree-EA | Desk +1 512 687 9758 | Cell +1 512-665-9513 | email: @.**@.>

From: Louis.z @.> Sent: Tuesday, December 12, 2023 4:02 AM To: apache/incubator-devlake @.> Cc: Dhiraj Waghulade @.>; Mention @.> Subject: Re: [apache/incubator-devlake] [DORA Metrics][Median Lead time for changes] configurable statuses for start and end time (Issue #6263)

@dwaghuladehttps://github.com/dwaghulade It should be collected, did you replace the 'An id in table issues' with the real value of the issue id?

- Reply to this email directly, view it on GitHubhttps://github.com/apache/incubator-devlake/issues/6263#issuecomment-1851714583, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BDLG5Q4DWD62VEQFZAHTD23YJATQJAVCNFSM6AAAAAA6EI5TL2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNJRG4YTINJYGM. You are receiving this because you were mentioned.Message ID: @.**@.>>

[dwaghulade]

Any updates on above request please?

[Startrekzky]

Yes, issue logs are getting collected. In below example, we would like to consider time between when original_to_value changed to In Dev and changed to In QA. Current report is referring time between ticket created and to_value changed to DONE. When we refer status it is just considering TODO, IN_PROGRESS & DONE.

Hi @dwaghulade . In your case, you have to write your own query to get the result. The logical steps are:

1. Join table issues with table issue_changelogs to get all issues' time of transitioning to 'In Dev' and time of transitioning to 'In QA'. You should handle the situation for issues being changed to 'In Dev' or 'In QA' multiple times.
2. Diff the two time-fields to get the lead time of every issue.
3. Calculate the median lead time of all issues

It's a complicated SQL so you can follow the data schema and metric examples to figure it out.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.

[github-actions[bot]]

This issue has been closed because it has been inactive for a long time. You can reopen it if you encounter the similar problem in the future.