topic Re: Conditional-ish Sums in Splunk Search

Conditional-ish Sums

blurblebot — Tue, 02 Nov 2010 02:48:52 GMT

This is killing me.

I'm trying to sum the bytes crossing my boundary in each direction. For TCP sessions, I have a field of "dir" which indicates whether the TCP session is established from the outside-to-the-inside or from the inside-to-the-outside.

For example, dir=outb means that the client is internal and the server is external.
Obversely, dir=inb means that the client is external and the server is internal.

Now, I also have fields "client_bytes" and "server_bytes" which represent the number of bytes transmitted FROM the client or server, respectively.

So, I want to create "outbytes" and "inbytes".

Inbytes="sum(client_bytes) when dir=inb" PLUS "sum(server_bytes) when dir=outb"

Outbytes="sum(client_bytes) when dir=outb" PLUS "sum(server_bytes) when dir=inb"

So, how do I conditionally extract the value of a field (client_bytes OR server_bytes) according to another field's value (dir=inb OR dir=outb)?

Re: Conditional-ish Sums

southeringtonp — Tue, 02 Nov 2010 03:18:12 GMT

Use eval with if:

...
| eval inbytes=if(dir=inb, client_bytes, server_bytes)
| eval outbytes=if(dir=outb, client_bytes, server_bytes)
| stats sum(inbytes) sum(outbytes)

Re: Conditional-ish Sums

blurblebot — Tue, 02 Nov 2010 04:56:02 GMT

I see how this works, and it makes sense given the following table:

Event1) dir=inb cb=100 sb=10 - after two passes of eval, (IB=100, OB=10)

Event2) dir=outb cb=40 sb=33 - after two passes of eval, (IB=33, OB=40)

So what I'd expect for results are:

sum(OB)=50

sum(IB)=133

However your suggested search gives me identical values for sum(OB) and sum(IB).

So I tested this on a smaller set of data:

event1) dir=inb cb=100 sb=33

event2) dir=outb cb=14 sb=27

...| eval ib=if(dir=inb, cb, sb)|eval ob=if(dir=outb, cb, sb) | stats sum(ib) sum(ob)

and got the results:

sum(ib)=60, sum(ob)=60

So now I'm completely confused.

Re: Conditional-ish Sums

blurblebot — Tue, 02 Nov 2010 04:56:56 GMT

Thanks for the quick reply, but this didn't quite work. See my answer below for explanation.

Re: Conditional-ish Sums

bwooden — Tue, 02 Nov 2010 12:35:31 GMT

I think you want a little of each of the solutions already posted. This search

source=*input.txt | eval inbytes=if(dir="inb",cb,if(dir="outb",sb,"")) | 
eval outbytes=if(dir="outb",cb,if(dir="inb",sb,"")) | 
stats sum(inbytes) as inbytes sum(outbytes) as outbytes

returns this data...

inbytes     outbytes
133     50

where the events are...

Event1) dir=inb cb=100 sb=10
Event2) dir=outb cb=40 sb=33

Re: Conditional-ish Sums

blurblebot — Thu, 04 Nov 2010 02:19:07 GMT

Thanks for the answer. I'm a little fuzzy as to why the nested eval statements are necessary. Is this a bug? I ask because it seems more logical that each event is supposed to be passed through each of the two eval phrases of the search in, say, southerningtonp's answer...

Re: Conditional-ish Sums

bwooden — Thu, 04 Nov 2010 11:06:45 GMT

It is not a bug but a function of the original requirements: The 'inbytes' calculation depends on the value of 'dir'. This is just one way to express that in the search language.

Re: Conditional-ish Sums

blurblebot — Mon, 28 Sep 2020 09:20:26 GMT

I wasn't clear. I'm just trying to understand why nesting is necessary, when southerningtonp's answer SEEMS to make sense. With inbytes, for instance (using his answer), I thought the eval if would assign the value of client_bytes to inbytes if dir=inb, and server_bytes if not. THEN do the same process for the eval outbytes line. I can empirically see that it doesn't, yes, but can you help me understand why not?