Splunk Search

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

AKG1_old1
Builder

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.

alt text

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

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

AKG1_old1
Builder

It worked with minor modification. Thank you @woodcock 🙂

0 Karma

woodcock
Esteemed Legend

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
0 Karma

AKG1_old1
Builder

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.

alt text

alt text

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

AKG1_old1
Builder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

AKG1_old1
Builder

@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>
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

AKG1_old1
Builder

Thanks @cusello. 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.

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