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!

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