topic Re: How to compute _indextime-_time difference average with tstats? in Splunk Search

How to compute _indextime-_time difference average with tstats?

ctaf — Tue, 29 Sep 2020 14:10:12 GMT

Hi,

I'd like to calculate the average latency (_indextime-_time) with the tstats command, but I can not make it work:

| tstats avg(_indextime-_time) where (index=* OR index=_*) by index

Splunk thinks "_indextime-_time" is a field name. How can I compute the difference in the tstats?

Thank you

Re: How to compute _indextime-_time difference average with tstats?

rjthibod — Mon, 22 May 2017 15:46:29 GMT

You cannot do that kind of eval in tstats and tstats cannot be used to get the individual events out like you would need to.

Instead, you have to do this without tstats

(index=* OR index=_*) 
| fields _time index _indextime 
| fields - _raw
| stats avg(eval(_indextime - _time)) as avg by index

Re: How to compute _indextime-_time difference average with tstats?

somesoni2 — Mon, 22 May 2017 17:16:39 GMT

The tstats command doesn't support _time aggregations except for min/max. Give this version a try

| tstats count WHERE index=* OR index=_* by _time _indextime index| eval latency=abs(_indextime-_time) | stats sum(latency) as sum sum(count) as count by index| eval avg=sum/count

Update
Thanks @rjthibod for pointing the auto rounding of _time. If you've want to measure latency to rounding to 1 sec, use above version. If you want more precide, to the millisecond, use this version.

| tstats count WHERE index=* OR index=_* by _time _indextime index span=1ms | eval latency=abs(_indextime-_time) | stats sum(latency) as sum sum(count) as count by index| eval avg=sum/count

You can specify Time scale in microseconds (us), milliseconds (ms), centiseconds (cs), or deciseconds (ds) for more precision.

Re: How to compute _indextime-_time difference average with tstats?

rjthibod — Mon, 22 May 2017 17:45:22 GMT

I think this approach will always round _time to the closet second, hence throwing off the answers. Double-check by running this

| tstats count WHERE index=* OR index=_* by _time _indextime index | rename _time as time

Re: How to compute _indextime-_time difference average with tstats?

rjthibod — Wed, 24 May 2017 11:52:36 GMT

@ctaf, you might want to reconsider. As my other comment says, this approach rounds off the _time field to the nearest second.

Here is a quick test: run the command @somesoni2 gave and then run mine . If you get two difference answers for the average, then there is a problem. The tstats approach would be faster and better if it didn't round off _time.

Re: How to compute _indextime-_time difference average with tstats?

nunoaragao — Thu, 08 Nov 2018 11:15:58 GMT

| tstats earliest(_time) as etime where index=* by index _indextime
| eval delta=(etime-_indextime)/60 
| eval _time=_indextime  
| timechart span=10m min(delta) by index limit=0