[Bug][Grafana] Monthly Deployment SQL Filter

[jeffschaper]

Search before asking

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

What happened

I filtered on the "Last 2 days" drop down in the Grafana dashboard and didn't see any metrics related to my application.

What do you expect to happen

I would expect to see metrics related to my application regardless of the time frame selected.

How to reproduce

1. Make one or more commits in a new month
2. Wait at least three days into the month
3. Select the "Last 2 days" filter in the Grafana dashboard

Important: The last two days should not overlap the beginning or end of a month. Make sure the "Last 2 days" are in the middle of the month.

Anything else

I'm not sure if this is a bug or a design decision.

The SQL query appears to use the BETWEEN keyword in place of the $__timeFilter function. This results in the query looking something like this:

SELECT 
    cm.month, 
    case when d.deployment_count is null then 0 else d.deployment_count end as deployment_count
FROM 
    calendar_months cm
    LEFT JOIN _deployments d on cm.month = d.month
    WHERE cm.month_timestamp BETWEEN '2024-07-09T15:15:33.784Z' AND '2024-07-11T15:15:33.784Z'
    ORDER BY cm.month;

The calendar_months table appears to be truncating the month_timestamp column, so the WHERE clause doesn't catch anything.

Here's the full SQL query:

-- Metric 1: Number of deployments per month
with
  _deployments as (
    -- When deploying multiple commits in one pipeline, GitLab and BitBucket may generate more than one deployment. However, DevLake consider these deployments as ONE production deployment and use the last one's finished_date as the finished date.
    SELECT
      date_format(deployment_finished_date, '%y/%m') as month,
      count(cicd_deployment_id) as deployment_count
    FROM
      (
        SELECT
          cdc.cicd_deployment_id,
          max(cdc.finished_date) as deployment_finished_date
        FROM
          cicd_deployment_commits cdc
          JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
          and pm.`table` = 'cicd_scopes'
        WHERE
          pm.project_name in ($project)
          and cdc.result = 'SUCCESS'
          and cdc.environment = 'PRODUCTION'
        GROUP BY
          1
        HAVING
          $__timeFilter (max(cdc.finished_date))
      ) _production_deployments
    GROUP BY
      1
  )
SELECT
  cm.month,
  case
    when d.deployment_count is null then 0
    else d.deployment_count
  end as deployment_count
FROM
  calendar_months cm
  LEFT JOIN _deployments d on cm.month = d.month
WHERE
  $__timeFilter (cm.month_timestamp)

Version

v0.19.0

Are you willing to submit PR?

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

Code of Conduct

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

[dosubot[bot]]

Hello, @jeffschaper! I'm here to help you with any bugs, questions, or contributions you have for the repository. Let me know how I can assist you!

The issue you're experiencing with the "Last 2 days" filter in the Grafana dashboard is likely due to the way the __timeFilter macro is applied in your SQL queries. The calendar_months table's month_timestamp column is a timestamp representing the first day of the month at midnight, which might not align well with the "Last 2 days" filter.

To address this, ensure that the __timeFilter macro is correctly applied to the finished_date column in your _deployments CTE and that the calendar_months table is used appropriately for monthly aggregation. Here's a revised version of your SQL query:

-- Metric 1: Number of deployments per month
with
  _deployments as (
    -- When deploying multiple commits in one pipeline, GitLab and BitBucket may generate more than one deployment. However, DevLake consider these deployments as ONE production deployment and use the last one's finished_date as the finished date.
    SELECT
      date_format(deployment_finished_date, '%y/%m') as month,
      count(cicd_deployment_id) as deployment_count
    FROM
      (
        SELECT
          cdc.cicd_deployment_id,
          max(cdc.finished_date) as deployment_finished_date
        FROM
          cicd_deployment_commits cdc
          JOIN project_mapping pm on cdc.cicd_scope_id = pm.row_id
          and pm.`table` = 'cicd_scopes'
        WHERE
          pm.project_name in ($project)
          and cdc.result = 'SUCCESS'
          and cdc.environment = 'PRODUCTION'
        GROUP BY
          1
        HAVING
          $__timeFilter(max(cdc.finished_date))
      ) _production_deployments
    GROUP BY
      1
  )
SELECT
  cm.month,
  case
    when d.deployment_count is null then 0
    else d.deployment_count
  end as deployment_count
