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.
Like this:
Your Search Here
| streamstats current=false window=2 range(_time) AS diffoflastchange
| timechart span=1d avg(diffoflastchange)
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
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)
or use eval diffoflastchange =strftime(diffoflastchange,"%HH:%MM:%SS")
no I don't think this produces accurate results - I'd like to see avg in HH:MM:SS by day
It currently gives the result in seconds. You can format in duration format using tostring
function of eval. See this for example
https://answers.splunk.com/answers/367836/how-to-convert-the-output-of-tostring-or-convert-a.html
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