Surround Search with KQL

Question

Surround Search with KQL: How can I retrieve five records that were logged (based on a specific datetime column) before and after (again, based on a given datetime column) one/several record(s)?

Reference from Linux logs: we can search for "failed login" and obtain a list of 5 events logged before and after a failed login. The query can be phrased as follows:

$ grep -B 5 -A 5 'failed login' var/log/auth.log

Source: https://www.manageengine.com/products/eventlog/logging-guide/syslog/analyzing-syslogs-with-tools-techniques.html > search "Surround Search".

I tried the next() operator, but it doesn't retrieve the value of the entire record, only the value in a specific column. Example:

cluster("https://help.kusto.windows.net").database("Samples").
StormEvents
| serialize 
| extend NextEpisode = next(EpisodeId,5)
| extend PrevEpisode = prev(EpisodeId,5)
| extend formated_text = strcat("Current episode: ", EpisodeId, " .Next episode: ", NextEpisode, " .Prev episode: ", PrevEpisode)
| where StartTime == datetime(2007-12-13T09:02:00Z)
| where EndTime == datetime(2007-12-13T10:30:00Z)
| project-reorder  formated_text, *

Answer 1

rows_near plugin

cluster("https://help.kusto.windows.net").database("Samples").StormEvents
| order by StartTime asc
| evaluate rows_near(EventType == "Dense Smoke", 5)
| project StartTime, EventType

StartTime	EventType
2007-09-04T18:15:00Z	Thunderstorm Wind
2007-09-04T18:51:00Z	Thunderstorm Wind
2007-09-04T19:15:00Z	Flash Flood
2007-09-04T22:00:00Z	Dense Fog
2007-09-04T22:00:00Z	Dense Fog
2007-09-04T22:00:00Z	Dense Smoke
2007-09-04T22:00:00Z	Dense Fog
2007-09-04T22:00:00Z	Dense Fog
2007-09-05T02:00:00Z	Flash Flood
2007-09-05T04:45:00Z	Flash Flood
2007-09-05T06:00:00Z	Flash Flood
2007-10-17T15:51:00Z	Thunderstorm Wind
2007-10-17T15:55:00Z	Hail
2007-10-17T15:56:00Z	Thunderstorm Wind
2007-10-17T15:58:00Z	Hail
2007-10-17T16:00:00Z	Thunderstorm Wind
2007-10-17T16:00:00Z	Dense Smoke
2007-10-17T16:00:00Z	Thunderstorm Wind
2007-10-17T16:00:00Z	Thunderstorm Wind
2007-10-17T16:03:00Z	Funnel Cloud
2007-10-17T16:05:00Z	Thunderstorm Wind
2007-10-17T16:08:00Z	Hail
2007-11-05T06:00:00Z	Lake-Effect Snow
2007-11-05T06:00:00Z	Winter Storm
2007-11-05T07:00:00Z	Winter Storm
2007-11-05T07:00:00Z	Winter Storm
2007-11-05T07:00:00Z	Winter Storm
2007-11-05T07:00:00Z	Dense Smoke
2007-11-05T07:00:00Z	Winter Storm
2007-11-05T08:44:00Z	Hail
2007-11-05T09:57:00Z	Blizzard
2007-11-05T11:00:00Z	Strong Wind
2007-11-05T11:00:00Z	Strong Wind

Fiddle