[Feature][JIRA/DORA] Incident mapping

[GSoul-hub]

Search before asking

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

Use case

As a QA lead, I want to be able to compare the Scrum team's efficiency using DORA.

Description

At the moment, devlake allows mapping only issue type (e.g. "Incident" maps to the "Bug" of JIRA issue type).

Our current configuration of JIRA:
"Bug sub-task" (a.k.a. Sprint bug) - a bug is caught during the testing of a new feature (sprint), we can clearly associate it with the feature, so we create it as a sub-task under the feature's main task.
"Bug" (a.k.a. Regression/Release bug) - a bug is caught during regression testing before release to a production environment. Since it is not unequivocally clear which feature influenced the appearance of this bug - it is registered as a separate issue (not a sub-task).
"Bug with JIRA label "production" - a bug is caught in the production environment (by the team, clients, or customers).

As I understand, DORA should include only "production bugs", but devlake configuration supports only issue type and can't be separated by labels. In our cases - regression and production issues will be treated as DORA incidents which will not be correct (shouldn't include regression bugs).

We have multiple teams in our company and as a result, changing/agreeing to the jira configuration setup is quite a long process (e.g. introducing separate issue type only for production bugs).

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

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

[Startrekzky]

Thanks @GSoul-hub , I agree that the definition of BUG/INCIDENT needs to be more flexible. I'll think about it.

[github-actions]

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

[GSoul-hub]

Any update?

[Startrekzky]

Hi @GSoul-hub , I'll give you an update by the end of this week.

[Startrekzky]

Hi @GSoul-hub , I haven't thought of a general solution for this. However, we started collecting Jira issue labels in v0.16, which will be released soon. This version brings a workaround for you by changing the SQL in the DORA dashboard. Please see the following steps:

1. Go to the DORA dashboard, Edit the Median time to restore service
   

2. As you can see, the existing SQL gets incidents as below. See other examples SQL in this doc

-- Metric 3: Median time to restore service 
with _incidents as (
-- get the incidents created within the selected time period in the top-right corner
	SELECT
	  cast(lead_time_minutes as signed) as lead_time_minutes
	FROM
	  issues i
	  join board_issues bi on i.id = bi.issue_id
	  join boards b on bi.board_id = b.id
	  join project_mapping pm on b.id = pm.row_id
	WHERE
	  pm.project_name in ($project)
	  and i.type = 'INCIDENT'
	  and $__timeFilter(i.created_date)
),

...

You can update it based on your own definition of incident by joining table issue_labels. For example,

-- Metric 3: Median time to restore service 
with _incidents as (
-- get the incidents created within the selected time period in the top-right corner
	SELECT
          -- please add a distinct here
          distinct [i.id](http://i.id/),
	  cast(lead_time_minutes as signed) as lead_time_minutes
	FROM
	  issues i
	  join board_issues bi on i.id = bi.issue_id
	  join boards b on bi.board_id = b.id
	  join project_mapping pm on b.id = pm.row_id
          -- add a new join to issue_labels here
          left join issue_labels il on i.id = il.issue_id
	WHERE
	  pm.project_name in ($project)
          -- change the definitions of incidents 
	  and i.type = 'bug'
          and il.name = 'production'
	  and $__timeFilter(i.created_date)
),

...

I hope it helps.

[GSoul-hub]

Hi @Startrekzky ,

Will try, waiting for the release of v0.16.

[Startrekzky]

Hi @GSoul-hub , the beta version of v0.16.0 is coming out. You can try it out.

[GSoul-hub]

Hi @Startrekzky.
I tried this one (slightly different than yours) and it seems to be working (version v0.16.0-beta7):

-- Metric 3: Median time to restore service 
with _incidents as (
-- get the incidents created within the selected time period in the top-right corner
	SELECT
	  distinct i.id,
		cast(lead_time_minutes as signed) as lead_time_minutes
	FROM
		issues i
	  join board_issues bi on i.id = bi.issue_id
	  join boards b on bi.board_id = b.id
	  join project_mapping pm on b.id = pm.row_id
	    left join issue_labels il on i.id = il.issue_id
	WHERE
	  pm.project_name in ($project)
		and i.type = 'INCIDENT'
		and il.label_name = 'Production'
		and $__timeFilter(i.created_date)
),

but I faced another problem - any panel related with deployments do not show any data (Gitlab).

I looked through the queries (grafana->DORA) and database records and noticed that the queries expects the type to be "deployment".

and type = 'DEPLOYMENT'
and result = 'SUCCESS'
and environment = 'PRODUCTION'

in database records (in my case) this value is empty for production (do not know why)

I tried to comment/delete this line in queries, but it fixed only "Monthly deployments" panel. Other panel still doesn't work.

e.g. "Deployment Frequence" shows result as "Fewer than once per six months".

[Startrekzky]

Hey @GSoul-hub , where did you get the deployment data from? You have to configure the transformation rule to extract production values from your CI job titles via RegEx.

This is a screenshot of the GitHub transformation page, I hope it makes sense.

[GSoul-hub]

@Startrekzky my configuration looks like this:

Database records for "deploy-production":

and for some reason, "deploy-staging" is marked:

[Startrekzky]

@GSoul-hub I see.

[klesh]

I believe that the Deployment regex should be ^deploy-

[GSoul-hub]

Guys (@Startrekzky , @klesh ), I confused a bit.

In sum, our project has around ~17 jobs in the pipeline.
Some of them:

• deploy-staging
• deploy-production
• build-composer-tests
• phpunit
• behat
• phpstan
• phpcs
  and so on

Production indicates - "deploy-production"
Staging indicates - "deploy-staging".

What's wrong here?

Job "deploy-production" - exist.
If I change to ^deploy- - it will pick up a lot of other stuff (like db migration, messenger workers and so on.) + not all of them will be deployments to production.

[Startrekzky]

Sorry @GSoul-hub , I made a mistake. Based on your job names, the

• configuration for the Deployment field can be (deploy-staging|deploy-production)
• configuration for the Production field can be deploy-production or production or production$

The two configurations extracted the values of fields type and environment separately in the table cicd_tasks. I hope I've made it clear this time.

[GSoul-hub]

@Startrekzky seems good now (at least in DB)! Thanks @klesh @Startrekzky!

But DORA dashboard still shows "Deployment Frequency" as "Fewer than once per month" :/

But in DB we have records

Any ideas?

Code:

-- Metric 1: Deployment Frequency
with last_few_calendar_months as(
-- get the last few months within the selected time period in the top-right corner
	SELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) day
	FROM ( SELECT 0 H
			UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
		) H CROSS JOIN ( SELECT 0 T
			UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
			UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
			UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
		) T CROSS JOIN ( SELECT 0 U
			UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
			UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
			UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
		) U
	WHERE
		(SYSDATE()-INTERVAL (H+T+U) DAY) > $__timeFrom()
),

