Splunk Search

How do you calculate the average duration of timestamps?

tb5821
Communicator

I want to calculate the average time between updates for my data — I.E: on average, how often is this data changing?
I'm able to get the changes in data and the delta between those changes by using the streamstats command.

...| table _time namespace diffoflastchange

I end up with the columns above where the important column is diffoflastchange, which is really...

| streamstats current=false  last(count) as prev_count last(_time) as time_of_last_change by namespace
| eval diffoflastchange=now()-time_of_last_change

...so now, I got all my timestamps per above, but I can't figure how to average them together to get the, let's say, daily average over a 2 week period.

0 Karma

woodcock
Esteemed Legend

Like this:

Your Search Here
| streamstats current=false window=2 range(_time) AS diffoflastchange
| timechart span=1d avg(diffoflastchange)
0 Karma

tb5821
Communicator

Let me give a more concrete example of my data since none of these suggestions seem to be working.

_time       
Processed_time      
namespace       
time_of_last_change     
prev_count      
actualchange    
1   2018-11-28 11:15:01     1543421701  sample  1543422601  130701  20
2   2018-11-28 08:15:01     1543410901  sample  1543411801  130681  4 

I got my query to the point to where I get back data like the above - now what I really want is to take these two values which between them is 3hrs and if these were the only two values by namespace for the month, week whatever then my average update time would be ~3hrs - but I can't seem to get that to compute

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

..base search...
| streamstats current=false window=1  last(_time) as time_of_last_change by namespace
 | eval diffoflastchange=_time-time_of_last_change
| timechart span=1d avg(diffoflastchange)
0 Karma

macadminrohit
Contributor

or use eval diffoflastchange =strftime(diffoflastchange,"%HH:%MM:%SS")

0 Karma

tb5821
Communicator

no I don't think this produces accurate results - I'd like to see avg in HH:MM:SS by day

0 Karma

somesoni2
SplunkTrust
SplunkTrust

It currently gives the result in seconds. You can format in duration format using tostringfunction of eval. See this for example
https://answers.splunk.com/answers/367836/how-to-convert-the-output-of-tostring-or-convert-a.html

0 Karma

tb5821
Communicator

looking at this again I think even my eval diffoflastchange is wrong b/c I want that diff to be from the previous time_of_last_change ... hmmm

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...