Can't search modified _time value using earliest/latest requests.

[rlaan]

I ran into a timeformatting issue with some of my logs due to the string starting with the following time format resulting in the indexing of months of events as a 24 hour clock (AM/PM events are both in the same 12 hour time frame.
"<26-Mar-2021 12:59:56 o'clock AM MDT> <Error>......"

I am trying to run a search based on a dashboard panel that is using the make results command to gather the index times that are +- 12 hours surrounding the event i am searching, i then use rex and formatting to try to correct the _time to proper AM/PM values, this appears to work up to the point before the "| search" command. 

I am unable to use earliest/latest values to do a second time bounded search on the corrected time stamps. I am hoping someone can offer some advice of how to fix the time stamps to properly reflect _time values, and then search those results for a subset of events to report on.

.conf files have already been updated to fix the _time when indexed moving forward, i am just stuck trying to evaluate the older data.

Data/duration are example data (these are populated by drilldown options via a dashboard)

index=myindex source=mysource [ 
  | makeresults 
  | eval duration="00:16:35"
  | eval earliest=round(strptime("2021/03/24 11:23:90","%Y/%m/%d %H:%M:%S")-43200)
  | rex field=duration "(?<hour>\d*?):(?<min>\d*?):(?<sec>\d*)"
  | eval latest=earliest+(hour*3600)+(min*60)+sec+43200
  | dedup earliest,latest
  | table earliest,latest
  | format
]
| rex "^<(?<raw_time>.*?)>.*" 
| eval _time=strptime(raw_time,"%d-%b-%Y %I:%M:%S o'clock %p %Z") 
| search [ 
  | makeresults 
  | eval duration="00:16:35"
  | eval earliest=round(strptime("2021/03/24 11:23:90","%Y/%m/%d %H:%M:%S"))
  | rex field=duration "(?<hour>\d*?):(?<min>\d*?):(?<sec>\d*)"
  | eval latest=earliest+(hour*3600)+(min*60)+sec
  | dedup earliest,latest
  | table earliest,latest
  | format
]

[ITWhisperer · Accepted solution]

This looks like a bug to me - the documentation seems to imply that time modifiers should work with the search command, however, they don't appear to be being parsed correctly (at least I couldn't see it in my job log).

To work around this, you could use the where command instead but you would need to calculate values for _time in order to get this to work.

Try something like this:

index=myindex source=mysource [ 
  | makeresults 
  | eval duration="00:16:35"
  | eval earliest=round(strptime("2021/03/24 11:23:90","%Y/%m/%d %H:%M:%S")-43200)
  | rex field=duration "(?<hour>\d*?):(?<min>\d*?):(?<sec>\d*)"
  | eval latest=earliest+(hour*3600)+(min*60)+sec+43200
  | dedup earliest,latest
  | table earliest,latest
  | format
]
| rex "^<(?<raw_time>.*?)>.*" 
| eval _time=strptime(raw_time,"%d-%b-%Y %I:%M:%S o'clock %p %Z") 
| where _time>=strptime("2021/03/24 11:23:90","%Y/%m/%d %H:%M:%S") AND _time<=strptime("2021/03/24 11:23:90","%Y/%m/%d %H:%M:%S")+(tonumber(substr("00:16:35",1,2))*60+tonumber(substr("00:16:35",4,2)))*60+tonumber(substr("00:16:35",7,2))