topic Re: Splunk Query stats with time intervals in Splunk Search

Splunk Query stats with time intervals

Satyapv — Sat, 13 Apr 2024 04:19:39 GMT

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

Re: Splunk Query stats with time intervals

gcusello — Sat, 13 Apr 2024 05:08:22 GMT

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

Re: Splunk Query stats with time intervals

isoutamo — Sat, 13 Apr 2024 06:28:04 GMT

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

Re: Splunk Query stats with time intervals

Satyapv — Sat, 13 Apr 2024 08:27:30 GMT

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?

Re: Splunk Query stats with time intervals

isoutamo — Sat, 13 Apr 2024 08:33:56 GMT

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

Re: Splunk Query stats with time intervals

PickleRick — Sat, 13 Apr 2024 09:30:24 GMT

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.

Re: Splunk Query stats with time intervals

Satyapv — Sat, 13 Apr 2024 09:31:42 GMT

Hello,

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

Re: Splunk Query stats with time intervals

yuanliu — Sun, 14 Apr 2024 00:13:44 GMT

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

Re: Splunk Query stats with time intervals

tscroggins — Sun, 14 Apr 2024 00:59:56 GMT

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"

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