_days_weeks_deploy as(
	SELECT
			date(DATE_ADD(last_few_calendar_months.day, INTERVAL -WEEKDAY(last_few_calendar_months.day) DAY)) as week,
			MAX(if(deployments.day is not null, 1, 0)) as week_deployed,
			COUNT(distinct deployments.day) as days_deployed
	FROM 
		last_few_calendar_months
		LEFT JOIN(
			SELECT
				distinct DATE(finished_date) AS day,
				id
			FROM cicd_tasks ct
			join project_mapping pm on ct.cicd_scope_id = pm.row_id
			WHERE
			  pm.project_name in ($project)
				and result = 'SUCCESS'
				and environment = 'PRODUCTION') deployments ON deployments.day = last_few_calendar_months.day
	GROUP BY week
	),

_monthly_deploy as(
	SELECT
			date(DATE_ADD(last_few_calendar_months.day, INTERVAL -DAY(last_few_calendar_months.day)+1 DAY)) as month,
			MAX(if(deployments.day is not null, 1, 0)) as months_deployed
	FROM 
		last_few_calendar_months
		LEFT JOIN(
			SELECT
				distinct DATE(finished_date) AS day,
				id
			FROM cicd_tasks ct
			join project_mapping pm on ct.cicd_scope_id = pm.row_id
			WHERE
			  pm.project_name in ($project)
				and type = 'DEPLOYMENT'
				and result = 'SUCCESS'
				and environment = 'PRODUCTION') deployments ON deployments.day = last_few_calendar_months.day
	GROUP BY month
	),

_median_number_of_deployment_days_per_week_ranks as(
	SELECT *, percent_rank() over(order by days_deployed) as ranks
	FROM _days_weeks_deploy
),

_median_number_of_deployment_days_per_week as(
	SELECT max(days_deployed) as median_number_of_deployment_days_per_week
	FROM _median_number_of_deployment_days_per_week_ranks
	WHERE ranks <= 0.5
),

_median_number_of_deployment_days_per_month_ranks as(
	SELECT *, percent_rank() over(order by months_deployed) as ranks
	FROM _monthly_deploy
),

_median_number_of_deployment_days_per_month as(
	SELECT max(months_deployed) as median_number_of_deployment_days_per_month
	FROM _median_number_of_deployment_days_per_month_ranks
	WHERE ranks <= 0.5
)

SELECT 
	CASE  
		WHEN median_number_of_deployment_days_per_week >= 3 THEN 'On-demand'
		WHEN median_number_of_deployment_days_per_week >= 1 THEN 'Between once per week and once per month'
		WHEN median_number_of_deployment_days_per_month >= 1 THEN 'Between once per month and once every 6 months'
		ELSE 'Fewer than once per six months' END AS 'Deployment Frequency'
FROM _median_number_of_deployment_days_per_week, _median_number_of_deployment_days_per_month

[Startrekzky]

Hi @GSoul-hub , these are the filters to query DEPLOYMENTs in a selected project

