How can I search for all domains for "All Time" and limit results by second criteria -- First seen date of today or yesterday?

[ag100]

Hey All,

Sorry if this is a duplicate, or already been answered, but I've tried numerous ideas from posts, and the documentation, but haven't managed to get something to work, and was hoping someone may have a suggestion.

I'm trying to do a search for all domains, for "All Time", but limit the results that are returned to those with a first seen date of yesterday or today.

This works:

sourcetype=blah... domain=* | eval time=strftime(_time, "%m/%d/%y") | eval yest=strftime(relative_time(time(), "-1d"), "%m%d%y") | stats earliest(time) as start, latest(time) as stop by domain | where start > "10/18/17"

But when I try to use yest as the variable instead of a hard-coded (yesterday's) date, it fails. I've printed out all of the fields in table format, and it doesn't appear that yest is getting populated (though the query works fine when run alone). I've also tried macros, but haven't had much luck with that either. Any thoughts, or suggestions would be greatly appreciated.

Thanks!

[ag100 · Accepted solution]

Thanks - Unfortunately that didn't work (still no result data, BUT... A friend and I sat down, and worked through it. We made a few changes. First, we did add the @d to go back to the earliest time yesterday, and simplified the time to ease the load on Splunk a bit. The big thing with the logic is with where the yesterday variable resides. It has to be after the stats command, is it looks like it (stats) zeroes everything out. Oh how easy it is to forget when you haven't done it for a while.. 🙂 This search works:

sourcetype=bro_http domain=*
| stats earliest(_time) as start, latest(_time) as stop by domain
| eval yesterday=relative_time(now(), "-1d@d") | where start > yesterday
| convert ctime(yesterday), ctime(start), ctime(stop) | sort start

[elliotproebstel]

Wow, so many conversions! How about: sourcetype=blah ... domain=* | yest=relative_time(now(), "-1d@d") | stats earliest(_time) as start, latest(_time) as stop by domain | where start>yest.

I'm not sure what time() does in your example, but I've always used now(). Also, by using -1d@d, you'll snap to the earliest time yesterday, rather than 24 hours ago. If I'm guessing wrong and you actually want 24 hours ago, stick with your -1d.

[ag100]

Thanks - Unfortunately that didn't work (still no result data, BUT... A friend and I sat down, and worked through it. We made a few changes. First, we did add the @d to go back to the earliest time yesterday, and simplified the time to ease the load on Splunk a bit. The big thing with the logic is with where the yesterday variable resides. It has to be after the stats command, is it looks like it (stats) zeroes everything out. Oh how easy it is to forget when you haven't done it for a while.. 🙂 This search works:

sourcetype=bro_http domain=*
| stats earliest(_time) as start, latest(_time) as stop by domain

| eval yesterday=relative_time(now(), "-1d@d") | where start > yesterday
| convert ctime(yesterday), ctime(start), ctime(stop) | sort start

[elliotproebstel]

Ahh, yes! My mistake for aircoding 🙂 Glad you solved it.