topic Re: How do I create a derived field using two searches? in Splunk Search

How do I create a derived field using two searches?

SlothB77 — Fri, 07 Sep 2018 15:42:03 GMT

I want to create a derived field using a search string like so:

(host=HostA sourcetype="SourceTypeA" counter="CounterA" avg(ValueA)) /
(host=HostA sourcetype="SourceTypeA" counter="CounterB" avg(ValueB))

This divides the average value when counter=CounterA by the average value when counter=CounterB. I get an error when I plug this search string into the Splunk dynamic search.

I tried this alternative version:

host=Host sourcetype=SourceType counter=CounterA avg(Value) by GroupByField as numerator
counter=CounterB avg(ValueB) by GroupByField as denominator |

eval quotient= numerator/denominator |
table GroupByField, quotient

but no luck.

Re: How do I create a derived field using two searches?

horsefez — Fri, 07 Sep 2018 16:01:51 GMT

Hi @SlothB77,

please provide us some sample data and an expected output with further clarification on what you are expecting to do.

Unfortunately your SPL is just a mess. And I can't figure out what you are trying to do.

Re: How do I create a derived field using two searches?

horsefez — Fri, 07 Sep 2018 16:09:28 GMT

Hi @SlothB77,

I just throw a random suggestion out there.

How about:

host=HostA sourcetype="SourceTypeA"
| stats avg(ValueA) AS average_valueA, avg(ValueB) AS average_valueB by counter
| stats values(*) as *
| ratio=average_valueA/average_valueB

Props go to @DalJeanis for help on the last two lines!

Re: How do I create a derived field using two searches?

renjith_nair — Fri, 07 Sep 2018 16:11:09 GMT

@SlothB77,
Try,

host=Host sourcetype=SourceType |stats avg(eval(if(counter=CounterA,ValueA,null()))) as numerator,
                                       avg(eval(if(counter=CounterB,ValueB,null()))) as denominator 
                                       by GroupByField 
|eval quotient= numerator/denominator

Re: How do I create a derived field using two searches?

SlothB77 — Fri, 07 Sep 2018 16:17:01 GMT

This code worked without error when I plugged in my values and it returned exactly what I was looking for. Well done!