How to use span with stats?

[jpanderson]

My query below does the following:

1. Ignores time_taken values which are negative
2. For each event, extracts the hour, minute, seconds, microseconds from the time_taken (which is now a string) and sets this to a "transaction_time" field
3. Sums the transaction_time of related events (grouped by "DutyID" and the "StartTime" of each event) and names this as total transaction time

I now have a list of StartTimes along the left, and durations along the right. I have thousands of events and the granularity of the time is very small (e.g. there are just seconds or milliseconds between each event).

So it looks something like this (in table form):

StartTime - total_transaction_time
2016-01-25 10:00:00.598000 - 0.124000
2016-01-25 10:00:04.342000 - 0.780000
2016-01-25 10:00:05.153000 - 0.078000
2016-01-25 10:00:07.275000 - 0.546000
...
...

I now want to group these into averages over an hour, so it will look something like:

2016-01-25 10:00 - 0.839100
2016-01-26 11:00 - 0.590000
...
...

Unfortunately I cannot use a "span" argument to the stats command like with a timechart. I've tried using bins/buckets but I can't find many good examples of this.

If I could do this in a way which uses a timechart or another function which takes a "span" argument that would be perfect, as I want to add it to a dashboard which is using "span" on a number of other charts, so I can then control them all off the same control which currently changes the span variable in each search string.

The query:

index=duty time_taken!=-* | rex field=time_taken "(?<H>\d{1,2}):(?<M>\d{2}):(?<S>\d{2})\.(?<uS>\d{6})" 
   | eval transaction_time = tonumber(H)*3600 + tonumber(M)*60 + tonumber(S) + tonumber("0.".uS)
   | stats sum(transaction_time) as total_transaction_time by DutyId, StartTime

[jpanderson · Accepted solution]

Nevermind, sorted it now...

index=duty time_taken!=-* | rex field=time_taken "(?\d{1,2}):(?\d{2}):(?\d{2})\.(?\d{6})" |  bin  _time  AS "TIME" span=1h | convert ctime(TIME)   | eval transaction_time = tonumber(H)*3600 + tonumber(M)*60 + tonumber(S) + tonumber("0.".uS)  | stats avg(transaction_time) as total_transaction_time_h  by  TIME

I think perhaps StartTime wasn't an actual time value so I was having trouble sorting it. I've put the bucket at the start instead, and used the _time values of the events (which we configured to be the same as the StartTime anyway).

[javiergn]

What about something like this:

index=duty time_taken!=-* 
| rex field=time_taken "(?<H>\d{1,2}):(?<M>\d{2}):(?<S>\d{2})\.(?<uS>\d{6})" 
| eval transaction_time = tonumber(H)*3600 + tonumber(M)*60 + tonumber(S) + tonumber("0.".uS)
| bucket StartTime span=1d
| stats sum(transaction_time) as total_transaction_time by DutyId, StartTime

Or this:

index=duty time_taken!=-* 
| rex field=time_taken "(?<H>\d{1,2}):(?<M>\d{2}):(?<S>\d{2})\.(?<uS>\d{6})" 
| eval transaction_time = tonumber(H)*3600 + tonumber(M)*60 + tonumber(S) + tonumber("0.".uS)
| eval StartTime = strptime(StartTime, "%Y-%m-%d %H:%M:%S.%6N")
| bucket StartTime span=1d
| eval StartTime = strftime(StartTime, "%Y-%m-%d %H:%M:%S.%6N")
| chart sum(total_transaction_time) as total_transaction_time over StartTime by DutyId

[jpanderson]

Sorry I just solved it. Thanks for your answer though.
I think perhaps StartTime wasn't an actual time value so I was having trouble sorting it, I've put the bucket at the start instead, and used the _time values of the events (which we configured to be the same as the StartTime anyway).

[HeinzWaescher]

You could use this (if StartTime is in epochtime, otherwise you need to convert it into epochtime)

  | rename StartTime As _time

And then use the timechart command

 | timechart span=1d sum(transaction_time) as total_transaction_time by DutyId

[jpanderson]

Sorry I just solved it. Thanks for your answer though.
I think perhaps StartTime wasn't an actual time value so I was having trouble sorting it, I've put the bucket at the start instead, and used the _time values of the events (which we configured to be the same as the StartTime anyway).

[m0rt1f4g0]

Please, send the solution, please