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

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