Forum Discussion
Florian Wallny
Jun 08, 2018Copper Contributor
next() function behaviour
Hi all,
i have file server data in Log Analytics and i want to know when a file gets modified after remaining unchanged for a certain amount of days. What i have done is retrieving a list of files writes, sort them by file name and editing date and calculate the amount of time between two rows using the following:
let mod = SecurityEvent | where AccessMask == "0x2" | summarize count() by ObjectName | where ObjectName like "." and count_ > 1 | sort by count_ desc; SecurityEvent | where ObjectName in (mod) | where AccountType == "User" | project ObjectName, TimeGenerated | sort by ObjectName asc, TimeGenerated desc | extend timerange = TimeGenerated - next(TimeGenerated)
But of course, results are sorted by file name, and when the file name changes, i don't want a time range to be calculated because it is not the same file anymore. I would expect to be able to do
extend timerange = iif(ObjectName == next(ObjectName),TimeGenerated - next(TimeGenerated), null)
to only calculate time range for different file names. I get "function next cannot be invoked in current context". Why can't it just compare the current file name to the next and only calculate the timerange value if they equal?
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.
- Ketan Ghelani
Microsoft
You will need to serialize operator before you can use next() operator.
Table | serialize | extend nextA = next(A,1)
| extend diff = A - nextA
| where diff >1- Florian WallnyCopper Contributor
That doesn't make sense to me. Documentation says 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
Microsoft
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