Difference volume comparison query for the last 30 minutes

[leandromatperei]

Hello, I would like a support for a query to compare the values ​​of the last 30 minutes, if it is below 80% of the volume, generate another column in red or exceed the limit.

Ex:

index="txt" "Retrieving message #"
| timechart span=30m count as server

Command Result:

_time	server
2020-06-16 08:00:00	857
2020-06-16 08:30:00	1605
2020-06-16 09:00:00	4507
2020-06-16 09:30:00	4666
2020-06-16 10:00:00	3798

In this case, the first two volumes were below expectations.

[dmarling · Accepted solution]

Edited to add the requested third column per his original request:

If your goal is to only alert when the data in the current 30 minutes has a greater than 80% increase from the previous 30 minutes this query will accomplish that:

index="txt" "Retrieving message #"
| timechart span=30m count as server
| streamstats window=1 current=f values(server) as last30
| eval AboveThreshold=if(round(((server-last30)/last30),4)>.8, "True", null())

Here are some run anywhere examples with the two use cases you provided to show how it works

| makeresults count=1
| eval server="857
1605
4507
4666
3798"
| makemv server tokenizer="(?<server>\d+)"
| mvexpand server
| streamstats window=1 current=f values(server) as last30
| eval AboveThreshold=if(round(((server-last30)/last30),4)>.8, "True", null())

| makeresults count=1
| eval server="2000
4666
3798"
| makemv server tokenizer="(?<server>\d+)"
| mvexpand server
| streamstats window=1 current=f values(server) as last30
| eval AboveThreshold=if(round(((server-last30)/last30),4)>.8, "True", null())

Same thing but with a below 80% threshhold:

index="txt" "Retrieving message #"
| timechart span=30m count as server
| streamstats window=1 current=f values(server) as last30
| eval BelowThreshold=if(round(((server-last30)/last30),4)<.8, "True", null())

| makeresults count=1
| eval server="857
1605
4507
4666
3798"
| makemv server tokenizer="(?<server>\d+)"
| mvexpand server
| streamstats window=1 current=f values(server) as last30
| eval BelowThreshold=if(round(((server-last30)/last30),4)<.8, "True", null())

| makeresults count=1
| eval server="2000
4666
3798"
| makemv server tokenizer="(?<server>\d+)"
| mvexpand server
| streamstats window=1 current=f values(server) as last30
| eval BelowThreshold=if(round(((server-last30)/last30),4)<.8, "True", null())

[richgalloway]

How is the expectation determined?

[leandromatperei]

I would like a third column with a written value that is above the threshold or not, if it is below 80% of the previous value.

Ex:

2020-06-16 09:00:00	2000
2020-06-16 09:30:00	4666
2020-06-16 10:00:00	3798

In the period from 09:30 until 10:00 the volume is ok, since the data volume is above 80%. However between 09:00 and 09:30 the value was less than 80%, so I would have to alarm.