Splunk Search

conditional stats function

amitsehgal
Path Finder

Hi Folks,

I need to use conditional stats
e.g current:

| stats avg(res_time) count(res_time) by transaction

required

| if transaction == tname stats sum(somefield) else count(res_time) by transaction.

only one transaction i need sum but count for rest ?

More Info:
Thanks for the prompt response,Not sure if i'm able to explain the issue properly. Here are details. I had tried eval as you had suggested but since every transaction is one event. I have no issue with avg which i'm already calculating in eval.

my event line is:

"2011-11-22 00:00:00 tname res_time trans_count" 

with value let's say:

"2011-11-22 00:00:00 SELECT 5 3" 

|eval res_time = if (transaction =="tname",res_time/trans_count,res_time) 
| stats by avg(res_time),count(res_time)

but requirement is trans_count should be summed up in count for taking volume of transaction and one way was doing is sum(trans_count) only for this transaction and simple count(res_time) for all others since they have one occurrence (trans_count):

so that why i need conditional sum for one type of transaction and count for rest.

Thanks,
Amit

Tags (3)
0 Karma

raziasaduddin
Path Finder

Use built in sum+eval combos:

.. | stats count(eval(action=="Denied")) as denies by source_ip

You can use an eval(field="value") inside of a sum or count inside of a stats command. Works likea charm.

gkanapathy
Splunk Employee
Splunk Employee

Just compute all values for all, then select which ones you want. There is no significant performance difference in computing extra sums or averages:

| stats avg(res_time) 
        count(res_time) as countrestime
        sum(somefield) as sumsomefield
  by transaction
| eval mydesiredvalue = if(transaction=="tname",sumsomefield,countrestime)
0 Karma

amitsehgal
Path Finder

Thanks...ihad fixed this issue by having sum(eval(tname exists,tnamecount,else 1)

0 Karma

dwaddle
SplunkTrust
SplunkTrust

This may not do exactly what you're looking for, but it's probably close. The idea is to use eval to play with some of the values of the fields so that your stats command can work without needing to be conditional.

| eval tname_value=if(transaction=="tname",somefield,0)
| eval res_time_value=if(transaction!="tname",res_time,0)
| stats sum(tname_value),count(res_time_value) by transaction

The general idea is that eval can do the conditional parts for you where stats cannot. So, using eval we make up some new fields to do your stats computation on -- and use 0 as a filler value.


UPDATE

Actually, I'm not 100% sure this is going to get you exactly where you want to be. It dawned on me right after I posted this that 0 as a filler value will still be counted in your count(res_time_value), and could affect averages and so on. The general plan for using eval to do the conditional part seems sound, but needs some more work...

gkanapathy
Splunk Employee
Splunk Employee

you can avoid the 0 filler by just using null(). Then neither count() nor sum() will include the filler.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...