date manipulation

[stephenmoorhous]

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?

[stephenmoorhous]

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?

[somesoni2]

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

[stephenmoorhous]

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

[stephenmoorhous]

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

[stephenmoorhous]

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

[pradeepkumarg]

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