Exclude specific Tag combinations from Query

InfluxDB 1 Kapacitor
,
1

Hi,

I face right now the problem, that I want to perform a query, that exclude some specific combinations of multiple tags.

So for example I have the following situation

Measurement: ipmi_sensor
Tags: server, name, status_desc
Fields: value

I have now for example three servers A, B and C, where A has disk0, B disk1 and C disk0 and disk1

The ipmi_sensor input plugin reads for A, B and C a value for disk0 and disk1, where they have the following combinations of tags
server: name:status_code:value
A: disk0:ok:0
A: disk1:ns:0
B: disk0:ns:0
B: disk1:ok:0
C: disk0:ok:0
C: disk1:ns:0

i want now to create a querry, that only returns C:disk1:ns:0. Since this is a present disk, that failed.

I in A and B there are missing disks, so ns is a valid output, that shouldn’t be shown.

I tried the following query, but it didn’t worked, since the evaluation within the where section isn’t performed with respect to the brackets.

SELECT mean("value") AS "mean_value" FROM "db_server"."autogen"."ipmi_sensor" WHERE time > :dashboardTime: AND ("status_code"='lnr' OR "status_code"='lnc' OR "status_code"='ns') and ("name"='disk0' or "name"='disk1') and ("server"!='A' and "name"!='disk1' and "status_code"!='ns') and ("server"!='B' and "name"!='disk1' and "status_code"!='ns') GROUP BY time(5m), "server", "name", "status_code", "status_desc" FILL(null)

I added “status_code”!=‘ns’ since I wanted to also got a notification if somebody placed a disk into a wrong disk slot.

It would be great to get some advice how to achieve this. I was not able to find any documentation or examples to this question right now.

Later on, I have to add dozens of such combinations. Nevertheless, it seems valuable to me, since the exceptions are only a very small faction of the whole set of combinations.

Best Regards,

Stephan

2

Nobody out there, that can help?

3

Hi @SWalter ,

this is a general SQL problem ,
you said that the evaluation within the where section isn’t performed with respect to the brackets ,

but it is because putting AND conditions between brackets is the same as not using brackets ,
the OR between brackets on the other hand has an influence on the outcome.

I have looked at your query but could not find an alternative query so far …

4

Ok, that would match my observation.

If I understand the Query, it is not possible to just invert the expressions with OR and =, since the result is not equal.

This doesn’t sounds very good, since it seems, that it is not possible to exclude a specific amount of entries from a query.

5

Does something like this work ?

SELECT mean(“value”) AS “mean_value” FROM “db_server”.“autogen”.“ipmi_sensor” WHERE time > :dashboardTime: AND (“status_code”=‘lnr’ OR “status_code”=‘lnc’ OR “status_code”=‘ns’) and
(
(“server”=‘A’ and “name”=‘disk0’ and “status_code”=‘ns’) OR
(“server”=‘B’ and “name”=‘disk1’ and “status_code”=‘ns’) OR
(“server”=‘C’ and ( “name”=‘disk0’ or "name=‘disk1’) and “status_code”=‘ns’)
)
…

6

This would only trigger ns output for the present disks in A, B and C, but it wouldn’t trigger a wrong disk placement within the server - for example disk1 present within A - or other errors than ns.

The nice characteristic of the initial querry would be, that I don’t have to know the status code apriori. It is just ok to know, that OK is ok and everything else is an error, beside specific combinations of fields, where I now, that NS is the valid output.

In addition I wouldn’t be able to catch the “lnr” status code within your example

Ok, we could add this of course like the one before, but at the end with the real system, I will have something like 30 Servers with 100 to 200 valid taks. So something like 30k OR statements. I don’t know if this would work.

For the original Query, that doesn’t work it would be something like 1k special entries. So much less.

7

I don’t know but probably it is possible in FLUX … it has more filtering options

Flux language

8

We haven’t enabled FLUX yet, since it is quite confusing for me.

So before we would do this, we would have to know, that it is working, how to do it and if there are any drawbacks.

9

I will try it out and let you know …

10

Ok, but is FLUX also possible with Kapacitor? I haven’t really found anything, since we have to use at the end Kapacitor to automatically create the alerts.

Or would we have to create new entries through FLUX and read them with Kapacitor?