How to use 2 events and calculate in SPL

[LizAndy123]

So I have in the past used a report which finds a string and then calculates the size left and it came as 1 whole event so was simple.

Now it is coming as 2 events - how do I perform this on the 2 events

1st event  - replies with totalCapacity=12323455667

2nd event - replies with usedCapacity=233445

I need to take away the used from the total and report - and this was possible before as it came as just 1 event and I did an eval CapLeft = totalCapacity - usedCapacity and it worked because everything was in the same event.

1 event contained totalCapacity and userCapacity in the same output

[LizAndy123]

No they do not relate to each other - I am not sure why this just started happening - anyway the totalCapacity never changes so I could hardcode this value for now until I figure something out.

[livehybrid]

Hi @LizAndy123 

You can use stats values(fieldName) as fieldName with an optional by someOtherField if you have a field for which each of these relate (e.g. host)

See my example below:

| makeresults count=2 
| streamstats count 
| eval field=IF(count=1,"totalCapacity", "usedCapacity") 
| eval value=json_array_to_mv("[12323455667,233445]")
| eval value=mvindex(value,count-1)
| eval {field}=value
| eval host="abc"
| table host *Capacity
| stats values(totalCapacity) AS totalCapacity, values(usedCapacity) AS usedCapacity

Please let me know how you get on and consider adding karma to this or any other answer if it has helped.
Regards

Will

[gcusello]

Hi @LizAndy123 ,

is there a common field to use for grouping, e.h. host or transaction_id?

if yes, use it in this way:

<your_search>
| stats 
     values(totalCapacity) AS totalCapacity 
     values(usedCapacity ) AS usedCapacity
     BY common_key
| eval CapLeft = totalCapacity - usedCapacity 

if for the same common_key you can gave more values, use max or min or avg instead of values as function.

Ciao.

giuseppe