Get highest value per month

1

Hello,

I have an InfluxDB with a lot of Nagios data in it. For every 10 minutes I have information on how many users are logged in to a system at that time. I’m wondering if it’s possible to get the highest value per month somehow?

If I run:

SELECT mean("value") FROM "metrics" WHERE ("host" = 'SERVER' AND "performanceLabel" = 'Users') GROUP BY time(10m)

I get a list of all the values each 10 minutes, like this:

…
1532924400000000000 4
1532925000000000000 4
1532925600000000000 6
1532926200000000000 9
1532926800000000000 13
1532927400000000000 20
1532928000000000000 23
1532928600000000000 25
1532929200000000000 25
1532929800000000000 27
1532930400000000000 29
1532931000000000000 31
1532931600000000000 32
…

Maybe it’s possible to select everything for only the month of July etc.?
If I can do that I can just have one query for each month.

How would I only select everything in for 2018-07?

2

If you just want the highest value for July, just use max() aggregation with a time restriction for July only

SELECT max("value") FROM "metrics" WHERE ("host" = 'SERVER' AND "performanceLabel" = 'Users' AND time >= '2018-07-01' and time <= '2018-07-31')

If you want the highest value per-month for the last three months, for example just group by time:

SELECT max("value") FROM "metrics" WHERE ("host" = 'SERVER' AND "performanceLabel" = 'Users' AND time >= now() - 12w) group by time(4w)
1 Like
3

Awesome. That was just what I was looking for.
Thank you.

4

Hi, i have the same situation as above. But my result is a bit strange.

My database output data for probe “Test 1”:

time probe lastvalue_raw

2019-05-13T16:25:03Z Test 1
2019-05-13T20:00:03Z Test 1 862.8011
2019-05-14T00:00:02Z Test 1 873.1168
2019-05-14T04:00:02Z Test 1 879.2377
2019-05-14T08:00:03Z Test 1 887.7636
2019-05-14T12:00:02Z Test 1 895.3399
2019-05-14T16:00:03Z Test 1 909.3372
2019-05-14T20:00:03Z Test 1 920.6659
2019-05-15T00:00:02Z Test 1 933.2304
2019-05-15T04:00:03Z Test 1 940.0173
2019-05-15T08:00:03Z Test 1 953.7991
2019-05-15T12:00:03Z Test 1 963.5531
2019-05-15T16:00:02Z Test 1 974.5145
2019-05-15T20:00:03Z Test 1 989.7965
2019-05-16T00:00:03Z Test 1 1013.9998
2019-05-16T04:00:02Z Test 1 1004.0067
2019-05-16T08:00:02Z Test 1 1022.36
2019-05-16T12:00:02Z Test 1 1066.4328

My query:

SELECT max(lastvalue_raw) as lastvalue_raw FROM data_traffic WHERE (probe =~ /Test 1/ AND time >= now() - 52w) group by time(4w)

My Result:

time lastvalue_raw

2018-05-17T00:00:00Z
2018-06-14T00:00:00Z
2018-07-12T00:00:00Z
2018-08-09T00:00:00Z
2018-09-06T00:00:00Z
2018-10-04T00:00:00Z
2018-11-01T00:00:00Z
2018-11-29T00:00:00Z
2018-12-27T00:00:00Z
2019-01-24T00:00:00Z
2019-02-21T00:00:00Z
2019-03-21T00:00:00Z
2019-04-18T00:00:00Z 989.7965
2019-05-16T00:00:00Z 1066.4328

Why i have a value for april? I hope someone can explain that to me. Thanks in advance.

regards

5

If i use this query:

SELECT max(lastvalue_raw) as lastvalue_raw FROM data_traffic WHERE probe =~ /Test 1/ AND time >= ‘2018-05-16T00:00:00Z’ AND time <= ‘2019-05-16T13:00:00Z’ group by time(30d)

i receive the following result:

time lastvalue_raw

2018-04-19T00:00:00Z
2018-05-19T00:00:00Z
2018-06-18T00:00:00Z
2018-07-18T00:00:00Z
2018-08-17T00:00:00Z
2018-09-16T00:00:00Z
2018-10-16T00:00:00Z
2018-11-15T00:00:00Z
2018-12-15T00:00:00Z
2019-01-14T00:00:00Z
2019-02-13T00:00:00Z
2019-03-15T00:00:00Z
2019-04-14T00:00:00Z 862.8011
2019-05-14T00:00:00Z 1066.4328

This result is also not right but the april value is the first value in the table.

6

Hello @joschika77,

I believe it’s because of the combination of the time you selected and the groupby. Influx isn’t aware of months in the query you wrote. That April date with the value denotes the groupby that starts on 04/18 and groups data for the next 4 weeks. 4 weeks later on 05/15 you have a value of 989 so it is included in that group by. Instead, try using a start date at the beginning of the month. However, keep in mind that some months have more than 4 weeks (28 days), so this problem can still occur. Alternatively you can add the month in your tag.

7

Hey thx for the answer. Can you explain me what do you mean? How can i add it?

regards

8

@joschika77, you could include tags that specify the month at ingest. For example, I would write a python script that adds the months in tags based on the timestamp. You could also run 12 continuous queries for each month and output the max value into a new db. If you need to apply a CQ to historical data, you can backfill your old data by doing something similar to: Backfilling a Continuous Query?