KQL query showing preceding logs from a specific log-CodePudding

I'm working on a query where I need the log that has a message of "Compromised" in it, then I want it to return the preceding 5 "deny" logs. New to KQL and just don't know the operator, so I appreciate the help!

Current query:

| sort by TimeGenerated
| where SourceIP == "555.555.555.555"
| where TimeGenerated between (datetime(10/20/2021, 16:25:41.750).. datetime(10/20/2021, 16:35:41.750))
| where AdditionalExtensions has "Compromised" or DeviceAction == "deny"

Ideally in my head it would be something like:

Needed query:

| sort by TimeGenerated
| where SourceIP == "555.555.555.555"
| where AdditionalExtensions has "Compromised"  
| \\show preceding 5 logs that have DeviceAction = "deny"

Thank you!

CodePudding user response：

You can use the prev() function

CodePudding user response：

Here's how you do it:

let N = 5; // Number of records before/after records for which Cond is true
YourTable
| extend Cond = (SourceIP == "555.555.555.555") and (AdditionalExtensions has "Compromised") and (DeviceAction == "deny") // The predicate to "identify" relevant records
| sort by TimeGenerated asc
| extend rn = row_number(0, Cond)
| extend nxt = next(rn, N), prv = prev(rn, N)
| where nxt < N or (rn <= N and isnotnull(prv)) or Cond
| project-away rn, nxt, prv, Cond

Note that the sorting is done after the extend, and not before - this is more optimal (it's always best to push down the sorting as further down as possible).

(Courtesy of @RoyO)