Splunk Search

Determine time value based on count and Average duration

jericksonpf
Path Finder

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

Tags (1)
0 Karma
1 Solution

lguinn2
Legend

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

View solution in original post

jonuwz
Influencer

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

lguinn2
Legend

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

lguinn2
Legend

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

0 Karma

jericksonpf
Path Finder

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

0 Karma

Ayn
Legend

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.

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...