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?
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:
tstats
. It is the fastest. However, it may not be available.where clause
. this is easy to understand and faster.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.
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:
tstats
. It is the fastest. However, it may not be available.where clause
. this is easy to understand and faster.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.
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
thank you for reply, @woodcock
Certainly it is faster than timechart where
.
faster:
eventstats <<< subsearch stats << timechart where < untable and xyseries
So I win then?
my query is faster a little .
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
thank you for reply, @darrenfuller
tstats
is faster.
but it can't be used any time.
I will ask a little more.
Timechart with where clause
index=_internal earliest=-60m
| timechart useother=false span=1m count by sourcetype WHERE count > 100
thank you for reply, @spayneort
Certainly it is faster than subsearch stats
.
I will ask a little more.
faster:
eventstats
< subsearch stats
< timechart where
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
thank you for reply, @manjunathmeti
Certainly it is faster than eventstats
.
I will ask a little more.
faster:
eventstats
< subsearch stats