How to fetch timestamp for dates where events are lesser than average event count?

[Taruchit]

Hello All,

I need to fetch the dates in the past 7 days where events are lesser than average event count.

I used the below SPL: -

|tstats count where index=index_name sourcetype=xxx BY _time span=1d
|eventstats avg(count) AS avg_count

However, in scenario where on a particular day no events are ingested, the result skips those dates, that is does not return the dates with event count as zero.

For example: It skips showing the highlighted rows in the below table: -

_time	count	avg_count
2024-01-01	0	240
2024-01-02	240	240
2024-01-03	0	240
2024-01-04	0	240
2024-01-05	240	240
2024-01-06	240	240
2024-01-07	0	240

And gives below as the result: -

_time	count	event_count
2024-01-02	240	240
2024-01-05	240	240
2024-01-06	240	240

Thus, need your guidance to resolve this problem.

Thanking you

Taruchit

[PickleRick · Accepted solution]

That's "by design". You only generate results for those days when you had results. That's how tstats works.

You need to use timechart along with tstats and use the prestats feature of tstats.

|tstats prestats=t count where index=index_name sourcetype=xxx BY _time span=1d
| timechart span=1d count 

[Taruchit]

Hello @PickleRick,

Thank you for your inputs. It helped to resolve the issue.

It would be very helpful if you could share how the use of prestats helped in this case so that its usage becomes more clear to understand.

Thank you

Taruchit

[PickleRick]

prestats=t is an option which tells tstats to produce results in format apropriate for further processing (most typically by timechart). So the main thing here is the timechart command - it is responsible for creating the timeseries with "empty" days counted as 0.

[Taruchit]

Thank you for sharing your inputs.