topic Specify specific time range in query in Splunk Search

Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 16:02:30 GMT

Hello Splunkers

I have an IIS log that I am testing against and I have a need to test for a specified range

The _time field in the log is formatted like this 2020-08-23T21:25:33.437-0400

2020-08-23T21:25:33.437-0400

I want to query everything between 21:25:33 and 21:25:43

2020-08-23T21:25:33.437-0400
2020-08-23T21:25:34.133-0400
2020-08-23T21:25:35.267-0400
2020-08-23T21.25:36:42.683-0400
2020-08-23T21:25:37.270-0400
2020-08-23T21:25:38.013-0400
2020-08-23T21:25:39.320-0400
2020-08-23T21:25:40.753-0400
2020-08-23T21:25:41.597-0400
2020-08-23T21:25:42.013-0400
2020-08-23T21:25:43.353-0400

So my search would look something like this. What is the best way to do this?

| where _time < blah _time >= blah

Re: Specify specific time range in query

isoutamo — Tue, 25 Aug 2020 16:17:21 GMT

the easiest way is to use earliest and latest on your query like

index=<your index> earliest="08/23/2020:21:25:33" latest="08/33/2020:21:25:43"

r. Ismo

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 16:18:54 GMT

thanks will test after lunch

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 17:30:41 GMT

Here is my query

I have a CSV lookup file that I am trying to test against because I don't have enough production data

| inputlookup myfile.csv
| search InfoSourceID="2" OR InfoSourceID="3" ErrorCode=*
| where _time < TIME_RANGE_START AND _time >= TIME_RANGE_END
| streamstats reset_after=(isnull(ErrorCode)) count
|Stats latest(eval(if(count>=10,_time,NULL))) as _time

I am reading through this file to test the triggering of an alert when 10 consecutive errors are found in the lookup file. What would be the correct syntax for this line? | where _time < TIME_RANGE_START AND _time >= TIME_RANGE_END

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 17:42:21 GMT

the ten errors in my log I want to trigger my alert on are

Re: Specify specific time range in query

thambisetty — Tue, 25 Aug 2020 18:20:54 GMT

Can you share one sample value of _time from your lookup file?

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 18:28:21 GMT

sure here is the first record

_time

2020-08-23T21:25:33.437-0400

Re: Specify specific time range in query

thambisetty — Tue, 25 Aug 2020 18:35:14 GMT

Is it showing same format when you do |inputlookup yourlookupname | rename _time as testingtime

can you let me know how is the format of testingtime field value.share one sample value of testingtime.

I am trying to understand _time is recognized by Splunk or not.

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 19:05:36 GMT

This is what I am tinkering with now but its still not working.

My file the top 10 results all have a data in the errorcode field and are all InfoSourceID 3

| inputlookup mylookupfile.csv
| search InfoSourceID="2" OR InfoSourceID="3" ErrorCode=*
| eval hourmin = strftime(_time, "%H%M")
| where (hourmin >= 2124 AND hourmin <= 2126)
| streamstats reset_after=(isnull(ErrorCode)) count
|stats latest(eval(if(count>=10,_time,NULL))) as _time

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 19:13:38 GMT

when I rename _time testingtime this is what I see

2020-08-23T21:25:33.437-0400

Re: Specify specific time range in query

isoutamo — Tue, 25 Aug 2020 20:42:10 GMT

here is concept how this can do if you are using lookup file instead of reading those from index. If/when you are looking those directly from index please use my first answer!

index=_internal | head 1 | eval _raw="Time,InfoSourceID,ErrorCode 2020-08-23T21:25:33.437-0400,2,2 2020-08-23T21:25:34.133-0400,2,2 2020-08-23T21:25:35.267-0400,3,3 2020-08-23T21:25:36.683-0400,2,3 2020-08-23T21:25:37.270-0400,3,3 2020-08-23T21:25:38.013-0400,3,2 2020-08-23T21:25:39.320-0400,1,3 2020-08-23T21:25:40.753-0400,1,2 2020-08-23T21:25:41.597-0400,2,2 2020-08-23T21:25:42.013-0400,2,3 2020-08-23T21:25:43.353-0400,3,3" | multikv forceheader=1 | eval TIME_RANGE_START="08/23/2020:21:25:33-0400", TIME_RANGE_END="08/23/2020:21:25:43-0400" | eval time = strptime(Time, "%FT%T.%3Q%z"), TIME_RANGE_START=strptime(TIME_RANGE_START, "%m/%d/%Y:%T%z"), TIME_RANGE_END=strptime(TIME_RANGE_END, "%m/%d/%Y:%T%z") | rename COMMENT AS "Previous was setting up sample data and valuse. Those don't need when reading from index. Also remove above | when ... and use first answer to get data" | table time,TIME_RANGE_START,TIME_RANGE_END, InfoSourceID, ErrorCode| search InfoSourceID="2" OR InfoSourceID="3" ErrorCode=* | where (time >= TIME_RANGE_START) AND (time <= TIME_RANGE_END) | streamstats reset_after=(isnull(ErrorCode)) count

Next time it helps to get correct answer if/when you tell all relevant items

r. Ismo

Re: Specify specific time range in query

irishmanjb — Tue, 25 Aug 2020 21:10:47 GMT

point taken thanks for the response I will give it a shot

thanks

Re: Specify specific time range in query

isoutamo — Tue, 25 Aug 2020 21:36:44 GMT

Excellent , Lets hope that this helps you and Happy splunking 😉

Re: Specify specific time range in query

irishmanjb — Wed, 26 Aug 2020 16:56:46 GMT

Any idea why this would not work? The first 12 lines of my file have data and Errorcodes yet my search yields no returns. It seems that the search is having a hard time with _time format like this.

_time
2020-08-23T21:25:33.437-0400

| inputlookup myfile.csv
| search InfoSourceID="2" OR InfoSourceID="3" ErrorCode=*
| eval hourmin = strftime(_time, "%H%M")
| where (hourmin >= 2124 AND hourmin <= 2126)
| streamstats reset_after=(isnull(ErrorCode)) count
| stats latest(eval(if(count >=10,_time,NULL))) as _time

Re: Specify specific time range in query

isoutamo — Wed, 26 Aug 2020 17:13:42 GMT

I think that it’s better to use some other field than _time in your query when you are reading those from inputlookup (just like I did in my example). When you start to read those from index with real _time then change to it. That way it’s much easier to do and test.
r. Ismo

Re: Specify specific time range in query

irishmanjb — Wed, 26 Aug 2020 17:19:02 GMT

ok so that is why you used that approach

Re: Specify specific time range in query

irishmanjb — Wed, 26 Aug 2020 19:33:19 GMT

thanks for your help with this

Re: Specify specific time range in query

isoutamo — Wed, 26 Aug 2020 19:54:45 GMT

You are welcome.