Splunk Query stats with time intervals

[Satyapv]

Hello All,

I want to build a splunk query using stats to get count of messages for last 5 min, last 10min and last 15min.Something like below. Kindly let me know how below can be achieved? 

Transaction       Last 5min Vol        Last 10min Vol       Last 15min Vol

A

B

C

[tscroggins]

Hi @Satyapv,

Hi,

Here's another alternative. We'll use internal splunkd components to simulation a field named Transaction.

To see event counts over [-300,0], [-600,0], and [-900,0] seconds:

index=_internal sourcetype=splunkd component=* earliest=-15m latest=now
| rename component as Transaction 
| addinfo ``` assumes a valid latest value ```
| stats
    count(eval(_time>=info_max_time-300)) as "Last 5min Vol"
    count(eval(_time>=info_max_time-600)) as "Last 10min Vol"
    count as "Last 15min Vol"
    by Transaction

To see event counts over [-300,0], [-600,300), and [-900,600) seconds:

index=_internal sourcetype=splunkd component=* earliest=-15m latest=now
| rename component as Transaction 
| addinfo ``` assumes a valid latest value ```
| stats
    count(eval(_time>=info_max_time-300)) as "Last 5min Vol"
    count(eval(_time>=info_max_time-600 AND _time<info_max_time-300)) as "Last 10min Vol"
    count(eval(_time<info_max_time-600)) as "Last 15min Vol"
    by Transaction

You can adjust earliest and latest as needed, but note that the last count will always be inclusive of earliest, i.e. the last 15 minutes for -15m. You adjust the count aggregates to disallow counting events more than 900 seconds (15 minutes) prior to latest:

count(eval(_time>=info_max_time-900)) as "Last 15min Vol"

or

count(eval(_time>=info_max_time-900 AND _time<info_max_time-600)) as "Last 15min Vol"

[PickleRick]

My three cents on general approach to such tasks.

Since "last 15 minutes" and "last 10 minutes" can be expressed in terms of 5-minute periods, you can simply either use a timechart with 5-minute bins or bin manually time to 5-minute buckets and do stats over the 5-minute periods. And then - when you have those 5-minute stats - you can aggregate last two or last three stats to get summarized "last 10 minutes" and "last 15 minutes" values.

It's often useful to see if the problem containing several "parallel" computations cannot be transformed to a single - maybe a bit more detailed - calculation and some form of aggregation after that.

[gcusello]

Hi @Satyapv,

you can use eval to categorize your data:

<your_search>
| eval period=case(
   _time>now()-300,"Last 5min Vol",
   _time>now()-600,"Last 10min Vol",
   _time>now()-900,"Last 15min Vol")
| chart count OVER Transaction BY period

Ciao.

Giuseppe

[Satyapv]

Thank you.

Is there way to combine this in stats instead of chart as i need extract few other fields also as par of stats?

[isoutamo]

Can you give some sample events and how you would like to present results?

[Satyapv]

Hello,

Transaction       Last 5min Vol        Last 10min Vol       Last 15min Vol Timeouts Errors

A

B

C

[yuanliu]

In addition to possible additions such as "Timeout Errors", you case requires an additional consideration.  Using case function (or use timechart command) will count each 5-minute interval separately in disagreement with the semantics of "last 10 min vol" and "last 15min vol".  These terms are cumulative.  Any event in "last 5min" must also be counted in "last 10min" and "last 15min".

Here is a semantic implementation; mvappend satisfies both considerations.

| foreach 5min 10min 15min
    [eval header = mvappend(header, if(_time - relative_time(now(), "-<<FIELD>>") > 0, "Last <<FIELD>> Vol", null()))]
| eval header = mvappend(header, if(log_level == "ERROR", "Timeout Errors", null()),
``` This is error emulation.  Use real condition(s) ```
  if(someother > 0, "Some other count", null()))
| chart count OVER Transaction BY header
| table Transaction "Last 5min Vol" "Last 10min Vol" "Last 15min Vol" "Timeout Errors"

Note the last mvappend evaluation is emulation.  Use your real condition.  Here is a data emulation I use to test the above code; one emulated error condition gives non-zero output.

index=_internal earliest=-15m
| rename sourcetype AS Transaction
``` data emulation above; some events have leg_level "ERROR" ```

This emulation gives the counts like the following:

Transaction	Last 5min Vol	Last 10min Vol	Last 15min Vol	Timeout Errors
dbx_health_metrics	1370	2055	2740	0
dbx_server	0	0	4	0
splunk_python	10	15	20	0
splunk_search_messages	4	2	2	2
splunkd	4736	7390	9833	2779
splunkd_access	388	600	787	0
splunkd_ui_access	244	134	148	0

[isoutamo]

Hi
If query takes long, then maybe you should look if you should use time() instead of now()? 1st gives you current time and 2nd is time when query has started.
r. Ismo
http://docs.splunk.com/Documentation/Splunk/9.2.1/SearchReference/DateandTimeFunctions