Add duration_chart endpoint for Cycle Analytics (!19293) · Merge requests · GitLab.org / GitLab

Adam Hegyi requested to merge 12183-data-for-ca-duration-chart into master Oct 29, 2019

What does this MR do?

This MR exposes Cycle Analytics duration data for the scatterplot chart. For each stage returns max 500 items (for performance reasons) in the following format:

X axis: time
Y axis: duration

[
  {
     "duration_in_seconds": 1234,
     "finished_at": "2012-04-23T18:25:43.511Z"
  }
]

Note: later on the schema can be extended to include MR or Issue information.

Try it

To try the feature, make sure that you enable the FF:

Feature.enable(:cycle_analytics)

Example Endpoint With a Default Stage:

-/analytics/cycle_analytics/stages/review/duration_chart?group_id=gitlab-org&created_after=2019-01-01&created_before=2019-05-01

Example Endpoint With a Persisted Stage:

In rails console:

group = Group.find_by_full_path("gitlab-org")
user = User.find_by_username("root")
Analytics::CycleAnalytics::Stages::CreateService.new(parent: group, params: { name: 'My Stage', start_event_identifier: :merge_request_created, end_event_identifier: :merge_request_merged }, current_user: user).execute

puts Analytics::CycleAnalytics::GroupStage.find_by_name("My Stage").id

Use the printed id in the URL:

-/analytics/cycle_analytics/stages/$STAGE_ID/duration_chart?group_id=gitlab-org&created_after=2019-01-01&created_before=2019-05-01

Query

The query has similar structure like other CA queries. Here we select two columns, limit the result set to 500 (will be tweaked later).

Issue based stage

SELECT ROUND(EXTRACT(EPOCH
               FROM COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") - "issues"."created_at")) AS duration_in_seconds,
       COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") AS finished_at
FROM "issues"
INNER JOIN "projects" ON "projects"."id" = "issues"."project_id"
INNER JOIN "issue_metrics" ON "issue_metrics"."issue_id" = "issues"."id"
INNER JOIN
  (WITH RECURSIVE "base_and_descendants" AS (
                                               (SELECT "namespaces".*
                                                FROM "namespaces"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."id" = 9970)
                                             UNION
                                               (SELECT "namespaces".*
                                                FROM "namespaces",
                                                     "base_and_descendants"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
WHERE "issues"."created_at" >= '2019-07-01'
  AND "issues"."created_at" <= '2019-11-01'
  AND ("issue_metrics"."first_added_to_board_at" IS NOT NULL
       OR "issue_metrics"."first_associated_with_milestone_at" IS NOT NULL)
  AND COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at") >= "issues"."created_at"
ORDER BY COALESCE("issue_metrics"."first_associated_with_milestone_at", "issue_metrics"."first_added_to_board_at")
LIMIT 500

Plan

Merge Request based stage

SELECT ROUND(EXTRACT(EPOCH
               FROM "merge_request_metrics"."merged_at" - "merge_requests"."created_at")) AS duration_in_seconds,
       "merge_request_metrics"."merged_at" AS finished_at
FROM "merge_requests"
INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
INNER JOIN
  (WITH RECURSIVE "base_and_descendants" AS (
                                               (SELECT "namespaces".*
                                                FROM "namespaces"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."id" = 9970)
                                             UNION
                                               (SELECT "namespaces".*
                                                FROM "namespaces",
                                                     "base_and_descendants"
                                                WHERE "namespaces"."type" IN ('Group')
                                                  AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".*
   FROM "base_and_descendants" AS "namespaces") namespaces ON namespaces.id=projects.namespace_id
WHERE "merge_requests"."created_at" >= '2019-07-01'
  AND "merge_requests"."created_at" <= '2019-11-01'
  AND "merge_request_metrics"."merged_at" >= "merge_requests"."created_at"
ORDER BY "merge_request_metrics"."merged_at"
LIMIT 500

Plan

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Closes #12183

Edited Oct 29, 2020 by 🤖 GitLab Bot 🤖

Add duration_chart endpoint for Cycle Analytics