topic Re: I want to delete fields whose total value is less than the threshold on a timechart. in Splunk Search

I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Sun, 08 Mar 2020 09:39:03 GMT

index=_internal
| eventstats count by sourcetype
| where count > 100
| timechart span=1m count by sourcetype

note:earliest=-60m

if the total is less than the threshold, I don't want to display the field.
eventstats calculate all events , which is inefficient.

Is there any other good way?

Re: I want to delete fields whose total value is less than the threshold on a timechart.

manjunathmeti — Sun, 08 Mar 2020 16:58:05 GMT

How about using subsearch? I found below query faster than one in the question.

index=_internal earliest=-60m 
    [ search index=_internal earliest=-60m 
    | stats count by sourcetype 
    | where count > 100 
    | fields sourcetype] 
| timechart span=1m count by sourcetype

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Sun, 08 Mar 2020 20:04:27 GMT

thank you for reply, @manjunathmeti
Certainly it is faster than eventstats.

I will ask a little more.

faster:
eventstats < subsearch stats

Re: I want to delete fields whose total value is less than the threshold on a timechart.

spayneort — Mon, 09 Mar 2020 03:44:13 GMT

Timechart with where clause

index=_internal earliest=-60m 
| timechart useother=false span=1m count by sourcetype WHERE count > 100

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Mon, 09 Mar 2020 04:41:32 GMT

thank you for reply, @spayneort
Certainly it is faster than subsearch stats.

I will ask a little more.

faster:
eventstats < subsearch stats < timechart where

Re: I want to delete fields whose total value is less than the threshold on a timechart.

darrenfuller — Mon, 09 Mar 2020 14:43:17 GMT

Well...if we're counting by sourcetype, let me throw this into the ring:

| tstats count where index=_internal [| tstats count where index=_internal earliest=-60m by sourcetype 
                                      | search count > 100 
                                      | fields - count 
                                      | format] 
  by _time span=1m sourcetype
| timechart span=1m sum(count) as count by sourcetype

Mind you, this won't work if you are not working with an OOB Accelerated, or data model, or indexed extraction..ed field... but in this specific case it's marvelous.

./DF

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Tue, 10 Mar 2020 00:18:49 GMT

thank you for reply, @darrenfuller
tstats is faster.
but it can't be used any time.

I will ask a little more.

Re: I want to delete fields whose total value is less than the threshold on a timechart.

woodcock — Tue, 10 Mar 2020 01:25:22 GMT

Like this:

index=_internal
| timechart span=1m count by sourcetype
| rename _span AS span
| untable _time sourcetype count
| eventstats sum(count) AS sourcetype_count BY sourcetype
| where sourcetype_count > 100
| xyseries _time sourcetype count
| rename span AS _span

Or, even better:

|tstats count WHERE index=_internal BY sourcetype _time span=1m
| eventstats sum(count) AS sourcetype_count BY sourcetype
| where sourcetype_count > 100
| timechart span=1m count by sourcetype

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Tue, 10 Mar 2020 04:32:45 GMT

thank you for reply, @woodcock
Certainly it is faster than timechart where.

faster:
eventstats <<< subsearch stats << timechart where < untable and xyseries

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Tue, 10 Mar 2020 04:59:43 GMT

index=_internal
| timechart usenull=f useother=f limit=0 span=1m count by sourcetype
| addcoltotals labelfield=_time label=total
| transpose 0 header_field=_time
| where total >100
| transpose 0 column_name=_time header_field=column
| where _time!="total"

This is the fastest in normal SPL.
timechart option, usenull useother limit all are off, it is faster in this case. 

Thank you all.

Conclusion:

Use tstats. It is the fastest. However, it may not be available.
Use where clause. this is easy to understand and faster.
To dashboard, @woodcock or @to4kawa 's solution is good. they are more faster and useful. Of course, you can use it ordinary.
subsearch can reduce the number of searches.
eventstats is slow. It is better to use another way.

@spayneort
your solution is good and fast.
I want to describe it more, so I made it like this. sorry.

Re: I want to delete fields whose total value is less than the threshold on a timechart.

woodcock — Tue, 10 Mar 2020 05:33:57 GMT

So I win then?

Re: I want to delete fields whose total value is less than the threshold on a timechart.

to4kawa — Tue, 10 Mar 2020 05:52:56 GMT

my query is faster a little .