Select with sql equivalent "not null"

InfluxDB 2 Dashboards
1

Hello,

i want to select a measurement with a lot of field values where whole columns are all set to “null”.
The select should only show me columns containing data and nothing with “null” values.

Is this somehow possible?

T-One

2

I think Influx doesn’t distinguish between ‘’ (blank, empty) and NULL, so:

field!=''

should do what you want (that’s two single apostrophes, not one double
quotation mark).

Antony.

3

This field!=’’ is a part of the “where”-statement and i have to identify it by name right?
So i have to know the name of the column i don’t want to see the “null” values?

4

This field!=‘’ is a part of the “where”-statement

Yes.

and i have to identify it by name right?

Right.

So i have to know the name of the column i don’t want to see the “null”
values?

Yes. You can use more than one if necessary, though:

… where field1 != ‘’ and field2 != ‘’ and …

… where (field1 != ‘’ or field2 != ‘’) and …

Antony.

5

That’s a problem, i have over 400 columns and based on the Tag filter most of them are “null” but i dont really know which column is null or not. So i need a way to filter all columns with “null” value based on the value, not on the column name.

one of my selct statements (via API) is:

SELECT * from "disk_usage" WHERE ( "hostname" = 'host.example.com' ) ORDER BY time DESC limit 1

This outputs every mountpoint available across all hosts as a column but shows most of them with “null” and only the ones matching the hostname tag with a real value.

But i start to think this is a 3rd party error on the whole db scheme, i don’t think this is how influxdb should be used.

6

I think I agree with you. This sounds like a most inefficient and inconvenient
way to set up a database.

Personally I would start with just three fields (plus time, of course):

hostname
devicename
usage

Then a “select * from table where hostname = ‘one.specific.host’” would give
you just the device names and usage values for that host.

If you want the most recent value for each device, something like:

“select last(usage) from table where hostname = ‘specific.host’ group by
devicename”

Antony.