Splunk Search

How to use span with stats?

Path Finder

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
0 Karma
1 Solution

Path Finder

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

View solution in original post

Path Finder

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

View solution in original post

SplunkTrust
SplunkTrust

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

Path Finder

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

0 Karma

Motivator

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

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!