timechart 'distinct_count' combined with 'latest' function

[jbllog]

Hello, 

I would like to create timechart that counts number of tests with different statuses (e.g. statuses 'OK', 'ERROR', 'WARN' etc) for last 30 days (per each day). The problem is that it should take only latest log with status per test (e.g. I have Login test (id 151), it has couple events/logs with different statuses, and I would like to take for that test last log/event with latest status. 

I have a problem to combine 'latest' and 'distinct_count' with timechart. 

When I do following search, I get duplicates of logs for test (e.g. I should have every day count of 62 (tests) for all statuses): 

basesearch
| timechart span=1d distinct_count(test) as tests by status

e.g. on day 2025-05-26 test 'Login test (id 151)' have one event with status 'OK' and another one with status 'Blad', and the duplicate is shown here.


When I want to combine 'latest' to timechart I get distinct_count results only for last day:

basesearch
| stats latest(status) as statuses latest(test) as tests latest(_time) as myTime by test
| eval _time=myTime 
| timechart span=1d distinct_count(tests) by statuses

I appreciate help how to combine timechart, distinct_count and latest all together.

[richgalloway · Accepted solution]

You almost had it.  Use the bin and stats commands to group events by day and get the latest status.  Then timechart will give the counts.

basesearch
| bin span=1d _time
| stats latest(status) as status by _time, test
| timechart span=1d distinct_count(test) by status

[jbllog]

Thank you for the response 🙂 I tried your solution but still have results only for one day. 


I wonder maybe this line may affect the unwanted one-day results: 

status latest(test) as tests latest(_time) as _time

maybe I shouldn't use 'latest' agg function for 'test' and '_time'? But I don't know how to pass these values in a different way to 'timechart' function.

[richgalloway]

Please try my updated query.

[jbllog]

It works! 🙂 Thank you for the solution :)!