Forum Discussion

mczelen's avatar
mczelen
Copper Contributor
Jun 16, 2022
Solved

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 ret...
KQL
kusto
microsoft defender for endpoint
monitoring
siem
  • GaryBushey's avatar
    GaryBushey
    Jun 16, 2022

    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
GaryBushey's avatar
GaryBushey
Bronze Contributor
Jun 16, 2022

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

Resources