Get a Single value for count of hosts for tstats query

[neerajs_81]

Hi, i have a requirement to create single value visual with trendline.  I have looked at sample queries on Dashboard studio examples hub.  Below is my base query.  

|tstats dc(host) as distinct_count where index=okta
sourcetype="OktaIM2:log"

Expected result:  Something like this

I have been trying below 2 searches but neither of two is showing the expected result. 

|tstats dc(host) as distinct_host where index=okta sourcetype="OktaIM2:log"
| chart count(distinct_host) by _time
OR
|tstats dc(host) as distinct_host where index=okta sourcetype="OktaIM2:log"
| timechart count(distinct_host) by _time

If i try the below query without tstats,  it works but i need to use tstats from a performance point of view.

index=okta sourcetype="OktaIM2:log"
| chart dc(host) by _time span=1h

Any suggestion how to generate single value trendline with tstats?

[ITWhisperer · Accepted solution]

If you want any sort of stat based on time, you should include it in the by clause. Try starting with something like this

|tstats dc(host) as distinct_host where index=okta sourcetype="OktaIM2:log" by _time

[neerajs_81]

Thanks, didn't realize we could do a by clause with tstats as well.