topic Re: How can we fetch data for specific time interval by comparing time. in Splunk Search

How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Tue, 29 Sep 2020 13:44:33 GMT

Hi,

I have a search query in which I want to display the data for a particular time interval. I have data for 5 days and I want to display only for specific interval (say 1 hrs).

how can we display data in between earliest and latest time.

Search Query:
eventtype=mlc sourcetype=sun_jvm host=27-05-2016-05-27_18_24_42-archive service_name=mxmlc_2016-05-22_20-18-17.gc.log | eval _time = 0 + relative_time | eval earliest_time=strftime(180000,"%Y-%m-%d %H:%M:%S.%3q") | eval latest_time=strftime(183600,"%Y-%m-%d %H:%M:%S.%3q") | table _time, earliest_time, latest_time

Re: How can we fetch data for specific time interval by comparing time.

gcusello — Wed, 30 Sep 2020 02:54:23 GMT

Hi agoyal,
I'm not sure to had understood your need: do you want a single row with earliest_time and latest_time?
if this is your need use something like this:

your_search
| stats min(_time) AS earliest max(_time) AS latest

if instead you want to insert two columns with earliest and latest times to your search, you could use appendcols command:

your_search
| appendcols [search your_search | stats min(_time) AS earliest max(_time) AS latest ]
| table your_fields earliest latest

Bye.
Giuseppe

Re: How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Tue, 18 Apr 2017 11:52:44 GMT

Hi @cusello,

Thanks for reply. I dont want single row and also dont want to display earliest and latest time in additional column.

if you check the screen shot, there is hundrad of rows with time starting from "1970-01-01 01:00:01.795" to "1970-01-05 23:05:49.357" (5 days data).

I want to display only those rows which are in between "1970-01-03 03:00:00.000" and "1970-01-03 04:00:00.000".

Regards
Ankit

Re: How can we fetch data for specific time interval by comparing time.

gcusello — Tue, 18 Apr 2017 12:44:24 GMT

did you tried to directly insert in your main search earliest="03/01/1970:03:00:00" latest="03/01/1970:04:00:00"?
in your example:

eventtype=mlc sourcetype=sun_jvm host=27-05-2016-05-27_18_24_42-archive service_name=mxmlc_2016-05-22_20-18-17.gc.log earliest="03/01/1970:03:00:00" latest="03/01/1970:04:00:00"
| table _time, earliest_time, latest_time

Bye.
Giuseppe

Re: How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Tue, 18 Apr 2017 13:11:33 GMT

@cusello . unfortunately, Its not working. I think we cannot specify earliest and latest time in search query. earliest and latest time shoud be outside the query. but in my case its already set to earliest = 0 and latest =now and i can't chage it as it will inpact the output of search query.

I need condition in search query which consider only those records which fall in that time interval.

      <query>Search query</query>
      <earliest>0</earliest>
      <latest>now</latest>

Re: How can we fetch data for specific time interval by comparing time.

gcusello — Tue, 18 Apr 2017 13:22:16 GMT

No you can use earliest and latest in a search ( see http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/SearchTimeModifiers ).
I used them in this format and run!
you have to specify the full date and time in the correct format "mm/dd/yyyy:HH:MM:SS".
Bye.
Giuseppe

Re: How can we fetch data for specific time interval by comparing time.

woodcock — Tue, 18 Apr 2017 13:58:24 GMT

Like this:

eventtype=mlc sourcetype=sun_jvm host=27-05-2016-05-27_18_24_42-archive service_name=mxmlc_2016-05-22_20-18-17.gc.log | eval _time = 0 + relative_time | eval earliest_time=strftime(180000,"%Y-%m-%d %H:%M:%S.%3q") | eval latest_time=strftime(183600,"%Y-%m-%d %H:%M:%S.%3q") | table _time, earliest_time, latest_time | where _time >= 180000 AND _time <= 183600

Or, better yet, just put it into the base search:

eventtype=mlc earliest=180000 latest=183600 sourcetype=sun_jvm host=27-05-2016-05-27_18_24_42-archive service_name=mxmlc_2016-05-22_20-18-17.gc.log | eval _time = 0 + relative_time | eval earliest_time=strftime(180000,"%Y-%m-%d %H:%M:%S.%3q") | eval latest_time=strftime(183600,"%Y-%m-%d %H:%M:%S.%3q") | table _time, earliest_time, latest_time

Re: How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Tue, 29 Sep 2020 13:41:56 GMT

Thank you @woodcock. The first query should work fine for me but getting some unexpected results. when i am using both less than and greater than condition its not showing any results and when I am using only one condition its trim out the results but its not exactly matching with condition.

Attached screenshot of both scenario.

The second query won't work for me as I modifying _time (eval _time = 0 + relative_time) and can't specify earliest and latest time before this.

Re: How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Tue, 29 Sep 2020 13:44:46 GMT

Thanks @Anonymous. Its not working in my case but if it works fine it won't solve my problem as I am evaluating _time and can't specify earliest and latest before "eval _time = 0 + relative_time" and if i specify after this evaluation its technically incorrect.

Re: How can we fetch data for specific time interval by comparing time.

woodcock — Tue, 18 Apr 2017 15:54:52 GMT

You are not doing what I suggested. It is not working because the you are comparing _time to latest_time and the former is an integer whereas the latter is a string. If you must do it "like" that, use fieldformat instead of eval. See my next answer for full example.

Re: How can we fetch data for specific time interval by comparing time.

woodcock — Tue, 18 Apr 2017 15:57:59 GMT

Try using fieldformat instead of eval like this:

eventtype=mlc sourcetype=sun_jvm host=27-05-2016-05-27_18_24_42-archive service_name=mxmlc_2016-05-22_20-18-17.gc.log
| eval _time = 0 + relative_time
| eval earliest_time=180000
| fiedlformat earliest_time = strftime(earliest_time, "%Y-%m-%d %H:%M:%S.%3q")
| eval latest_time=183600
| fiedlformat latest_time = strftime(latest_time, "%Y-%m-%d %H:%M:%S.%3q")
| table _time, earliest_time, latest_time
| where _time >= earliest_time AND _time <= latest_time

Re: How can we fetch data for specific time interval by comparing time.

AKG1_old1 — Thu, 20 Apr 2017 14:15:08 GMT

It worked with minor modification. Thank you @woodcock 🙂