SOLVED

next() function behaviour

%3CLINGO-SUB%20id%3D%22lingo-sub-202165%22%20slang%3D%22en-US%22%3Enext()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202165%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%3CSPAN%3Ei%20have%20file%20server%20data%20in%20Log%20Analytics%20and%20i%20want%20to%20know%20when%20a%20file%20gets%20modified%20after%20remaining%20unchanged%20for%20a%20certain%20amount%20of%20days.%20What%20i%20have%20done%20is%20retrieving%20a%20list%20of%20files%20writes%2C%20sort%20them%20by%20file%20name%20and%20editing%20date%20and%20calculate%20the%20amount%20of%20time%20between%20two%20rows%20using%20the%20following%3A%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%3Elet%20mod%20%3D%20SecurityEvent%0A%7C%20where%20AccessMask%20%3D%3D%20%220x2%22%0A%7C%20summarize%20count()%20by%20ObjectName%0A%7C%20where%20ObjectName%20like%20%22.%22%20and%20count_%20%26gt%3B%201%0A%7C%20sort%20by%20count_%20desc%3B%0ASecurityEvent%0A%7C%20where%20ObjectName%20in%20(mod)%0A%7C%20where%20AccountType%20%3D%3D%20%22User%22%0A%7C%20project%20ObjectName%2C%20TimeGenerated%0A%7C%20sort%20by%20ObjectName%20asc%2C%20TimeGenerated%20desc%0A%7C%20extend%20timerange%20%3D%20TimeGenerated%20-%20next(TimeGenerated)%3C%2FPRE%3E%3CP%3EBut%20of%20course%2C%20results%20are%20sorted%20by%20file%20name%2C%20and%20when%20the%20file%20name%20changes%2C%20i%20don't%20want%20a%20time%20range%20to%20be%20calculated%20because%20it%20is%20not%20the%20same%20file%20anymore.%20I%20would%20expect%20to%20be%20able%20to%20do%26nbsp%3B%3C%2FP%3E%3CPRE%3Eextend%20timerange%20%3D%20iif(ObjectName%20%3D%3D%20next(ObjectName)%2CTimeGenerated%20-%20next(TimeGenerated)%2C%20null)%3C%2FPRE%3E%3CP%3Eto%20only%20calculate%20time%20range%20for%20different%20file%20names.%20I%20get%20%22function%20next%20cannot%20be%20invoked%20in%20current%20context%22.%20Why%20can't%20it%20just%20compare%20the%20current%20file%20name%20to%20the%20next%20and%20only%20calculate%20the%20timerange%20value%20if%20they%20equal%3F%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-202165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eiif%20function%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206903%22%20slang%3D%22en-US%22%3ERe%3A%20next()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%26nbsp%3Bthanks%2C%20that%20helped!%3C%2FP%3E%3CP%3EI%20agree%20that%20it%20might%20be%20useful%20to%20create%20an%20extendif%20function%20to%20enable%20contitioned%20calculated%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205727%22%20slang%3D%22en-US%22%3ERe%3A%20next()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205727%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20I%20see%20what%20you%20mean%2C%20have%20encountered%20that%20before%20(we%20should%20really%20have%20a%20%22nextif%22%20function).%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2Fsubscriptions%2Fe4272367-5645-4c4e-9c67-3b74b59a6982%2Fresourcegroups%2FContosoAzureHQ%2Fworkspaces%2Fcontosoretail-IT%3Fq%3DH4sIAAAAAAAAA3VQQU7DMBC8V%252BofRj3FUgrKGYUbRVzKAe7ItbfFYK%252BR7VCKeDx2qpaEwGm1O7Mzs2spwXmNFg%252BkumDS4eadOM1nX4idczKYT4LyHadKYHPA%252FeaFVFpLR4Wyf6ZAgxmseSUsLhaQrI9rT7hGczWfTeQnu4ZR5SiigG%252FBlzlWxhJnsP3h1Xg0jm6JKchEuk%252FqQyrhTmzIqH7RoCmqwqWPRDnbOtezOOemOnWi7sHR9pExGgkM1FbeWr83vOuvu%252BN8rGcd81fNdntWbtuha43kreddNdZdTszFZRNzqGXz52fq%252F8y%252FAb5Y2%252F7aAQAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EThis%20%3C%2FA%3Eis%20what%20I%20do%20in%20such%20cases%3A%3C%2FP%3E%0A%3CPRE%3Elet%20mod%20%3D%20SecurityEvent%0A%7C%20summarize%20count()%20by%20ObjectName%0A%7C%20where%20ObjectName%20like%20%22.%22%20and%20count_%20%26gt%3B%201%3B%0ASecurityEvent%0A%7C%20where%20ObjectName%20in%20(mod)%0A%7C%20project%20Filename%3DObjectName%2C%20TimeGenerated%0A%7C%20sort%20by%20Filename%20asc%2C%20TimeGenerated%20desc%0A%7C%20extend%20NextFilename%3Dnext(Filename)%2C%20NextTimeGenerated%3Dnext(TimeGenerated)%20%0A%7C%20extend%20FollowingEventInSeconds%20%3D%20iff(Filename%3D%3DNextFilename%2C%20tolong((TimeGenerated-NextTimeGenerated)%2F1s)%2C%20-1)%0A%7C%20project%20Filename%2C%20FollowingEventInSeconds%3C%2FPRE%3E%0A%3CP%3E(Note%20that%20I've%20removed%20the%20AccessMask%20filter%20to%20match%20our%20demo%20data).%3C%2FP%3E%0A%3CP%3EWhat%20I%20do%20is%20add%20column%20to%20hold%20the%20next%20row's%20filename%20and%20time%2C%20and%20then%20calculate%20the%20time%20diff%20between%20with%20the%20next%20row%20only%20if%20it%20has%20the%20same%20file%20name.%20Otherwise%2C%20I%20put%20%22-1%22%20in%20it%20to%20indicate%20there%20is%20no%20following%20event%20to%20work%20with.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20documentation%20is%20ok%20as%20far%20as%20I%20see%2C%20indeed%20sort()%20did%20the%20serialization%20so%20you%20don't%20need%20to.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205681%22%20slang%3D%22en-US%22%3ERe%3A%20next()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54923%22%20target%3D%22_blank%22%3E%40Noa%20Kuperberg%3C%2FA%3E%20can%20update%20the%20docs.%3C%2FP%3E%0A%3CP%3EYou%20can%20still%20achieve%20the%20scenario%20correct.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ea%20sample%20query%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA13OQQ6CQAwF0D0Jd2hYSaIxHoCFicaVuoALDDOfMEY6ZKaoGA8vsBFctnn%252Ftzl05630xwdY4uhDzxoedC1v0HJRDUg7FmU5ECXeOUl%252BaK%252B161iKvgVlGSVnpWvLmETr3dgwK1pTYRucwPBKYEYUnBcq%252B%252Fk1FfQfJIOgJw1v1d2%252BMQ54CdiQDPJgqyrH8KYJlNFqGd4QD3S5TNPtLsTRF%252BUYMZr7AAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22root%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20AccountType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22Machine%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%2C%20TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20asc%2C%20TimeGenerated%20desc%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eserialize%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20timeDiffSeconds%20%3D%20(TimeGenerated%20-%20next(TimeGenerated))%2F%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Es%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205598%22%20slang%3D%22en-US%22%3ERe%3A%20next()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205598%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20doesn't%20make%20sense%20to%20me.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FWindow-functions%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EDocumentation%20%3C%2FA%3Esays%20that%20the%20%3CEM%3Esort%26nbsp%3B%3C%2FEM%3Eoperator%20emits%20a%20serialized%20row%20set%26nbsp%3B%26nbsp%3Bwhich%20i%20apply%20in%20my%20query%20directly%20before%20the%26nbsp%3B%3CEM%3Eextend%26nbsp%3B%3C%2FEM%3Estatement.%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20i%20add%26nbsp%3B%3CEM%3Eserialize%26nbsp%3B%3C%2FEM%3Ein%20the%20following%20way%3A%3C%2FP%3E%3CDIV%3E%3CPRE%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20in%20(mod)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20AccountType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22User%22%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%2C%20TimeGenerated%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ObjectName%20asc%2C%20TimeGenerated%20desc%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eserialize%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20timerange%20%3D%20iif(ObjectName%20%3D%3D%20next(ObjectName)%2CTimeGenerated%20-%20next(TimeGenerated)%2C%20null%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FDIV%3E%3CP%3Eit%20still%20produces%20the%20same%20error%20message.%3C%2FP%3E%3CP%3EThe%20problem%20seems%20to%20be%20that%20next()%20can't%20be%20applied%20inside%20the%20iif()%20function%2C%20because%20without%20the%20condition%2C%20it%20works%20fine.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205034%22%20slang%3D%22en-US%22%3ERe%3A%20next()%20function%20behaviour%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205034%22%20slang%3D%22en-US%22%3EYou%20will%20need%20to%20serialize%20operator%20before%20you%20can%20use%20next()%20operator.%3CBR%20%2F%3ETable%20%7C%20serialize%20%7C%20extend%20nextA%20%3D%20next(A%2C1)%3CBR%20%2F%3E%7C%20extend%20diff%20%3D%20A%20-%20nextA%3CBR%20%2F%3E%7C%20where%20diff%20%26gt%3B1%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.