Splunk Search

date manipulation

stephenmoorhous
Path Finder

Hi, I have a simple xml form where the user can pass a start and end date and time to a query like

index=uk earliest=$userStartTime$ latest=$userEndTime$ | etc

this works fine

I want a 2nd query which runs for the same times but for a week earlier - I have tried the following

index=uk earlies=$userStartTime$-1w latest=$userEndTime$-1w | etc

index=uk | eval earliest=relative_time($userStartTime$,"-1w") | eval latest=relative_time($userEndTime$,"-1w") | etc

index=uk | myearliest = strptime($userStartTime$, "%m-%d-%YT%H:%M:%S%z") | eval earliest=relative_time(myearliest,"-1w")| mylatest = strptime($userEndTime$, "%m-%d-%YT%H:%M:%S%z") | eval latest=relative_time(mylatest,"-1w") | etc

but none of these work...

update -
The only working solution I have found so far is to do a search on the time field as per below

index=uk | eval e=strptime("09/09/2014:14:00:00","%m/%d/%Y:%H:%M:%S") | eval l=strptime("09/09/2014:14:10:00","%m/%d/%Y:%H:%M:%S") | eval e=e-3600 | eval l=l-3600 | convert mktime(_time) as d |where d>e AND d<l | table _time d e l

This successfully returns all records in the desired time range but has to search the entire data set first - but there must be a way of modifying the earliest and latest search times?

Tags (1)
0 Karma

stephenmoorhous
Path Finder

The only working solution I have found so far is to do a search on the time field as per below

index=uk | eval e=strptime("09/09/2014:14:00:00","%m/%d/%Y:%H:%M:%S") | eval l=strptime("09/09/2014:14:10:00","%m/%d/%Y:%H:%M:%S") | eval e=e-3600 | eval l=l-3600 | convert mktime(_time) as d |where d>e AND d<l | table _time d e l

This successfully returns all records in the desired time range but has to search the entire data set first - but there must be a way of modifying the earliest and latest search times?

0 Karma

somesoni2
Revered Legend

Try this

index=uk [|gentimes start=-1 | eval earliest=if(match("$userStartTime$","^\d+$"),relative_time("$userStartTime$","-1w"),relative_time(relative_time(now,"$userStartTime$"),"-1w")) | eval latest=if(match("$userEndTime$","^\d+$"),relative_time("$userEndTime$","-1w"),relative_time(relative_time(now,"$userEndTime$"),"-1w")) | table earliest latest] 
| etc
0 Karma

stephenmoorhous
Path Finder

The full search is

index=uk [|gentimes start=-1 | eval earliest=if(match("09/08/2014:12:00:00","^\d+$"),relative_time("09/08/2014:12:00:00","-1w"),relative_time(relative_time(now,"09/08/2014:12:00:00"),"-1w")) | eval latest=if(match("09/08/2014:12:30:00","^\d+$"),relative_time("09/08/2014:12:30:00","-1w"),relative_time(relative_time(now,"09/08/2014:12:30:00"),"-1w")) | table earliest latest] | top punct

0 Karma

stephenmoorhous
Path Finder

hi, i'm sorry but
This gives the following warnings

[subsearch]: No matching fields exist
The specified search will not match any events

I tried removing the

| table earliest latest

and that generates the error

Unable to parse 1410130799 with format: %m/%d/%Y:%H:%M:%S

0 Karma

stephenmoorhous
Path Finder

something like this is close

eval earliest = strptime("09/05/2014:09:01:00" , "%m/%d/%YT%H:%M:%S%z")-604800

but if I pipe it, then it just evaluates the time but does not limit the search to that time range

index=uk | eval earliest = strptime("09/05/2014:09:01:00" , "%m/%d/%YT%H:%M:%S%z")-604800

runs but with no time limit

The ones below give errors

index=uk eval earliest = strptime("09/05/2014:09:01:00" , "%m/%d/%YT%H:%M:%S%z")-604800

index=uk | earliest = strptime("09/05/2014:09:01:00" , "%m/%d/%YT%H:%M:%S%z")-604800

etc

0 Karma

pradeepkumarg
Influencer

You can try converting your time to epoch time and subtracting 604800 (604800 is number of seconds in a week)

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...