Display data only if today_data-yesterday_data is grater than a number

[ngerosa]

Hi,
every day I extract from DB a data as inputs in my index.
The column that I extract is: NODE_A | NODE_Z | VALUE | TIMESTAMP
I want to view the data only if the value of today for NODE_A and NODE_Z is grater than 2.
Ex: NODE_EX_A | NODE_EX_Z | 15 | dd-mm-yyyy hh:mm:ss

I want to view in a chart the trend of VALUE only if for the same couple NODE_EX_A NODE_EX_Z I have today a VALUE grater than VALUE_yesterday+2

Eg:

NODE1 NODE2 17dB 25-05-2017 10:29:29
NODE3 NODE4 12dB 25-05-2017 10:29:29
NODE1 NODE2 19.5dB 26-05-2017 10:29:29
NODE3 NODE4 13dB 26-05-2017 10:29:29

I want to view a table only with:

NODE1 NODE2 17dB 25-05-2017 10:29:29
NODE1 NODE2 19.5dB 26-05-2017 10:29:29

Because the difference between 19.5dB-17dB is grater than 2dB.

Thanks

[woodcock · Accepted solution]

This should do it:

index=YourIndexHere sourcetype=YourSourcetypeHere
| rex field=VALUE mode=sed "s/dB$//"
| eval _time = strptime(TIMESTAMP, "%d-%m-%Y %H:%M:%S")
| sort 0 - _time
| streamstats current=f last(VALUE) AS VALUE_tomorrow BY NODE_A NODE_Z
| reverse
| streamstats current=f last(VALUE) AS VALUE_yesterday BY NODE_A NODE_Z
| where VALUE >= (VALUE_yesterday + 2) OR VALUE <= (VALUE_tomorrow - 2)
| foreach VALUE* [ fieldformat <<FIELD>> = <<FIELD>> . "dB" ]

[ngerosa]

This works! Another thing that I want to do is to save every couple of NODE_A NODO_Z that have VALUE >= (VALUE_yesterday + 2) so I can have a list of all couples that have problem.
Thanks

[woodcock]

Don't forget to click Accept to close the question.

[somesoni2]

Give this a try

your current search giving field NODE_A, NODE_Z,VALUE , TIMESTAMP
| eval _time=strptime(TIMESTAMP,"%d-%m-%Y %H:%M:%S")
| sort 0  _time NODE_A NODE_Z
| streamstats current=f window=1 values(VALUE) as valYest values(_time) as timeYest by NODE_A NODE_Z
| sort 0  -_time NODE_A NODE_Z
| streamstats current=f window=1 values(VALUE) as valTom values(_time) as valTom by NODE_A NODE_Z
| WHERE (VALUE>=valYest+2 OR VALUE<=ValTom-2) 

[woodcock]

I am struggling to understand. It will be much easier if you show sample data and a mockup of the desired output with a little bit of description on the conversion between.