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!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...