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
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
Not that it matters, but why use avgduration*count when you can just sum up the individual durations ?
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
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...
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
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.