[Visualizations] counter_rate formula not showing data

[crespocarlos]

Kibana version: 8.9

Describe the bug:

counter_rate formula not returning data depending on how sparse the data points are.

Steps to reproduce:

1. Setup metricbeat enabling the system module as following

- module: system
  metricsets:
    - cpu # CPU usage
    - load # CPU load averages
    - memory # Memory usage
    - network # Network IO
    - process # Per process metrics
    - process_summary # Process summary
    - uptime # System Uptime
    - socket_summary # Socket summary
    #- core           # Per CPU core usage
    - diskio # Disk IO
    - filesystem # File system usage for each mountpoint
    - fsstat # File system summary metrics
    #- raid           # Raid
    #- socket         # Sockets and connection info (linux only)
    #- service        # systemd service information
  enabled: true
  period: 1m # this is important
  processes: [".*"]

2. Start a local Kibana, Metricbeat and Elasticsearch instances
3. Create Line chart

Use a formula with counter_rate (eg: counter_rate(max(system.diskio.read.count), kql='system.diskio.read.count: *'))

4. Set a date range such that the auto interval is 5s (eg: Last 5 minutes):

Expected behavior:

The query executed returns data:

{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 3,
    "successful": 3,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1852,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "0": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "1": {
            "buckets": [
              {
                "key_as_string": "2023-08-11T13:40:05.000+02:00",
                "key": 1691754005000,
                "doc_count": 339,
                "2-bucket": {
                  "doc_count": 0,
                  "2-metric": {
                    "value": null
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:40:10.000+02:00",
                "key": 1691754010000,
                "doc_count": 30,
                "2-bucket": {
                  "doc_count": 1,
                  "2-metric": {
                    "value": 166418032
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:41:05.000+02:00",
                "key": 1691754065000,
                "doc_count": 345,
                "2-bucket": {
                  "doc_count": 0,
                  "2-metric": {
                    "value": null
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:41:10.000+02:00",
                "key": 1691754070000,
                "doc_count": 30,
                "2-bucket": {
                  "doc_count": 1,
                  "2-metric": {
                    "value": 166445670
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:42:05.000+02:00",
                "key": 1691754125000,
                "doc_count": 338,
                "2-bucket": {
                  "doc_count": 0,
                  "2-metric": {
                    "value": null
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:42:10.000+02:00",
                "key": 1691754130000,
                "doc_count": 30,
                "2-bucket": {
                  "doc_count": 1,
                  "2-metric": {
                    "value": 166464805
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:43:05.000+02:00",
                "key": 1691754185000,
                "doc_count": 342,
                "2-bucket": {
                  "doc_count": 0,
                  "2-metric": {
                    "value": null
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:43:10.000+02:00",
                "key": 1691754190000,
                "doc_count": 30,
                "2-bucket": {
                  "doc_count": 1,
                  "2-metric": {
                    "value": 166492436
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:44:05.000+02:00",
                "key": 1691754245000,
                "doc_count": 338,
                "2-bucket": {
                  "doc_count": 0,
                  "2-metric": {
                    "value": null
                  }
                }
              },
              {
                "key_as_string": "2023-08-11T13:44:10.000+02:00",
                "key": 1691754250000,
                "doc_count": 30,
                "2-bucket": {
                  "doc_count": 1,
                  "2-metric": {
                    "value": 166497782
                  }
                }
              }
            ]
          },
          "key": "Carloss-MacBook-Pro.local",
          "doc_count": 1852
        }
      ]
    }
  }
}

If the interval is manually set to minute, the chart shows data

That's the expected behaviour.

Screenshots (if relevant):

counter_rate results depending on the interval

1 minute interval:

Auto (5s)

[elasticmachine]

Pinging @elastic/kibana-visualizations @elastic/kibana-visualizations-external (Team:Visualizations)

[crespocarlos]

@stratoula I have. In the description, there is an example of the response with data.

Couple of examples:

{
  "key_as_string": "2023-08-11T13:40:10.000+02:00",
  "key": 1691754010000,
  "doc_count": 30,
  "2-bucket": {
    "doc_count": 1,
    "2-metric": {
      "value": 166418032
},
{
    "key_as_string": "2023-08-11T13:44:10.000+02:00",
    "key": 1691754250000,
    "doc_count": 30,
    "2-bucket": {
      "doc_count": 1,
      "2-metric": {
        "value": 166497782
      }
    }
  }

If the formula used in the chart is changed to average, it shows data:

in contrast with counter_rate:

[dej611]

When using a counter_rate function, Lens sends a max query to ES and then computes the rate client-side based on the max results.
In the first response I see that there's a null after each numeric value, so Lens it's not able to compute the rate based on that as null - X will return NaN for each bucket.
Es would be able to compute the rate for those buckets with a value, even if surrounded by null buckets, but with Lens' implementation is not possible. We're investigating the migration to the ES rate agg API in the future but that's not something we could easily do right now due to the complexity of the current Lens counter_rate operation.

Meanwhile I think we could do better here and perhaps prompt the user about this scenario and suggest to increment the time interval in order to have some meaningful data to visualize.

[dej611]

Closing in favour of #170393