topic Re: date manipulation in Splunk Search

date manipulation

stephenmoorhous — Mon, 28 Sep 2020 17:30:10 GMT

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

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

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?

Re: date manipulation

pradeepkumarg — Fri, 05 Sep 2014 13:58:00 GMT

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

Re: date manipulation

stephenmoorhous — Fri, 05 Sep 2014 14:47:21 GMT

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

Re: date manipulation

somesoni2 — Fri, 05 Sep 2014 18:22:55 GMT

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

Re: date manipulation

stephenmoorhous — Mon, 08 Sep 2014 13:30:08 GMT

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

Re: date manipulation

stephenmoorhous — Mon, 28 Sep 2020 17:30:55 GMT

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

Re: date manipulation

stephenmoorhous — Tue, 09 Sep 2014 15:11:11 GMT

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

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?