topic Re: Using non timestamp field to produce search for yesterday in Splunk Search

Using non timestamp field to produce search for yesterday

jackreeves — Mon, 27 Nov 2017 15:58:24 GMT

I have a date field called "Closed date" in following format "%Y/%m/%d" that IS NOT my timestamp field & want to create a daily scheduled report that only returns data for "Closed date"=previous day. I've tried a eventstats max(closed date) formula but this trips up as on a rare occasion there will be a close date of today.

Any suggestions??

Re: Using non timestamp field to produce search for yesterday

somesoni2 — Mon, 27 Nov 2017 16:04:48 GMT

Try something like this

index=foo sourcetype=bar... (your base search)...   [| gentimes start=-1 | eval "Closed date"=strftime(relative_time(now(),"-1d@d"),"%Y/%m/%d") | table "Closed date" ]
|...rest of the search

The subsearch would return yesterday's date in same format as field "Closed date" to filter records. Please ensure that the time range (which only works on _time) is selected in way that it includes all the data that may have "Closed date" of yesterday.

Re: Using non timestamp field to produce search for yesterday

jackreeves — Mon, 27 Nov 2017 16:17:29 GMT

That has worked like a charm! Thank you so much. I've never come across the gentimes function before

Re: Using non timestamp field to produce search for yesterday

somesoni2 — Mon, 27 Nov 2017 16:30:36 GMT

I'm using gentimes command to just generate a single row where I can set "Closed date" and return it's value. A more appropriate command for this, for version 6.3+, is | makeresults. (Replacing | gentimes start=-1 with |makeresults in above search).

Re: Using non timestamp field to produce search for yesterday

jackreeves — Mon, 27 Nov 2017 16:38:00 GMT

Thanks, updated search accordingly. Is there any documentation on these functions, would like to understand more?

Re: Using non timestamp field to produce search for yesterday

somesoni2 — Mon, 27 Nov 2017 16:49:11 GMT

Absolutely. Here is the documentation for makeresults specifically. You can find all other search command in the left side tree view.

https://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Makeresults