Splunk Search

How to use span with stats?

jpanderson
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

jpanderson
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

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

javiergn
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

jpanderson
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

HeinzWaescher
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

jpanderson
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

m0rt1f4g0
Explorer

Please, send the solution, please

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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