Splunk Search

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

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

0 Karma
1 Solution

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

View solution in original post

0 Karma

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

0 Karma

elliotproebstel
Champion

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

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

0 Karma

elliotproebstel
Champion

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

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Take Action Automatically on Splunk Alerts with Red Hat Ansible Automation Platform

 Are you ready to revolutionize your IT operations? As digital transformation accelerates, the demand for ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...