Splunk Search

Specify specific time range in query

irishmanjb
Path Finder

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

0 Karma
1 Solution

soutamo
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

soutamo
SplunkTrust
SplunkTrust

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 

0 Karma

irishmanjb
Path Finder

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

0 Karma

thambisetty
Super Champion

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

————————————
If this helps, give a like below.
0 Karma

irishmanjb
Path Finder

sure here is the first record

 

_time

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


 

0 Karma

thambisetty
Super Champion

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.

 

————————————
If this helps, give a like below.
0 Karma

irishmanjb
Path Finder

when I rename _time testingtime this is what I see

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

0 Karma

soutamo
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

irishmanjb
Path Finder

thanks for your help with this

0 Karma

soutamo
SplunkTrust
SplunkTrust
You are welcome.
0 Karma

irishmanjb
Path Finder

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

 

thanks

 

0 Karma

soutamo
SplunkTrust
SplunkTrust
Excellent , Lets hope that this helps you and Happy splunking 😉
0 Karma

irishmanjb
Path Finder

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

0 Karma

soutamo
SplunkTrust
SplunkTrust

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

0 Karma

irishmanjb
Path Finder

ok so that is why you used that approach

0 Karma

irishmanjb
Path Finder

 

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

0 Karma

irishmanjb
Path Finder

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

0 Karma

irishmanjb
Path Finder

thanks will test after lunch

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!