[InfluxDB] Time calculation

[UrBnW]

Hi,

Using InfluxDB plugin, I would like to check if the last value of a series is not more than 90s old.

I then tried the following :

--plugin=database::influxdb::plugin
--mode=query
--query="count,select count(rta) as count FROM \"centreon\".\"365\".\"host-latency\" where \"host\"='myhost' and time>now()-90s group by host"
--instance='host'
--output='Number of updates in last 90 seconds : %{count}'
--critical-status="%{count} == 0"

Unfortunately, this does not work as count in InfluxDB returns NULL instead of 0...

My idea is then to select the timestamp of the last inserted value and compare it to current timestamp using --critical-status option.

First, I then think we need epoch timestamps to easier achieve this.
We should then need the &epoch=s query parameter.

Secondly, I tried to select time field, but I did not manage to do it :

--plugin=database::influxdb::plugin
--mode=query
--query="time,select last(rta), time as time FROM \"centreon\".\"365\".\"host-latency\" where \"host\"='myhost' group by host TZ('Europe/Paris')"
--instance='host'
--output='Last timestamp : %{time}'

returns status : skipped (no value(s)).

Finally, I'm not sure how to make the comparison with current timestamp in --critical-status option (time() does not seem to be evaluated)...

Any help ?

Thank you very much 👍

[garnier-quentin]

Maybe we can use empty value instead of undef.

[UrBnW]

And I would do --critical-status="%{count} eq ''" ?

[garnier-quentin]

that's the idea

[UrBnW]

Sounds good, should work :)
Thank you for your quick feedback 👍

[garnier-quentin]

Could you test it ?

[UrBnW]

Unfortunately this does not help.

Here's the raw result (--debug) when count returns something >0 :
{"results":[{"statement_id":0,"series":[{"name":"host-latency","tags":{"host":"myhost"},"columns":["time","count"],"values":[["2019-10-24T12:59:00.609502725Z",1]]}]}]}
And when it should return 0 but returns null :
{"results":[{"statement_id":0}]}

[garnier-quentin]

It cannot work. I have looked the code. We don't know the instance. Influxdb provides that information.

[UrBnW]

Understood.
So perhaps we should go with my other idea, being able to select time column ?

[garnier-quentin]

Could you try to add fill(-1) at the end of your request ?

[UrBnW]

Already tested before opening this bug, but just tested again, does not work 😕

[garnier-quentin]

It seems like a bug never fixed:
influxdata/influxdb#6967

[UrBnW]

Yep.
Those asking count to return 0 would then also be happy :)

[UrBnW]

Here's the raw result of the second query of my first post, if it can help :
{"results":[{"statement_id":0,"series":[{"name":"host-latency","tags":{"host":"myhost"},"columns":["time","last"],"values":[["2019-10-24T15:32:16+02:00",10.638]]}]}]}

[garnier-quentin]

Maybe for other users with the same issue.

[UrBnW]

As you can see in my first post, we can't select time column.
Sounds like api.pm always select second column ([1]) as the result column.

[garnier-quentin]

Yes exactly.

[UrBnW]

OK. We're in some kind stuck with this then ?

[garnier-quentin]

You can do a PR. But it's more like a bug (missing feature) of influxdb.

[UrBnW]

Yes sounds like there are some missing feature in InfluxDB.
Thank you for having looked at this.

[UrBnW]

Issue solved thanks to #1702.

Example to check that InfluxDB is constantly updated (by the Stream Connector for example) :

--query="ts,select time as ts, last(rta) FROM \"centreon\".\"365\".\"host-latency\" group by ts TZ('Europe/Paris')"
--instance='ts'
--critical-status='%{ts} < time()-180'
--output='Last update: %{ts}'
--change-short-output='(\d{10})~scalar localtime $1~e'

Nice human readable output :
OK: Last update: Sat Nov 23 13:21:42 2019 | '#ts'=1574511702;;;;