Splunk Search

Conditional-ish Sums

Communicator

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)?

Tags (1)
2 Solutions

Motivator

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)

See also, functions for eval and where

View solution in original post

Splunk Employee
Splunk Employee

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

View solution in original post

Splunk Employee
Splunk Employee

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

View solution in original post

Communicator

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?

0 Karma

Splunk Employee
Splunk Employee

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.

0 Karma

Communicator

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

0 Karma

Communicator

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.

0 Karma

Motivator

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)

See also, functions for eval and where

View solution in original post

Communicator

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

0 Karma