Find records between dates entered in text boxes

[sarge338]

Good Evening,

I have, what appears to be, a unique situation.  I have tried every means that I could find even vaguely related to my problem.

The Scenario

Data, which each record having it's own epoch-based timestamp, is being imported into Splunk weekly.  As a result, indexed timestamps are nowhere near the actual record timestamp.

My dashboard has two text boxes in which the user can input a date range (with formatting guidance) for the records' timestamps which fall between those dates.

The Problem 

No matter how I try to format string inputs, I cannot retrieve the records within those dates.  What's worse is, when I include my WHERE statement, I don't get ANY records returned.  I have been working on this for hours, but I am no closer now than when I began.

The Code 

My input tokens for the text boxes are "date_start" and "date_stop".  The field "eventTime" is the record's timestamp in epoch time.

<query>index=customer  sourcetype=json_no_timestamp custApiKey=d8lwmc9qjd778ksmfy  
| eval _start=strptime($date_start$, "%Y-%m-%d")
| eval _start=strftime(_start, "%s")
| eval _stop=strptime($date_stop$, "%Y-%m-%d")
| eval _stop=strftime(_stop, "%s")
| where (_start &gt;= eventTime) AND (_stop &lt; eventTime)
</query>

Any help would be GREATLY appreciated!

[bowesmana]

As @scelikok you should have the time from the event made as the time recorded in Splunk - note that for each event there is _time (the event time) and _indextime (the time the data was indexed).

You want to ensure that your _time is created based on your event time, as this is what the time picker controls.

You are making a comparison against eventTime - what is that field, what does it look like? You are making a STRING comparison against that data - there's no need, if your eventTime is epoch, then there's no need for strftime

As to your token usage, if your tokens are just the dates like YYYY-MM-DD, then you should ensure they are quoted in the strptime() command, e.g.

<query>index=customer  sourcetype=json_no_timestamp custApiKey=d8lwmc9qjd778ksmfy  
| eval start=strptime($date_start|s$, "%Y-%m-%d")
| eval stop=strptime($date_stop|s$, "%Y-%m-%d")
| where (start &gt;= eventTime) AND (stop &lt; eventTime)
</query>

NB: I removed the _ prefix - that's a Splunk internal character for var names.

The above is also very dependent on the time range of your events.

[scelikok]

Hi @sarge338,

Why don't you index the files using eventTime field as a timestamp? It will be much easier and faster to search. By the way, please try changing variables to without underscore;

<query>index=customer  sourcetype=json_no_timestamp custApiKey=d8lwmc9qjd778ksmfy  
| eval start=strptime($date_start$, "%Y-%m-%d")
| eval start=strftime(start, "%s")
| eval stop=strptime($date_stop$, "%Y-%m-%d")
| eval stop=strftime(stop, "%s")
| where (start &gt;= eventTime) AND (stop &lt; eventTime)
</query>

[sarge338 · Accepted solution]

@scelikok 

That's a great idea!  The person responsible for pulling that data into Splunk is going to do that very thing.  I'm just the newbie report guy who is trying to figure out how to get what I need from Splunk.  😉  

Once the data is indexed based on the eventTime, I should be good to go.

Thanks for the suggestion.