Where and how to exclude one of two unique values in a timechart's by clause

[essklau]

I am trying to build a timechart in 24-hr increments which shows a count of hosts by version of a software package. However, there are cases where during those 24-hr spans, more than one version is present, and this makes the below search return more hosts than I have. I only want the most recent (highest) version to be returned, but I can't make the search logic work for me.

The search is:

index=sw sourcetype=package | timechart span=24h dc(host) by version

If I add dedup to hosts before the timechart stanza, of course, I only get one event per host for the entire week. I've fumbled around with latest, and last, but haven't gotten a good outcome.

Could anyone suggest an appropriate search to take days in which two host/version combinations appear and remove the events with the lowest version number?

Thank you.

[martin_mueller · Accepted solution]

This should be faster than doing sort | dedup, and doesn't rely on sortable versions:

index=sw sourcetype=package | bucket span=24h _time as day | stats latest(version) as version by host day | rename day as _time | timechart dc(host) by version

The fiddling with the temporary day field is necessary to determine the latest(version) after bucketing the _time down to whole days.

[helge]

VERY cool, thanks Martin!

[martin_mueller]

Cool... I've promoted the comment to an answer so you can mark it as solved.

[essklau]

Martin, tried yours with great success. Thank you.

[somesoni2]

Try this,

index=sw sourcetype=package | eventstats latest(version) as versionToUse | where version=versionToUse| timechart span=24h dc(host) by version

OR

index=sw sourcetype=package [index=sw sourcetype=package | head 1 | table version]| timechart span=24h dc(host) by version

[somesoni2]

Since Splunk gives result in chronological order of _time, I believe first should be the one appearing on top . Best option would be to use 'latest'. Will update the answer.

[linu1988]

you meant last(version) that would be the latest

[linu1988]

Try this,

index=sw sourcetype=package |bucket _time span=24h|sort - version|dedup host,_time| timechart  dc(host) as host by version

Thanks