InfluxDB show amount of entries per day

Grafana InfluxDB
1

Hello,
i have an InfluxDB 2 instance and am storing data like this:

_time _value hostname system username

2023-08-10 15:36:01 1234 host1 PRD mueller
2023-08-10 13:11:01 5541 host1 TST mueller
2023-08-10 11:33:01 1234 host1 PRD mueller
2023-08-09 13:32:01 2234 host1 PRD mueller
2023-08-09 10:46:01 3334 host1 PRD mueller

Now i want a simple graph showing the amount of entries in this measurement per day:

2023-08-10 => 3
2023-08-09 => 2

Currently i try with following flux query:

from(bucket: “v6crashes_temp”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “crashes”)
|> group()
|> aggregateWindow(every: 1d, fn: count)
|> yield(name: “count”)

As a result i get in Graphana for each day a time of 02:00 added, so 2023-08-12 02:00 is have a value of 3 (but this is already the next day) → is this normal?

Is my query wrong?

Also if i query for last 30 days it is “ok” (with the above +2 hours). but if i query for last 90 days data is missing (see attachments)

2
21823×666 100 KB

1
11817×662 148 KB

2

Welcome @fuba77 to the Grafana forum…

Sometimes when these aggregate queries are off by 1 or so, trying something like:

 |> aggregateWindow(every: 1d , fn: count, timeSrc: "_start")

will make it right. There is also an offset parameter to the aggregateWindow function that you can try using.

Re: the above, what does the data look like in table format when using Influx Data Explorer? It makes no sense that the most recent 30 days get lost when you move the time selector 90.

Also, what happens to your query if you remove the group() function? Again, trying it out in Influx Data Explorer might be the better place to troubleshoot.

3

i think i found the “problem” but still need a solution.

I exported all data from measurement using the command:
"influxd inspect export-lp --bucket-id 12345 --engine-path PATH_TO_ENGINE --measurement myMeasurement --output-path D:\temp\data.lp

If i now look at the data, i can see that some fields have an i (the newer ones) and others just have an integer. Might this be a problem showing all data in a graph?
How can i solve it?

data:
crashes_temp,hostname=host1,stage=TST,username=user1,version=6.422.12.0 duration=2176 1628065333000000000
crashes_temp,hostname=host11,stage=ABC,username=user2,version=6.422.12.0 duration=18434i 1692097086000000000
crashes_temp,hostname=host12,stage=GRU,username=user3,version=6.422.12.0 duration=125i 1692101211000000000

4

I believe the i means integer, so those values without a trailing i are floats. You can try converting all the floats to integers with a function like int() in a map() function or just toInt():

  |> map(fn: (r) => ({ r with floatValue: int(v: r.floatValue) }))

  |> toInt()
5

Thanks, but unfortunately it does not work for me.
I tested it in “Data Explorer” from OOTB Influx WEB UI

from(bucket: “v6crashes_temp”)
|> range(start: 1970-01-01T00:00:00Z, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “v6crashes_temp”)
|> filter(fn: (r) => r[“_field”] == “duration”)
|> map(fn: (r) => ({ r with floatValue: int(v: r.floatValue) }))

When i now export all data, there is still a mixture in type of field “duration”

6

What are you using to push this data to influxdb?
Are the time values UTC