Forum Discussion

Florian Wallny's avatar
Florian Wallny
Copper Contributor
Jun 08, 2018

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?

 
  • Noa Kuperberg's avatar
    Noa Kuperberg
    Jun 19, 2018

    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.

  • 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 Wallny's avatar
      Florian Wallny
      Copper 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's avatar
        Ketan Ghelani
        Icon for Microsoft rankMicrosoft

        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

Resources