Splunk Search

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

essklau
Path Finder

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.

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
Builder

VERY cool, thanks Martin!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

essklau
Path Finder

Martin, tried yours with great success. Thank you.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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.

0 Karma

linu1988
Champion

you meant last(version) that would be the latest

0 Karma

linu1988
Champion

Try this,

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

Thanks

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...