next() function behaviour

let mod = SecurityEvent | summarize count() by ObjectName | where ObjectName like "." and count_ > 1; SecurityEvent | where ObjectName in (mod) | project Filename=ObjectName, TimeGenerated | sort by Filename asc, TimeGenerated desc | extend NextFilename=next(Filename), NextTimeGenerated=next(TimeGenerated) | extend FollowingEventInSeconds = iff(Filename==NextFilename, tolong((TimeGenerated-NextTimeGenerated)/1s), -1) | project Filename, FollowingEventInSeconds

Florian Wallny
Copper Contributor
Jun 19, 2018
That doesn't make sense to me. https://docs.loganalytics.io/docs/Language-Reference/Window-functionssays that the sort operator emits a serialized row set which i apply in my query directly before the extend statement.
However, if i add serialize in the following way:
SecurityEvent
| where ObjectName in (mod)
| where AccountType == "User"
| project ObjectName, TimeGenerated
| sort by ObjectName asc, TimeGenerated desc
| serialize
| extend timerange = iif(ObjectName == next(ObjectName),TimeGenerated - next(TimeGenerated), null)
it still produces the same error message.
The problem seems to be that next() can't be applied inside the iif() function, because without the condition, it works fine.
- Ketan Ghelani
  Former Employee
  Jun 19, 2018
  Noa Kuperberg can update the docs.
  
  You can still achieve the scenario correct.
  
  a sample query here
  
  SecurityEvent
  
  | where ObjectName contains "root"
  
  | where AccountType == "Machine"
  
  | project ObjectName, TimeGenerated
  
  | sort by ObjectName asc, TimeGenerated desc
  
  | serialize
  
  | extend timeDiffSeconds = (TimeGenerated - next(TimeGenerated))/1s
  - Noa Kuperberg
    Microsoft
    Jun 19, 2018
    I think I see what you mean, have encountered that before (we should really have a "nextif" function).
    
    This is what I do in such cases:
    
    let mod = SecurityEvent | summarize count() by ObjectName | where ObjectName like "." and count_ > 1; SecurityEvent | where ObjectName in (mod) | project Filename=ObjectName, TimeGenerated | sort by Filename asc, TimeGenerated desc | extend NextFilename=next(Filename), NextTimeGenerated=next(TimeGenerated) | extend FollowingEventInSeconds = iff(Filename==NextFilename, tolong((TimeGenerated-NextTimeGenerated)/1s), -1) | project Filename, FollowingEventInSeconds
    
    (Note that I've removed the AccessMask filter to match our demo data).
    
    What I do is add column to hold the next row's filename and time, and then calculate the time diff between with the next row only if it has the same file name. Otherwise, I put "-1" in it to indicate there is no following event to work with.
    
    The documentation is ok as far as I see, indeed sort() did the serialization so you don't need to.

Forum Discussion