Hello,
I’m just getting started with grafana and InfluxDB. Coming from TSQL, VBA and Lua doesn’t make it easier for me to understand the syntax of InfluxDB.
I want to get an output as list/table of the first and the last “Timestamp” to each _value which are the used external IP’s of the last 12 month. The bucket based on a list ob IPs (less than 100 different) for every login-timestamp.
The output should look something like that:
IP | Min_Date | Max_Date
222.222.222.222 | 20240224 | 20240531
I tried this, which works with one specific IP (“222.222.222.222”) and about a Range of one month:
import "strings"
from(bucket: "TEST")
|> range(start: -1mo, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "log"
and r._field == "message"
and r.host == "grafana.host.de"
and r._value =~ /222.222.222.222/ )
|> keep(columns: ["_time", "_value"])
|> map(fn: (r) => ({r with _time: strings.splitN(v: string(v: r._time), t: "T", i: 2)[0]}))
|> map(fn: (r) => ({r with Min_time: strings.replace(v: r._time, t: "-", u: "", i: 2)}))
|> map(fn: (r) => ({r with Max_time: string(v: r.Min_time)}))
But ther are 2 Errors:
-
I get the warning: “Status: 500. Message: A query returned too many datapoints and the results have been truncated at 7961 points to prevent memory issues. At the current graph size, Grafana can only draw 796. Try using the aggregateWindow() function in your query to reduce the number of points returned.”
-
If I change the range to “-12Mo” the warning is: “Status: 504. Message: Post “https://grafana.host.de:…”: net/http: request canceled (Client.Timeout exceeded while awaiting headers)”
I think, I have to aggregate the amount of data or have to split the query for every IP/month, but I just don’t find out how that works. I’ve tried “aggregatewindow” and “unique” but my syntax seemed to be wrong.
Perhaps someone can help me.with this.
Many thanks and best regards!
