Single Value visualisation for a timechart with sparkline and showing the group by field

[MCW]

Hi expert,

My SPL looks something like:

index=<> sourcetype::<>
| <do some usual data manipulation>
| timechart min(free) AS min_free span=1d limit=bottom1  usenull=f BY hostname
| filldown

What I want to achieve is displaying the outcome as Single Value visualisation with sparkline.

My expectation is to have the very last and smallest value min_free for the time span selected displayed and showing the hostname with the smallest min_free shown in the same visual.

However, I get different outcome. The BY split appears to group data by hostname first and then applies the min_free value as secondary sort criteria.

Following is what I get:

When I modify the SPL timechart to limit=bottom2, I get the following.

What I want with a slightly modified SPL (limit=bottom1 useother=f) is to only display the circled middle one with the Single Value showing both the latest smallest min_free and hostname values.

How can I achieve this?

Thanks,

MCW

[ITWhisperer · Accepted solution]

``` bucket time by day ```
| bin _time span=1d 
``` find minimum for each host for each day ```
| stats min(free) AS min_free BY _time hostname
``` find lowest for minimum for each day ```
| eventstats min(min_free) as lowest by _time
``` find host which has that minimum for each day ```
| eval min_host=if(min_free=lowest,hostname,null())
``` find the latest host which has the daily minumum ```
| eventstats latest(min_host) as latest_lowest
``` just keep that host ```
| where hostname==latest_lowest
``` switch to "chart" format ```
| xyseries _time hostname min_free

[MCW]

Thanks very much ITWhisper, it does work for what I need. Cheers!