Splunk Search
Highlighted

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

Explorer

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(relativetime(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!

0 Karma
Highlighted

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

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.

0 Karma
Highlighted

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

Explorer

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=brohttp 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

0 Karma
Highlighted

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

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

0 Karma
Highlighted

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

Explorer

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=brohttp 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

View solution in original post

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.