Archive

Using non timestamp field to produce search for yesterday

jackreeves
Explorer

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??

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

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.

View solution in original post

0 Karma

somesoni2
Revered Legend

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.

View solution in original post

0 Karma

jackreeves
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma

jackreeves
Explorer

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

0 Karma

somesoni2
Revered Legend

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

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!