Grafana query value error not sure if my query statement is wrong

InfluxDB 2
1

Hi guys！

These queries are from my query under influxdbv2

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com:9988")  
  |> count()

Its value is 1,051,093.

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com")  
  |> count()

Its value is 213,895

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com:6688")  
  |> count()

Its value is 11,322

However, I queried the values in grafana to try to sort them, and the query doesn’t look quite the same as the values I queried in my influxdb, and this is my query statement in grafana

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> group(columns: ["server_name"])
  |> count()
  |> group()
  |> sort(columns: ["_value"], desc: true)
  |> limit(n: 10)

The query looks like this

www.test.com:9988 value 15766306
www.test.com value 3208172
www.test.com:6688 value 169826

The values queried in Grafan and the values I queried in the database are way too different, is there a problem with my query statement in Grafana, please help me out!

2

@tengdagg Just to confirm, the query that you’re running in Grafana is using a statically defined time range and is not using the timerange variables (v.timeRangeStart, v.timeRangeStop)?

How are you running the queries in InfluxDB (that filter by specific by a specific tag value)? In the data explorer? In a dashboard cell? If in a dashboard cell, what type of visualization are you using?

One discrepancy between the queries is that the ones you’re running in InfluxDB use the default group key (by measurement and all tags) while the one you’re running in Grafana groups by server_name. count() operates on each group/table. I’d recommend updating the queries you’re running in InfluxDB to use the same grouping before executing a count():

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com:9988")
  |> group(columns: ["server_name"])
  |> count()

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com")
  |> group(columns: ["server_name"])
  |> count()

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com:6688")
  |> group(columns: ["server_name"])
  |> count()
3

@scott
I want to count the number of requests for server_name in grafana in this specific time period and rank them in descending order of Top10.
|> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
Both grafana and influxdb times are like this This time I just conducted a test

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> group(columns: ["server_name"])
  |> count()
  |> group()
  |> sort(columns: ["_value"], desc: true)
  |> limit(n: 10)

This query is a query test for my explorations in grafana, and because of the differences in the data, I have queried the specific domains with differences in influxdb, is there a problem with the query statement in grafana that leads to the different number of results? Is there something wrong with my query statement in grafana that is causing the difference in the number of results?

The query value is not correct in influxdb either, I feel like there is something wrong with my query statement

image
image1040×538 48.8 KB

My most queried domain alone has just over 1 million requests in 24 hours but the query yielded over 10 million results

4

What fields do you have in this measurement?

5

@scott
There are only these fields in this query

WX20240828-111037
WX20240828-1110371371×119 8.76 KB

There are no other fields.

6

Run this query for me:

import "influxdata/influxdb/schema"

schema.measurementFieldKeys(bucket: "nginx_prod", measurement: "nginx_log")
7

@scott Hi scott I used this query you gave me and the query looks like this

image
image1367×483 33.3 KB

image
image1371×574 44.9 KB

image
image786×347 33.5 KB

My server_name is of tag type, otherwise the query is very slow

8

Ok, the reason I ask what fields exist is because you’re not filtering by field, so the numbers you’re calculating are actually the sum of points across all fields. I don’t think that’s what you’re looking for. I’d suggest filter by a field, just to limit the count to a single field:

from(bucket: "nginx_prod")
  |> range(start: 2024-08-21T00:00:00Z, stop: 2024-08-22T00:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["_field"] == "request")
  |> group(columns: ["server_name"])
  |> count()
  |> group()
  |> sort(columns: ["_value"], desc: true)
  |> limit(n: 10)