Splunk Search

Get a field from same time every day, or closest.

vaijpc
Communicator

I would like to get the value of a field from the same time every day (e.g. midday) over a 'long' time period (e.g. a month) That much is easy.

However is it possible, if that is missing, to get the value of a field from the 'closest' time available that day?

I don't much mind whether the closest is +- as long as I understand how its obtained.

Tags (2)
0 Karma
1 Solution

hazekamp
Builder

vaijpc, I believe the following search would get you what you need.

The breakdown:

  1. Perform an initial query based on your time set as earliest_time and an appropriate + offset. The stats command will get the value closest to earliest_time going forward in time.

  2. Join a secondary query based on your time set as latest_time and an appropriate - offset. The stats command will get the value closest to latest_time going back in time.

  3. Compare the time difference between fields returned from each query and take <field> to be the value based on the smaller offset from the desired time.

    <your_search> earliest_time=-12h latest_time=-11h | stats first(field) as field1,first(_time) as time1 | eval timeDiff1=time1-(now()-43200) | join[search <your_search> earliest_time=-13h latest_time=-12h | stats last(field) as field,last(_time) as time2 | eval timeDiff2=(now()-43200)-time2] | eval field=if(timeDiff1<=timeDiff2,field1,field2)

View solution in original post

0 Karma

hazekamp
Builder

vaijpc, I believe the following search would get you what you need.

The breakdown:

  1. Perform an initial query based on your time set as earliest_time and an appropriate + offset. The stats command will get the value closest to earliest_time going forward in time.

  2. Join a secondary query based on your time set as latest_time and an appropriate - offset. The stats command will get the value closest to latest_time going back in time.

  3. Compare the time difference between fields returned from each query and take <field> to be the value based on the smaller offset from the desired time.

    <your_search> earliest_time=-12h latest_time=-11h | stats first(field) as field1,first(_time) as time1 | eval timeDiff1=time1-(now()-43200) | join[search <your_search> earliest_time=-13h latest_time=-12h | stats last(field) as field,last(_time) as time2 | eval timeDiff2=(now()-43200)-time2] | eval field=if(timeDiff1<=timeDiff2,field1,field2)

0 Karma

vaijpc
Communicator

I guess a scheduled search and splunk/bin/fill_summary_index.py could get the job done... not exactly the cleanest solution.

0 Karma

hazekamp
Builder

Sorry. No, this will not help you go get the entire year's midday values in one shot. I would recommend using a script which runs one search for each day and combines the results.

0 Karma

vaijpc
Communicator

I think I can see how this is going to work. The problem is that I want a final result of e.g. an entire year's midday values in one go. I don't think this search will give me that? I'll edit my question to make it clearer.

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