List the count of hosts by index for each day- My search gave me the right format but incorrect number?

[HX]

I would like to get the number of hosts per index in the last 7 days, the query as below gave me the format but not the correct number.

| tstats dc(host) where index=* by _time index | timechart span=1d dc(host) by index

Any idea? Thanks!

                        Index A Index B Index C Index D Index E Index F Index G Index H Index I Index J
2022-10-05 0               0                0             0               0             0              0              0           0            0 
2022-10-06 0               0                0             0               0             0              0              0           0            0
2022-10-07 0               0                0             0               0             0              0              0           0            0
2022-10-08 0               0                0             0               0             0              0              0           0            0
2022-10-09 0               0                0             0               0             0              0              0           0            0
2022-10-10 0               0                0             0               0             0              0              0           0            0

[bowesmana · Accepted solution]

Your first dc(host) makes a field called 'dc(host)' - it's no longer host. And you don't have a host field anyway from tstats, so you just need to take the values of the hosts field (named from the dc(host) aggregation).

| tstats dc(host) as hosts where index=* by _time index span=1d
| timechart span=1d values(hosts) by index

Note that the span=1d should be common to both tstats and timechart, because as you're not retaining the host name after tstats, you cannot do a dc() in the timechart