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.
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.
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.
VERY cool, thanks Martin!
Cool... I've promoted the comment to an answer so you can mark it as solved.
Martin, tried yours with great success. Thank you.
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
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.
you meant last(version) that would be the latest
Try this,
index=sw sourcetype=package |bucket _time span=24h|sort - version|dedup host,_time| timechart dc(host) as host by version
Thanks