[GitHub] [incubator-devlake] GSoul-hub commented on issue #4217: [Feature][JIRA/DORA] Incident mapping

[via GitHub]

GSoul-hub commented on issue #4217:
URL: 
https://github.com/apache/incubator-devlake/issues/4217#issuecomment-1491445065

   @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.
   
![image](https://user-images.githubusercontent.com/3878492/229053552-14e9f0b3-9ddd-4152-99cb-16656c45e49c.png)
   
   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
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@devlake.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org