Forum Discussion
KQL - endswith Operator Against an Array of Strings
Hello,
I have a monitoring use-case where I wish find certain events where a FileName ends with a specific subset of extensions (e.g. common ransomware extensions). Using the has_any operator returns too many false positives; I'm looking specifically for filenames with this string at the end.
The below query doesn't find the data I'm looking for, and it does not return a syntax error. Can the endswith operator accept string arrays? Could anyone kindly suggest a solution that returns the intended results?
let extensionList = pack_array(
'.foo1',
'.foo2',
'.bar1',
'.bar2'
);
DeviceFileEvents
| where ActionType has_any ("FileCreated", "FileModified", "FileDeleted")
| where FileName endswith (extensionList)
Thank you all in advance,
mczelen You can create a new column in DeviceFileEvents that uses an array to split the name using the period (in case there is more than one period in the name) and then use array_length-1 to get the extension of the FileName. Then, rather than using pack_array, use datatable to create a new table of the extensions in question and perform a join where the new column matches the column in this new table. Code would look something like what is shown below:
let Extensions = datatable (extension: string) [ '.foo1', '.foo2', '.bar1', '.bar2' ]; DeviceFileEvents | extend fileName="Testfile.Name.foo1" | extend indexArray = split(fileName,'.') | extend extension = strcat(".",indexArray[array_length(indexArray)-1]) | project fileName, indexArray, extension | join Extensions on $left.extension == $right.extension
1 Reply
mczelen You can create a new column in DeviceFileEvents that uses an array to split the name using the period (in case there is more than one period in the name) and then use array_length-1 to get the extension of the FileName. Then, rather than using pack_array, use datatable to create a new table of the extensions in question and perform a join where the new column matches the column in this new table. Code would look something like what is shown below:
let Extensions = datatable (extension: string) [ '.foo1', '.foo2', '.bar1', '.bar2' ]; DeviceFileEvents | extend fileName="Testfile.Name.foo1" | extend indexArray = split(fileName,'.') | extend extension = strcat(".",indexArray[array_length(indexArray)-1]) | project fileName, indexArray, extension | join Extensions on $left.extension == $right.extension