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

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!

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