SOLVED

next() function behaviour

Copper Contributor

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?

 
5 Replies
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

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.

@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
best response confirmed by Florian Wallny (Copper Contributor)
Solution

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.

1 best response

Accepted Solutions
best response confirmed by Florian Wallny (Copper Contributor)
Solution

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.

View solution in original post