KQL - endswith Operator Against an Array of Strings

Occasional Visitor

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,

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