topic Re: How to check which value is larger than the other and calculate the gap between them? in Splunk Search

How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 14:32:50 GMT

Hi
I have two values that i need to check which one of them is bigger and calculate the gap between them
how can i do it ?
i tried this :

| rex "Current temp Front block = (?<Front>.+)" | rex "Current temp Rear block = (?<Rear>.+)"|eval gap=coalesce(Front>Rear, Front-Rear, Rear-Front) |table Front Rear gap

But gap is returning empty

Re: How to check which value is larger than the other and calculate the gap between them?

nickhills — Wed, 20 Mar 2019 14:40:09 GMT

If you don't mind (possible) negative values.

| rex "Current temp Front block = (?<Front>.+)" | rex "Current temp Rear block = (?<Rear>.+)"|eval delta=Front-Rear |table Front Rear delta

If you don't want negative values, use this for delta:

eval delta=case(Front>Rear, Front-Rear, Front<Rear,Rear-Front,Front==Rear,0)

Re: How to check which value is larger than the other and calculate the gap between them?

vnravikumar — Wed, 20 Mar 2019 14:44:28 GMT

Try this

....|eval gap=if(Front>Rear, Front-Rear, Rear-Front) |table Front Rear gap

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 14:48:49 GMT

delta is empty

this is how the table looks like

Front   Rear    delta
  2789       
            2797     
  2788       
            2797

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 14:49:21 GMT

it is exactly what i did and said that gap is empty 🙂

Re: How to check which value is larger than the other and calculate the gap between them?

nickhills — Wed, 20 Mar 2019 14:51:52 GMT

Are the values numeric??
What values does the table produce for Front and Rear?

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 15:01:14 GMT

i pasted an example of the data
the values are numeric

for example :
Front = 2789
Rear = 2797

maybe the problem is that each raw in the table contains only one of the values ?

Front   Rear    delta
  2789       
          2797   
  2788       
          2797

Re: How to check which value is larger than the other and calculate the gap between them?

nickhills — Wed, 20 Mar 2019 15:05:35 GMT

Oh, they are separate events! that's why.
Is there anything common to both events which relates them?
Something like a host/device name or ID?

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 15:07:21 GMT

i can extract SerialNumber

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 15:10:40 GMT

also, i have this 2 values every minute, i need to check the gap for each minute so if:

_time   SerialNumber    Front   Rear    delta
2019-03-07 11:17:26 570123    2789       
2019-03-07 11:17:26 570123        2797   
2019-03-07 11:16:26 570123    2788       
2019-03-07 11:16:26 570123        2797

i will have to check it once for 11:17 and once for 11:16

Re: How to check which value is larger than the other and calculate the gap between them?

nickhills — Wed, 20 Mar 2019 15:28:28 GMT

Try this

| rex "Current temp Front block = (?<Front>.+)" | rex "Current temp Rear block = (?<Rear>.+)"|timechart last(Front) as Front last(Rear) as Rear span=1m|eval delta=case(Front>Rear, Front-Rear, Front<Rear,Rear-Front,Front==Rear,0)

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 20 Mar 2019 17:32:08 GMT

this is the error i get:

The specified span would result in too many (>50000) rows.

and also delta is still empty and SerialNumber got empty and the timestamp become without hour:

_time   SerialNumber    Front   Rear    delta
2019-01-23        2781    2791   
2019-01-24        2857    2865   
2019-01-25        2760    2769

Re: How to check which value is larger than the other and calculate the gap between them?

mhoogcarspel_sp — Sun, 24 Mar 2019 09:38:58 GMT

Because you have your values in different events you need to combine them into one event somehow

| makeresults count=12 
| eval SerialNumber="thesameserial1234" 
| streamstats count 
| eval _time=_time+(floor(count/2))*60
| eval Front=if(count%2=0, null(), count) 
| eval Rear=if(count%2=0, count, null()) 
| bin _time span=1m 
| stats values(Front) AS Front values(Rear) AS Rear BY _time SerialNumber 
| eval gap=abs(Front-Rear)

| makeresults count=12 
| eval SerialNumber="thesameserial1234" 
| streamstats count 
| eval _time=_time+(floor(count/2))*60
| eval Front=if(count%2=0, null(), count) 
| eval Rear=if(count%2=0, count, null()) 
| transaction maxspan=1m  _time SerialNumber

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Tue, 29 Sep 2020 23:48:15 GMT

this is the result i got :
Front Rear _raw _time closed_txn count duration eventcount field_match_sum linecount
12 2019-03-24 16:28:11 0 12 0 1 1 1
11 10

2019-03-24 16:27:11 0

10
11
0 2 2 2
9 8

2019-03-24 16:26:11 0

8
9
0 2 2 2
7 6

2019-03-24 16:25:11 0

6
7
0 2 2 2

no idea what is going on there
maybe you can explain to me the search command ?

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 27 Mar 2019 11:51:24 GMT

do you have any idea how to solve it ?

Re: How to check which value is larger than the other and calculate the gap between them?

FrankVl — Wed, 27 Mar 2019 13:27:10 GMT

Assuming you have serialnumber, Front and Rear extracted, just add the following to your search:

| stats max(Front) as Front max(Rear) as Rear by _time,serialnumber | eval delta=abs(Front-Rear)

The stats command combines the two rows with same time and serialnumber, the eval calculates the delta (using abs, so result is always positive and you don't need to first check which one is bigger).

A search incl. your sample data to demonstrate it works:

| makeresults | eval serialnumber=570123 | eval Front=2788
| append [ | makeresults | eval serialnumber=570123 | eval Rear=2797  ]
| append [ | makeresults | eval serialnumber=570123 | eval Front=2789 | eval _time=_time+60  ]
| append [ | makeresults | eval serialnumber=570123 | eval Rear=2797 | eval _time=_time+60  ]
| stats max(Front) as Front max(Rear) as Rear by _time,serialnumber | eval delta=abs(Front-Rear)

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 27 Mar 2019 13:39:23 GMT

delta is still empty 😞

_time               SerialNumber  Front  Rear                 delta
2019-01-23 11:48:18 570123    2776    2766   
2019-01-23 11:49:18 570123    2948    2941   
2019-01-23 11:50:19 570123    2940    2938   
2019-01-23 11:51:19 570123    2930    2934   
2019-01-23 11:52:20 570123    2921    2931   
2019-01-23 11:53:20 570123    2912    2925

Re: How to check which value is larger than the other and calculate the gap between them?

FrankVl — Wed, 27 Mar 2019 14:01:18 GMT

If merging the rows using the stats command works as expected (which your sample output seems to suggest), then there is no reason that eval part wouldn't work. Can you perhaps post a screenshot that shows the query and the output (mask sensitive details if needed)?

Unless the way you extract the Front and Rear values causes those fields to contain whitespace (which means they are not numeric). Perhaps change the .+ in your regex to \d+.

Re: How to check which value is larger than the other and calculate the gap between them?

sarit_s — Wed, 27 Mar 2019 14:08:51 GMT

unfortunately i don't have enough karma points to upload images
but changing .+ to \d+ returning Front and Rear empty

Re: How to check which value is larger than the other and calculate the gap between them?

FrankVl — Wed, 27 Mar 2019 14:13:27 GMT

You can always post a link to an image uploaded to some random imagehost 🙂

That seems to suggest the raw data perhaps contains more than 1 space before the number? Can you post some raw data? Or perhaps try this regex (similar for the other):

| rex "Current temp Front block =\s+(?<Front>\d+)"