Based on your given screenshot of the DB, my guess is that many cicd_tasks(deployments) are filtered out by this condition

pm.project_name in ($project)

Maybe there aren't many cicd_tasks(deployments) in the project 'shipping-project-test' you selected in Grafana?

[GSoul-hub]

@Startrekzky
There are 64 (production):

and look to the monthly deployments diagram (it shows some data):

[Startrekzky]

@abeizn can you take a look at the problem?

[abeizn]

@GSoul-hub
It is mainly to obtain a median by querying the records deployed within a specified time period, and then judge how often the deployment frequency is based on this median.
From the data you provided, I think there are a lot of deployments in March, but few or no deployments in other months, so it leads to this result

[GSoul-hub]

@abeizn you are right!
I changed default value (last 6 month) to 3 month and it started to show.

Just Median Time To Restore service showing different stuff - one show less than one hour, another shows 0.

First one:

-- Metric 3: Median time to restore service 
with _incidents as (
-- get the incidents created within the selected time period in the top-right corner
	SELECT
	  distinct i.id,
		cast(lead_time_minutes as signed) as lead_time_minutes
	FROM
		issues i
	  join board_issues bi on i.id = bi.issue_id
	  join boards b on bi.board_id = b.id
	  join project_mapping pm on b.id = pm.row_id
	    left join issue_labels il on i.id = il.issue_id
	WHERE
	  pm.project_name in ($project)
		and i.type = 'INCIDENT'
		and il.label_name = 'Production'
		and $__timeFilter(i.created_date)
),

_median_mttr_ranks as(
	SELECT *, percent_rank() over(order by lead_time_minutes) as ranks
	FROM _incidents
),

_median_mttr as(
	SELECT max(lead_time_minutes) as med_time_to_resolve
	FROM _median_mttr_ranks
	WHERE ranks <= 0.5
)

SELECT 
	case
		WHEN med_time_to_resolve < 60  then "Less than one hour"
    WHEN med_time_to_resolve < 24 * 60 then "Less than one Day"
    WHEN med_time_to_resolve < 7 * 24 * 60  then "Between one day and one week"
    WHEN med_time_to_resolve >= 7 * 24 * 60 then "More than one week"
    ELSE "N/A.Please check if you have collected deployments/incidents."
    END as med_time_to_resolve
FROM 
	_median_mttr

Another one (0 values):

-- Metric 3: median time to restore service - MTTR
with _incidents as (
-- get the incident count each month
	SELECT
	  distinct i.id,
		date_format(i.created_date,'%y/%m') as month,
		cast(lead_time_minutes as signed) as lead_time_minutes
	FROM
		issues i
	  join board_issues bi on i.id = bi.issue_id
	  join boards b on bi.board_id = b.id
	  join project_mapping pm on b.id = pm.row_id
	  	    left join issue_labels il on i.id = il.issue_id
	WHERE
	  pm.project_name in ($project)
		and i.type = 'INCIDENT'
		and il.label_name = 'Production'
		and i.lead_time_minutes is not null
),

_find_median_mttr_each_month_ranks as(
	SELECT *, percent_rank() over(PARTITION BY month order by lead_time_minutes) as ranks
	FROM _incidents
),

_find_median_mttr_each_month as(
	SELECT month, max(lead_time_minutes) as lead_time_minutes
	FROM _find_median_mttr_each_month_ranks
	WHERE ranks <= 0.5
	GROUP BY month
),

_find_mttr_rank_each_month as (
	SELECT
		*,
		rank() over(PARTITION BY month ORDER BY lead_time_minutes) as _rank 
	FROM
		_find_median_mttr_each_month
),

_mttr as (
	SELECT
		month,
		lead_time_minutes as med_time_to_resolve
	from _find_mttr_rank_each_month
	WHERE _rank = 1
),

_calendar_months as(
-- deal with the month with no incidents
	SELECT date_format(CAST((SYSDATE()-INTERVAL (month_index) MONTH) AS date), '%y/%m') as month
	FROM ( SELECT 0 month_index
			UNION ALL SELECT   1  UNION ALL SELECT   2 UNION ALL SELECT   3
			UNION ALL SELECT   4  UNION ALL SELECT   5 UNION ALL SELECT   6
			UNION ALL SELECT   7  UNION ALL SELECT   8 UNION ALL SELECT   9
			UNION ALL SELECT   10 UNION ALL SELECT  11
		) month_index
	WHERE (SYSDATE()-INTERVAL (month_index) MONTH) > SYSDATE()-INTERVAL 6 MONTH	
)

SELECT 
	cm.month,
	case 
		when m.med_time_to_resolve is null then 0 
		else m.med_time_to_resolve/60 end as med_time_to_resolve_in_hour
FROM 
	_calendar_months cm
	left join _mttr m on cm.month = m.month
ORDER BY 1

[abeizn]

Got it, I will close this issue, any other questions are welcome.