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

[sarit_s]

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

[FrankVl · Accepted solution]

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)

[sarit_s]

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   

[FrankVl]

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+.

[sarit_s]

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

[FrankVl]

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+)"

[sarit_s]

work like a charm !! thanks !!!!!!!
can you add your answer as answer and not comment so i will be able to accept it ?

[FrankVl]

Great!

You can mark my answer as accepted, the remaining part of the solution (fixing the regex) are comments under that same answer 🙂

https://answers.splunk.com/answering/735899/view.html

[nickhills]

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)

[sarit_s]

do you have any idea how to solve it ?

[sarit_s]

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 

[vnravikumar]

Hi

Try this

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

[sarit_s]

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

[nickhills]

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)

[sarit_s]

delta is empty

this is how the table looks like

Front   Rear    delta
  2789       
            2797     
  2788       
            2797     

[nickhills]

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

[sarit_s]

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   

[nickhills]

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?

[sarit_s]

i can extract SerialNumber

[sarit_s]

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

[mhoogcarspel_sp]

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