Filtering results from inputlookup by date/time

[EricLloyd79]

I have searched for hours on this and can't seem to find a way to do it. I have a .csv file being read in with inputlookup and the first column is _time... I want to be able to run a query that includes grabbing the file with inputlookup and then filtering the results based on a specific date range. I have tried piping earliest/latest afterwards, using gentimes, making the inputlookup into a subsearch and trying to do the earliest/latest search beforehand... alas, I keep coming up short. The closest I can get is the solution found here: http://answers.splunk.com/answers/11194/time-based-inputookup?sort=oldest

But I dont want to have to write the query using unix timestamp defined date rangees (ie. now() -9000) but instead using dates.

Any help would be greatly appreciated. Thanks.
Eric

[aelliott · Accepted solution]

did you attempt to do a strptime on the field |inputlookup myFile.csv | eval mytime=strptime(_time,formatoftime)

http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Commontimeformatvariables

This way you can then use "mytime" as regular date/times.

[EricLloyd79]

Thanks man, with your hint I was able to figure it out. Its ugly but it works. Any suggestion on how it could be improved would be welcomed.

| inputlookup indextest_sourcetype.csv | eval early="2/10/2014" | eval late="2/12/2014" | eval myearlytime=strptime(early, "%m/%d/%Y") | eval mylatetime=strptime(late, "%m/%d/%Y") | where _time >= myearlytime AND _time <= mylatetime

[EricLloyd79]

I have thought about this and tried it but not the way you presented it. Im pondering how to further filter the results with the new mytime variable.

| inputlookup indextest_sourcetype.csv | eval mytime=strptime(_time, "%m/%d/%Y") | where mytime ... or can I add a search here? My preference ultimately would be to be able to use the earliest/latest relational date pickers.. Ill keep experimenting