Forum Discussion
next() function behaviour
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.
Noa Kuperberg can update the docs.
You can still achieve the scenario correct.
a sample query here
MicrosoftI 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.
Noa Kuperberg thanks, that helped!
I agree that it might be useful to create an extendif function to enable contitioned calculated columns.
