tstats command consumes a lot of IO and runs for 20 minutes

[jarush]

I'm running this query to get average event counts per day by index. When I run this, each site does an aggregate 2.5GB/s of IO each for 15 - 30 minutes to satisfy the query. Our understanding is that this is just metadata, so this is fairly surprising. Does anyone know why this is and have a better way to write this query?

| tstats count where (index=* OR index=_*) AND earliest=-w@w+1d latest=-w@w+6d  by index
| eval eventCountPerDay=count/5 
| table index, eventCountPerDay

[jarush]

Does anyone have any thoughts on this one? or any other way to get avg # of events per day by index?

[to4kawa]

|metadata type=hosts index=A
| stats sum(totalcount) as total 
| eval index="A"
| append [     |metadata type=hosts index=B
| stats sum(totalcount) as total 
| eval index="B"]
| append [ ....
| eval day=strptime(relative_time(now(),"-1d"), "%F")
| outputlookup append=t dailyresult.csv

make report daily and search weekly.

[DalJeanis]

Okay, that sounds odd. Also, The earliest/latest in that position looks odd to me. The system may actually be calculating an earliest/latest field for each event or something weird like that.

First, take the earliest/latest out of your search, and use the time picker to set the beginning and ending for the search period.

Run the query. If it takes the same amount of time, then that may be how long it takes given your hardware and data. (It should not be)

You may also try this alternate and see what happens:

 | tstats count as daycount where (index=* OR index=_*)  by index span=1d
 | stats avg(daycount) as eventCountPerDay by index

[jarush]

It seemed to run faster when specifying via the date picker, but it also did more IO (we got up to 10GB/s....). I tried your query and that tosses out an error: Error in TsidxStats: span argument is only supported for _time.

[to4kawa]

 | tstats count as daycount where (index=* OR index=_*)  by index _time span=1d
  | stats avg(daycount) as eventCountPerDay by index

There is a typo.

[jarush]

this has the same impact on the environment.