Splunk Search

How to select data within selected timerange on particular fields?

imanpoeiri
Communicator

Hi Splunkers,

I understand we can re-write _time with particular timefield with this formula eval _time=strptime(time_submitted, "%m/%d/%Y") but if I re-write the _time, I notice that the time pickers on the right of the search box are not working. I can't select for data Yesterday, or Month to date, etc...

Is there a way to select timerange with the fields that we like?

The search that I am looking to run similar to a SQL query:

SELECT time_created, time_completed, order_id
FROM myTable
WHERE time_created > "mm-dd-yyyy hh:mi:ss"

Appreciate for the response!

somesoni2
Revered Legend

Try this workaround (first we need to override the timerange picker from the base search (all time) and then provided your custom condition)
If time_submitted is already in epoch format

index=blah sourcetype=foo earliest=0 time_submitted> [|gentimes start=-1 | addinfo | return $info_min_time] 

If time_submitted is not epoch and is in format "%m/%d/%Y"

index=blah sourcetype=foo earliest=0 | where strptime(time_submitted, "%m/%d/%Y")> [|gentimes start=-1 | addinfo | return $info_min_time]
0 Karma

imanpoeiri
Communicator

Hi @somesoni2,

Does |gentimes start=-1 means to search data with condition 1 day back? If so, I couldnt get any results even when I change the value.

I tried -30, and date as follow but still not working.

here is my completed search string:

index=blah Field1="Value1" | eval time_submitted=strptime(time_submitted, "%m/%d/%Y") | eval _time=time_submitted | eval FormattedOrderDate=strftime(_time, "%m/%d/%Y") | stats count(ticket_number) as inflow by FormattedOrderDate |search earliest=-30d@d time_submitted> [|gentimes start=08/01/2015 | addinfo | return $info_min_time]
0 Karma

tom_frotscher
Builder

Hi,

the best would be to chose the correct field for _time at indexing time. if you want _time to be something else, for example it should alwas have the time of field time_created, you should configure this in the sourcetype.

You could also use eval epoch=strptime(time_created, "%m/%d/%Y"), then you have an epoch in field epoch and you can use this field in your search, for example: index=myTable | eval epoch=strptime(time_created, "%m/%d/%Y") | where epoch > 1440490286 | table time_created time_completed order_id

The backfall is, that you still have to chose a time in the default timerangepicker, therefore, you should use a timerange that definitly covers your results.

Greetings

Tom

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!