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

[vaijpc]

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.

[hazekamp · Accepted solution]

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)

[vaijpc]

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

[hazekamp]

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.

[vaijpc]

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.