topic Re: How to select data within selected timerange on particular fields? in Splunk Search

How to select data within selected timerange on particular fields?

imanpoeiri — Tue, 25 Aug 2015 07:52:41 GMT

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!

Re: How to select data within selected timerange on particular fields?

tom_frotscher — Tue, 29 Sep 2020 07:04:17 GMT

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

Re: How to select data within selected timerange on particular fields?

somesoni2 — Tue, 25 Aug 2015 19:18:48 GMT

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]

Re: How to select data within selected timerange on particular fields?

imanpoeiri — Wed, 26 Aug 2015 02:55:51 GMT

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]