Jun 08 2018
02:50 AM
- last edited on
Apr 07 2022
05:11 PM
by
TechCommunityAP
Jun 08 2018
02:50 AM
- last edited on
Apr 07 2022
05:11 PM
by
TechCommunityAP
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?
Jun 17 2018 07:54 AM
Jun 19 2018 03:35 AM - edited Jun 19 2018 03:37 AM
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.
Jun 19 2018 07:31 AM
@Noa Kuperberg can update the docs.
You can still achieve the scenario correct.
a sample query here
Jun 19 2018 08:56 AM
SolutionI 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.
Jun 22 2018 01:55 AM
@Noa Kuperberg thanks, that helped!
I agree that it might be useful to create an extendif function to enable contitioned calculated columns.
Jun 19 2018 08:56 AM
SolutionI 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.