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