FROM
  calendar_months cm
  LEFT JOIN _deployments d on cm.month = d.month
WHERE
  $__timeFilter(cm.month_timestamp)

Ensure that the __timeFilter macro is correctly applied to the finished_date column in the _deployments CTE and that the calendar_months table is used appropriately for monthly aggregation. This should help in filtering the data correctly based on the selected time range ^[1][2].

To continue talking to Dosu, mention @dosu.

[Startrekzky]

Hi @jeffschaper , it's a design decision to take the time filter when measuring DORA metrics.

[jeffschaper]

Hi @jeffschaper , it's a design decision to take the time filter when measuring DORA metrics.

@Startrekzky - Thanks for getting back to me! Is there a way to remove these unsupported drop down filters in the Grafana dashboard?

Alternatively, does DevLake have any documentation explaining how these drop down filters are intended to work?

[jeffschaper]

@Startrekzky - Thanks for getting back to me! Is there a way to remove these unsupported drop down filters in the Grafana dashboard?

Alternatively, does DevLake have any documentation explaining how these drop down filters are intended to work?

@Startrekzky - just checking in to see if you saw my response from a couple weeks ago. Is there a way to remove these unsupported drop down filters from the Grafana dashboard? Does DevLake have any specific documentation explaining how these drop downs are intended to work?

[Startrekzky]

Is there a way to remove these unsupported drop down filters from the Grafana dashboard?

Hi @jeffschaper , I just checked that this filter has been removed since v1 or earlier versions. You're still on v0.19, which is 3 major versions behind the latest version v1.0.1-rc2.

Does DevLake have any specific documentation explaining how these drop downs are intended to work?

Not yet. Since the embedded dashboards change rapidly, we haven't added any dashboard-related docs to the doc site. It is also not easy to describe which filters affect which panels, as a dashboard might have more than 20 panels.

[jeffschaper]

Hi @jeffschaper , I just checked that this filter has been removed since v1 or earlier versions. You're still on v0.19, which is 3 major versions behind the latest version v1.0.1-rc2.

Interesting. I'm running v1 locally and I still see the "last 2 days" filter, unless I'm doing something wrong.

Not yet. Since the embedded dashboards change rapidly, we haven't added any dashboard-related docs to the doc site. It is also not easy to describe which filters affect which panels, as a dashboard might have more than 20 panels.

That's understandable.

I think at this point I'll just document the bug internally until this gets a resolution.

[Startrekzky]

Interesting. I'm running v1 locally and I still see the "last 2 days" filter, unless I'm doing something wrong.

Hi @jeffschaper , could you take a screenshot of the dashboard?

I forgot to ask the specific Grafana dashboard(s) you were referring to. I thought you were talking about the DORA dashboard, but it might not be true.

[jeffschaper]

I forgot to ask the specific Grafana dashboard(s) you were referring to. I thought you were talking about the DORA dashboard, but it might not be true.

Oh, yes, good call out, it is the DORA dashboard I was referring to. Here's a screenshot.

[Startrekzky]

@jeffschaper Oh, you're referring to the time filter on the top-right corner. It's the embedded time filter of Grafana, all the dropdown items are determined by Grafana. We can't remove it.

[jeffschaper]

@jeffschaper Oh, you're referring to the time filter on the top-right corner. It's the embedded time filter of Grafana, all the dropdown items are determined by Grafana. We can't remove it.

Gotcha. I assume you were thinking about the $__timeFilter function in the SQL query? I still think the SQL query should account for the scenario in the issue description, unless the update you referred to solves that problem?

[Startrekzky]

Hi @jeffschaper , do you mean this could be improved?

[jeffschaper]

Yeah, that's what I was thinking. In the scenario I explained above, the WHERE clause dates did not span between two distinct months in the calendar_months table, which caused the query to not return any results. Like I mentioned, I'm not sure if this is a design decision or a bug.

[Startrekzky]

Hi @jeffschaper , it could be improved for sure. I'll put up a PR later.

Is this the only problem remained after the above discussion? If so, I'll close the issue when the PR gets merged.

[jeffschaper]

@Startrekzky - Yep! That's the only issue remaining. I appreciate your help!

[jeffschaper]

@Startrekzky - What version of DevLake will this fix be released?