topic Re: How to find events that haven't happened in a given amount of time? in Splunk Search

How to find events that haven't happened in a given amount of time?

atebysandwich — Thu, 20 Apr 2023 16:44:35 GMT

I'm looking over vulnerability scan data and have the _time field formatted as

| eval Last_Scanned = strftime(time, "%F")

How can I created a search to show hosts(events) that have not been scanned within two weeks of the current date?

Re: How to find events that haven't happened in a given amount of time?

rut — Thu, 20 Apr 2023 17:32:03 GMT

Grouping by host and then filtering using relative_time should work. This only leaves you the date and host though, so maybe you'll want to add some fields to the stats command.

| stats max(_time) as Last_Scanned by host | where Last_Scanned<relative_time(now(), "-2w")

Re: How to find events that haven't happened in a given amount of time?

PickleRick — Thu, 20 Apr 2023 18:05:57 GMT

It depends whether you can find those hosts by expanding your time range. If you can, just find max(_time) by host and check if it falls within needed range. If you can't, you must have a static list of hosts to compare events in your index with. You can't find something if it's not there.

Re: How to find events that haven't happened in a given amount of time?

atebysandwich — Thu, 20 Apr 2023 18:24:32 GMT

I ad to do some tweaking to make some of it work. when I did

| stats max(Last_Scanned) by IP

I got all the IPs and their last scan time. However, when I did the second line, no results were found.

It should be noted that earlier in the search _time was specified as time

Re: How to find events that haven't happened in a given amount of time?

rut — Thu, 20 Apr 2023 18:41:51 GMT

OK, so are you adding the lines on _time or your formatted time? In your original question you added the following line:

| eval Last_Scanned = strftime(time, "%F")

%F = Equivalent to %Y-%m-%d (the ISO 8601 date format).

The command max and the comparison with relative_time are expecting a timestamp, not formatted time. So you can either use the original timestamp or use strptime to transform it back.

See the following docs for more information.

strptime:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions#strptime.28.26lt.3Bstr.26gt.3B.2C.26lt.3Bformat.26gt.3B.29

relative_time:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions#relative_time.28.26lt.3Btime.26gt.3B.2C.26lt.3Bspecifier.26gt.3B.29

Formats:

https://docs.splunk.com/Documentation/Splunk/9.0.4/SearchReference/Commontimeformatvariables#Time_variables

Re: How to find events that haven't happened in a given amount of time?

PickleRick — Thu, 20 Apr 2023 18:53:35 GMT

You can't do max() on non-numerical field. When you did your strftime() you lost the ability to calculate/compare timestamps.

Re: How to find events that haven't happened in a given amount of time?

atebysandwich — Thu, 20 Apr 2023 18:57:46 GMT

I removed strftime and moved a lookup after the searches you mentioned and it worked.