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

isoutamo
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

isoutamo
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
SplunkTrust
SplunkTrust

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

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

isoutamo
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

irishmanjb
Path Finder

thanks for your help with this

0 Karma

isoutamo
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

isoutamo
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

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