Search code examples
grafanainfluxdbdownsampling

Apply InfluxDB 1.x continuous query against multiple tables/columns?

For a monitoring project I have created a setup that uses InfluxDB 1.8. Because of compability reasons, using InfluxDB 2.x is not possible.

In order to save disk space, I am trying to downsample data the older it gets, a.k.a, "thin it out." In InfluxDB 1.x, this is perfectly possible using Retention Policies and Continuous Queries, as described here.

A problem arises however: the data that comes into InfluxDB contains many tables (availability, processors, ports ...) and many of those tables have more than one column of information. On top of that, I want to downsample three times: once from day to week, once from week to month and once from month to year. With 2 tables, each containing 4 columns, I will already have to write a minimum of 24 CQ's. Realistically speaking, I'll probably sit closer to around 20-30 tables with each having 1-20 columns. I am not writing 1200 CQ's.

My question: is there any built-in way to tell InfluxDB to apply RP's and CQ's to multiple tables and columns? If not, I'll probably just write some bash script to listen for current tables/columns and write the queries for me, but it's not elegant.

Many thanks in advance.

Details about the setup

  • LibreNMS 23.1.1 passing data (removing anything older than a day)
  • InfluxDB 1.8 receiving data (downsampling and removing anything older than a year)
  • Grafana 9.3.8 visualizing data and alerting

InfluxDB 2.x has made downsampling easier with the concept of buckets, but LibreNMS is not fully compatible with 2.x.

Solution

  • I managed to solve my issue by writing a 150 line bash script that writes the RP's and CQ's for me. By company policy I cannot share the script, but here's how I did it:

    1. Gather all InfluxDB databases.
    2. Create an empty .IQL text file.
    3. Write 4 RP's per database to the .IQL file: day, week, month, year.
    4. Use the InfluxDB API to erase all current CQ's per database.
    5. Write 3 CQ's per database, per measurement, per field to the .IQL file: dayToWeek, weekToMonth, monthToYear.
    6. Use the InfluxDB CLI (API is possible too) to execute each instruction in the .IQL file.

    I hope this can help someone. In total I ended up with almost 600 InfluxQL instructions for my test environment alone.