topic Re: Determine time value based on count and Average duration in Splunk Search

Determine time value based on count and Average duration

jericksonpf — Mon, 28 Sep 2020 12:58:24 GMT

This may not be possible but I work at a SAAS company and we want to start evaluating which of our web methods that are chewing up our web resources on a consistent basis. We have built an event type (_time) that spits out the duration in ms of each method call (_method) for every call.

So what we would like to do is measure the overall "cost" in time duration of each call by multiplying the average time per call by the count of calls in a specific time period. Is there a way to generate a table in splunk that shows avg duration, count of method calls, and cost (avgduration X count)? In other words is there a way to multiply the values in two different columns by each other to get a third column

I guess the query would look something like this:
index="webmethod" eventtype="duration" | stats avg(_time), count(_time), (avg(_time) * count(_time) by Method

Thanks

Re: Determine time value based on count and Average duration

Ayn — Sat, 15 Dec 2012 09:31:44 GMT

Most definitely, but first of all it's important to know that your choice of field names is kind of dangerous if you want sensible results. _time is Splunk's own internal field for event timestamps, so if you overwrite that at search-time you can get into all kinds of weird behaviour. If you're lucky things might come out right in the end but they might as well not. I suggest you use another name for that field - pretty much any other field name than _time 😉 In general, fields starting with _ are considered to be Splunk's internal fields and are treated a bit differently than "normal" fields, so the best thing to do is not to use leading underscores in your own field names.

So, let's say your fields are instead called duration and method. The thing with stats here is that you NEED to use some kind of statistical function, so you can't just multiply things directly. You can use eval statements inside that statistical function, but those eval statements in turn cannot themselves perform statistical functions, so it's a bit of a catch 22 situation.

You can however first calculate the statistics you want using eventstats, and then use those values in a separate eval statement. eventstats works very much like stats except it allows you to do stats "inline" without losing any information to other commands further along the search pipeline.

So, to sum up, something like this should do (I use first as a stats function here because it's a simple way of getting the unique value that's been calculated per method):

index="webmethod" eventtype="duration" | eventstats avg(duration) as avgduration, count by method | eval cost=avgduration*count | stats avg(duration), count, first(cost) as cost by method

You could also do a | dedup method | table avgduration count cost at the end if you like that approach more.

Re: Determine time value based on count and Average duration

lguinn2 — Sat, 15 Dec 2012 09:37:18 GMT

I foresee one problem: you cannot create fields in Splunk with names that start with "_". Also, _time is a reserved name, for the timestamp associated with each event.

Assuming that the eventtype is named "duration" and the actual duration of each call is named ms and the method call is named Method:

index="webmethod" eventtype="duration" 
| stats count as NumCalls avg(ms) as AvgDuration sum(ms) as TotalCost by Method

However, if you really want to compute the NumCalls * AvgDuration, do this:

index="webmethod" eventtype="duration" 
| stats count as NumCalls avg(ms) as AvgDuration sum(ms) as TotalCost by Method
| eval TotalCost = NumCalls * AvgDuration

Re: Determine time value based on count and Average duration

jonuwz — Sat, 15 Dec 2012 13:06:05 GMT

Not that it matters, but why use avgduration*count when you can just sum up the individual durations ?

Re: Determine time value based on count and Average duration

jericksonpf — Mon, 17 Dec 2012 20:00:00 GMT

This worked great Thanks!

index="webmethod" eventtype="duration"
| stats count as NumCalls avg(ms) as AvgDuration sum(ms) as TotalCost by Method
| eval TotalCost = NumCalls * AvgDuration

Re: Determine time value based on count and Average duration

lguinn2 — Mon, 17 Dec 2012 21:57:43 GMT

Oops, you could change the second option to

index="webmethod" eventtype="duration" | stats count as NumCalls avg(ms) as AvgDuration by Method | eval TotalCost = NumCalls * AvgDuration

Though it seems like the first option would be more efficient...