Splunk Search

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

rlaan
Path Finder

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
]

 

 

 

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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))
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...