There is no way to base a search on a date time field other than _time when using the datetime picker in the search app. Change my mind. PLEEEEEEASE!!!

[mumblingsages]

All,
I love Splunk as it makes tons of things super simple. Until it comes time to use the date time picker with any other field than _time. I hope that I am just fundamentally not understanding something. Please. Please educate me as to where I'm wrong.

First I'm using splunk enterprise 7.3.3 (upgrading soon). My data is being ingested via the HTTP Event Collector which is told the _time value in the event JSON. More specifically my python sending code creates the following:

    event = {
        'time' : eventTime.epoch, 
        'host' : socket.gethostname(),
        'index' : index,
        'event' : data
    }
    headers = self._getHeader(token)
    try:
        resp = requests.post(endPoint, headers=headers, json=event)
    except Exception as ex:
        self._logging.error("Sending to splunk failed.")
        self._logging.exception(ex)
        resp = None
    return resp

My events all have 3 important fields (dateCreated, dateUpdated, dateSLA) which are included in the data element of the event sent into the HEC. Additionally, in the above code 'time' is set to dateUpdated. Having _time set to dateUpdated is appropriate as I'll be querying on that value most frequently.

Now here's my objective. I want to use this thangy:

to initially limit search results on any datetime field other than _time.

Now, I've been searching splunk answers for several hours trying to find an answer to this. The majority of accepted answers are basically to reassign _time to the field you want to limit on making sure the field you specify in epoch time. Something like so:

[base search]
| eval _time = strptime(<your fieldname here>, "%Y-%m-%dT%H:%M:%S.%f")
| <additional search criteria> 

I tried that. But first let's make sure I have valid data by running a query.

It's a pretty straight forward query that pulls back the pertinent fields along with the fields containing the epoch equivalents. The picker is set for "Today" which is indeed 12/30/2019. As expected _time is only showing us results for today. Additionally we see that the epoch value for _time exactly matches that of the dateUpdated_epoch. This is expected as the event time at time of ingestion is also the dateUpdated_epoch. Cool. Valid data to work with.

Now lets try the generally accepted solution (and again some PLEASE PLEASE PLEASE tell me what I'm doing wrong).

So here we see that _time has indeed been set to the dateSLA_epoch. However, I'm now expecting to see that the only data we have is where the dataSLA is from today, but its clearly not. Somethings wrong. Maybe I need to utilize addinfo and add an addition filter criteria now? Lets try that.

Well thats an interesting result! Lets remove the where clause and see whats going on.

In looking through my data (and you'll have to somewhat trust me on this), there is no single row in which the dateSLA and dateUpdated occur today. This suggests to me that the even though _time has been reassign, the search is still rooted around dateUpdated. Please point out the error of my ways.

--Mark

[mumblingsages]

@woodcock I'd love to know what you make of these observations.

[vnravikumar]

Hi

Try with addinfo

| makeresults 
| addinfo 
| eval dateCreated="30/12/2019 12:23:12" 
| eval dateCreated_epoch = strptime(dateCreated,"%d/%m/%Y %H:%M:%S") 
| where ((dateCreated_epoch < info_max_time) AND (dateCreated_epoch > info_min_time))

[mumblingsages]

@vnravikumar
In the 2nd to last screencap you can see where this was attempted. It does not work.

[vnravikumar]

Hi

Try this and let me know

 index="cases"
    | addinfo 
    | where ((dateSLA_epoch < info_max_time) AND (dateSLA_epoch > info_min_time))
    |table timeH,caseNumber,dateCreated,dateCreated_epoch,dateUpdated,dateUpdated_epoch,dasteSLA,dateSLA_epoch

[mumblingsages]

This has been tried, and while it does further reduce the record count the result is incorrect. The query returns zero results.

I know this to be the incorrect answer because if I set the date/time picker to "All time" and then query on the dateSLA_epoch field over the same date range I do in fact find results.

This would seem to indicate what I suspect, which is that the date time picker cannot be reassigned.
--Mark

[to4kawa]

to initially limit search results on any datetime field other than _time.

try where

I'm sorry. There was no query because I couldn't understand what you wanted to do.

dateSLA AND dateUpdated >= today?

UPDATED:

index="cases"
| addinfo
| where info_min_time <= dateSLA_epoch AND date_SLA_epoch <= info_max_time
| table _time caseNumber dateCreated dateUpdated dateSLA

try this.

[mumblingsages]

@to4kawa ,
I want to limit my search to date ranges in a field other than _time via the date/time picker. A concrete example using the information from my original post would be:

"Show only those events where the dateSLA is within the range of the date/time picker."

Does that clarify the question?

--Mark

[to4kawa]

@mumblingsages
I understand that.
Updating answer.
try it.