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

[to4kawa]

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?

[to4kawa · Accepted solution]

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:

1. Use tstats. It is the fastest. However, it may not be available.
2. Use where clause. this is easy to understand and faster.
3. To dashboard, @woodcock or @to4kawa 's solution is good. they are more faster and useful. Of course, you can use it ordinary.
4. subsearch can reduce the number of searches.
5. 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.

[woodcock]

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

[to4kawa]

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

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

[woodcock]

So I win then?

[to4kawa]

my query is faster a little .

[darrenfuller]

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

[to4kawa]

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

I will ask a little more.

[spayneort]

Timechart with where clause

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

[to4kawa]

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

I will ask a little more.

faster:
eventstats < subsearch stats < timechart where

[manjunathmeti]

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

[to4kawa]

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

I will ask a little more.

faster:
eventstats < subsearch stats