Specify specific time range in query

[irishmanjb]

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

[isoutamo]

Hi

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 

[irishmanjb]

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

[thambisetty]

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

[irishmanjb]

sure here is the first record

_time

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

[thambisetty]

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.

[irishmanjb]

when I rename _time testingtime this is what I see

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

[isoutamo · Accepted solution]

Hi

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

[irishmanjb]

thanks for your help with this

[isoutamo]

You are welcome.

[irishmanjb]

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

thanks

[isoutamo]

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

[irishmanjb]

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

[isoutamo]

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

[irishmanjb]

ok so that is why you used that approach

[irishmanjb]

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

[irishmanjb]

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

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

[irishmanjb]

thanks will test after lunch