TL;DR InfluxDB Tech Tips - Advanced Conditionals in InfluxQL, Non-SELECT Queries in Chronograf, and Queries Across Measurements

By Regan Kuchan / Product, Developer
Mar 30, 2017

Navigate to:

In this weekly post we recap the most interesting InfluxDB and TICK-stack related issues, workarounds, how-tos and Q&A from GitHub, IRC and the InfluxData Community that you might have missed in the last week or so.

Advanced conditionals in InfluxQL

Q:  I want to calculate minimum values across distinct time intervals and only see results where the minimum value is greater than 0. Is there a way to do that with InfluxQL?

My basic GROUP BY time() query:

> SELECT MIN("new-customers") FROM "coffee"  
WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z' 
GROUP BY time(1h),"location"

name: coffee
tags: location=a
time                  min
----                  ---
2017-03-29T14:00:00Z  0    <--- I'd love to suppress that result
2017-03-29T15:00:00Z  1
2017-03-29T16:00:00Z  1

name: coffee
tags: location=b
time                  min
----                  ---
2017-03-29T14:00:00Z  14
2017-03-29T15:00:00Z  15
2017-03-29T16:00:00Z  4

It’s invalid syntax but this is essentially what I’m looking for:

> SELECT MIN("new-customers") FROM "coffee"
WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z'
AND MIN("new-customers") > 0
GROUP BY time(1h),"location"

ERR: invalid expression: min("new-customers"::float) > 0

A: Yes! InfluxQL’s subqueries offer that functionality:

> SELECT "min" FROM 
(SELECT MIN("new-customers") FROM "coffee"
WHERE time >= '2017-03-29T14:00:00Z' AND time < '2017-03-29T17:00:00Z'
GROUP BY time(1h),"location")
WHERE "min" > 0 GROUP BY "location"

name: coffee
tags: location=a
time                  min
----                  ---
2017-03-29T15:00:00Z  1    <--- No more 0! ????
2017-03-29T16:00:00Z  1

name: coffee
tags: location=b
time                  min
----                  ---
2017-03-29T14:00:00Z  14
2017-03-29T15:00:00Z  15
2017-03-29T16:00:00Z  4

The subquery (the bolded query that’s in the FROM clause of the main query) is your original query. The main query places an additional condition on the results of that subquery. Check out the Data Exploration page for more information about subqueries and their use cases.

Non-SELECT queries and user management in Chronograf

Q: I’ve been using the web admin console to run SHOW queries on my data and manage my users in InfluxDB. I’ve noticed that the web admin console has been deprecated and is expected to be removed in a future release. Are there any substitutions for the web admin console? If at all possible, I’d prefer not to use the HTTP API or the Command Line Interface.

A: Chronograf, InfluxData’s user interface for querying, visualizing, and monitoring your data, supports both SHOW queries and user management. Check out the installation guide for how to get up and running with Chronograf.

An example of running a SHOW query in Chronograf:

User management in Chronograf:

Chronograf is currently in beta and doesn’t support all of the functionality available in the web admin interface (yet!). If you have any issues or requests please feel free to open an issue. We’d love to get your feedback!

Queries across measurements

Q: I know I can query field keys with the same name across measurements; what happens if I query field keys that appear only in some of the measurements in the FROM clause? Will the query just return an error?

A: Your query won’t return an error. InfluxDB doesn’t return results for a measurement if the field key doesn’t exist in that measurement. In the example below, every measurement except for winter has the flower-index field key. Notice that the query doesn’t return any results for winter even though that measurement appears in the FROM clause.

> SELECT MEAN("flower-index") FROM "summer","fall","winter","spring"

name: fall
time                  mean
----                  ----
1970-01-01T00:00:00Z  4

name: spring
time                  mean
----                  ----
1970-01-01T00:00:00Z  10

name: summer
time                  mean
----                  ----
1970-01-01T00:00:00Z  8

 

What's next:

  • Downloads for the TICK-stack are live on our "downloads" page.
  • Deploy on the Cloud: Get started with a FREE trial of InfluxDB Cloud featuring fully-managed clusters, Kapacitor and Grafana.
  • Deploy on Your Servers: Want to run InfluxDB clusters on your servers? Try a FREE 14-day trial of InfluxDB Enterprise featuring an intuitive UI for deploying, monitoring, and rebalancing clusters, plus managing backups and restores. 
  • Tell Your Story: Over 300 companies have shared their story on how InfluxDB is helping them succeed. Submit your testimonial and get a limited edition hoodie as a thank you.