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
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
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
It worked with minor modification. Thank you @woodcock 🙂
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
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.
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.
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
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
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
@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>
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
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.