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?
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")
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.
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")
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
You can't do max() on non-numerical field. When you did your strftime() you lost the ability to calculate/compare timestamps.
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:
relative_time:
Formats:
I removed strftime and moved a lookup after the searches you mentioned and it worked.