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

[atebysandwich]

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?

[PickleRick]

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.

[rut · Accepted solution]

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

[atebysandwich]

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 

[PickleRick]

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

[rut]

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

relative_time:

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions#relative_ti...

Formats:

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

[atebysandwich]

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