InfluxQL + Grafana

Understanding the Basics

Selecting Data

• Basic Query: SELECT "field_key" FROM "measurement_name"
• With Time Filter: SELECT "field_key" FROM "measurement_name" WHERE $timeFilter

Filtering

• Specific Time Range: WHERE time >= '2023-01-01T00:00:00Z' AND time <= '2023-01-02T00:00:00Z'
• Relative Time: WHERE time > now() - 1h
• Tags: WHERE "tag_key" = 'tag_value'
• Fields: WHERE "field_key" > 100

Grouping Data

• Time Intervals: GROUP BY time(10m)
• Tags: GROUP BY "tag_key"
• Time and Tags: GROUP BY time(10m), "tag_key"

Aggregations and Calculations

Common Aggregations

• Count: SELECT COUNT("field_key") FROM "measurement_name"
• Sum: SELECT SUM("field_key") FROM "measurement_name"
• Mean: SELECT MEAN("field_key") FROM "measurement_name"
• Min/Max: SELECT MIN("field_key"), MAX("field_key") FROM "measurement_name"

Selectors (Return a single point)

• First/Last: SELECT FIRST("field_key"), LAST("field_key") FROM "measurement_name"
• Top/Bottom: SELECT TOP("field_key", 5), BOTTOM("field_key", 5) FROM "measurement_name"

Transformations

• Difference: SELECT DIFFERENCE(FIRST("field_key")) FROM "measurement_name"
• Moving Average: SELECT MOVING_AVERAGE("field_key", 5) FROM "measurement_name"
• Cumulative Sum: SELECT CUMULATIVE_SUM("field_key") FROM "measurement_name"

Advanced Techniques

Joining Data (Merge)

• SELECT * FROM "measurement_one" INNER JOIN "measurement_two" ON "measurement_one"."tag_key" = "measurement_two"."tag_key"

Subqueries

• SELECT MEAN("usage") FROM (SELECT SUM("field_key") AS "usage" FROM "measurement_name" GROUP BY time(1h))

Data Exploration

• Show Databases: SHOW DATABASES
• Show Measurements: SHOW MEASUREMENTS
• Show Field Keys: SHOW FIELD KEYS FROM "measurement_name"
• Show Tag Keys: SHOW TAG KEYS FROM "measurement_name"

Grafana-Specific Tips

• Using $timeFilter: Grafana replaces $timeFilter with the dashboard’s current time range, ensuring queries are automatically scoped to the visible window.
• Template Variables: Use Grafana template variables to create dynamic dashboards. You can incorporate variables in your InfluxQL queries like so: WHERE "tag_key" = '$variable_name'.
• Annotations: Use InfluxQL to create annotations in Grafana by selecting the time and text columns, e.g., SELECT time, "event_text" FROM "annotations" WHERE $timeFilter.
• Debugging Queries: Utilize Grafana’s Query Inspector feature to debug and optimize your InfluxQL queries. This can be especially helpful for understanding how Grafana applies $timeFilter and other variables to your queries.