Splunk Search

Find records between dates entered in text boxes

sarge338
Path Finder

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!

Labels (1)
Tags (3)
0 Karma
1 Solution

sarge338
Path Finder

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

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma

scelikok
SplunkTrust
SplunkTrust

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>
If this reply helps you an upvote and "Accept as Solution" is appreciated.

sarge338
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...