Basic influxdb queries, setup.

You can use influx (the influxdb shell) or use the http API on port :8086

Create and apply retention policy to a database: warning this may remove/purge existing data

CREATE RETENTION POLICY "30_days" ON collectd DURATION 30d REPLICATION 1 default

notice the ending default that means to apply the created policy

For 1 year, use 52w (52 weeks)

CREATE RETENTION POLICY "a_year" ON telegraf DURATION 52w REPLICATION 1 default

Show retention policies:

> show retention policies
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        false
30_days 720h0m0s 24h0m0s            1        true

Show databases contents (measurements):

> show measurements on collectd
name: measurements
name
----
cpu_value
df_value
disk_io_time
disk_read
disk_weighted_io_time
disk_write
filecount_value
interface_rx
interface_tx
load_longterm
load_midterm
load_shortterm
memory_value
ntpd_value
processes_value
swap_value
tcpconns_value
uptime_value
users_value

To see all series on a database:

> show series on collectd limit 4
key
---
cpu_value,host=aruba,instance=0,type=percent,type_instance=idle
cpu_value,host=aruba,instance=0,type=percent,type_instance=interrupt
cpu_value,host=aruba,instance=0,type=percent,type_instance=nice

To get a specific serie:

> show series on collectd from cpu_value WHERE host = 'aruba'
key
---
cpu_value,host=aruba,instance=0,type=percent,type_instance=idle
cpu_value,host=aruba,instance=0,type=percent,type_instance=interrupt
cpu_value,host=aruba,instance=0,type=percent,type_instance=nice
cpu_value,host=aruba,instance=0,type=percent,type_instance=system
cpu_value,host=aruba,instance=0,type=percent,type_instance=user

🔗loglevel warn

To avoid multiple log lines like:

error="NaN is an unsupported value for field value"

Set the log level to warn:

[logging]
  format = "auto"
  level = "info"
  suppress-logo = false

If you don't have this entry add it to your influxd.conf

🔗Drop series not getting data

For example if using collectd and want to remove series that haven't receive data in less than 10 days:

#!/bin/sh

HOSTS=$(influx -database "collectd" -execute "SHOW TAG VALUES WITH KEY=host" | awk '/^host/ && !a[$2]++ { print $2}')

for i in ${HOSTS}
do
    PURGE=$(influx -database "collectd" -execute "SELECT last(value) FROM cpu_value WHERE host = '${i}' and time > now() -10d" | head -c1 | wc -c)
    if [ ${PURGE} -eq 0 ]; then
        echo "droping ${i}"
        influx -database "collectd" -execute "DROP series WHERE host='${i}'"
    fi;
done

🔗Time format

Within the CLI use:

> precision rfc3339

Query using ssl:

$ influx -ssl -unsafeSsl