topic Re: How to create a conditional stats search based on a field value? in Splunk Search

How to create a conditional stats search based on a field value?

ronny_wang — Wed, 30 Sep 2020 00:59:03 GMT

Hi,
I am trying to write a conditional stats command based on a field value.
So for example:
I have a field called stat_command

Name, No., stat_command         
Name1, 5, latest
Name2, 12, avg
Name3, 13, max

So for stat_command = latest, I want to run | stats latest(Number)
for stat_command = avg, I want to run | stats avg(Number)

Is there a way to do this in a search-efficient way without doing many appends?
I have tried using macros, map, and case statements but have not had much luck.

Thanks

Re: How to create a conditional stats search based on a field value?

renjith_nair — Fri, 21 Jun 2019 11:59:06 GMT

@ronny_wang,

Does this approach work for you ?

    | eventstats latest(No) as _latest,avg(No) as _avg,max(No) as _Max
    | eval value=case(stat_command=="latest",_latest,stat_command=="avg",_avg,stat_command=="max",_Max)

Get all types of aggregate values using stats and select only the value based on your condition

Re: How to create a conditional stats search based on a field value?

ronny_wang — Wed, 30 Sep 2020 01:03:10 GMT

Hi,

As i had to split by a number of fields, including the Name field, we were able to do the following:
| stats latest(Value) AS latest_value
avg(Value) AS avg_value
max(Value) AS max_value
min(Value) AS min_value
count(Value) AS count_value
BY Name

| eval value=case(stats_command="latest", latest_value,
stats_command="avg", avg_value,
stats_command="min", min_value,
stats_command="max", max_value,
stats_command="count", count_value,
1=1, Value)

Re: How to create a conditional stats search based on a field value?

ronny_wang — Mon, 24 Jun 2019 23:30:52 GMT

Hi, As i had to split by a number of fields, including the name field, I was able to solve this with the following

| stats latest(Value) AS latest_value 
avg(Value) AS avg_value
max(Value) AS max_value
min(Value) AS min_value
count(Value) AS count_value
latest(_time) AS event_time_epoch
BY Name
| eval metric_value=case(stats_command="latest", latest_value,
stats_command="avg", avg_value,
stats_command="min", min_value,
stats_command="max", max_value,
stats_command="count", count_value,
1=1, Value)

Re: How to create a conditional stats search based on a field value?

richgalloway — Mon, 24 Jun 2019 23:34:24 GMT

@ronny_wang If your problem is resolved, please accept an answer to help future readers.