SOLVED

KQL String Search With Wildcards?

%3CLINGO-SUB%20id%3D%22lingo-sub-3260587%22%20slang%3D%22en-US%22%3EKQL%20String%20Search%20With%20Wildcards%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260587%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20do%20KQL%20string%20searches%20with%20wildcards%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I'm%20hunting%20for%20files%20written%20to%20C%3A%5CProgramData%5C%20but%20I%20don't%20want%20to%20see%20files%20written%20to%20subfolders.%20I've%20done%20this%20in%20Splunk%20so%20I%20was%20surprised%20that%20the%20last%20line%20in%20my%20query%20below%20does%20not%20filter%20out%20anything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShow%20this%3A%20C%3A%5CProgramData%5Cevil.exe%3C%2FP%3E%3CP%3EFilter%20this%20out%3A%20C%3A%5CProgramData%5CMyApp%5CNotEvil.exe%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuery%3A%3C%2FP%3E%3CP%3EDeviceFileEvents%3CBR%20%2F%3E%7C%20where%20ActionType%20%3D%3D%20%22FileCreated%22%3CBR%20%2F%3E%7C%20where%20FolderPath%20contains%20%22ProgramData%22%3CBR%20%2F%3E%7C%20where%20FolderPath%20!contains%20%22ProgramData%5C%5C*%5C%5C*%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3260587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EKQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3260894%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20String%20Search%20With%20Wildcards%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260894%22%20slang%3D%22en-US%22%3E%3CP%3ECould%20use%20regex%20as%20well.%3CBR%20%2F%3E%3CBR%20%2F%3EDeviceFileEvents%3C%2FP%3E%3CP%3E%7C%26nbsp%3B%3CSPAN%3Ewhere%20ActionType%20%3D%3D%20%22FileCreated%22%3C%2FSPAN%3E%3CBR%20%2F%3E%7C%20where%20FolderPath%20matches%20regex%20%40%22(.*%5C%5CProgramData%5C%5C%5B%5E%5C%5C%5D%2B)%24%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3260835%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20String%20Search%20With%20Wildcards%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3260835%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20parse%20out%20the%20stuff%20between%20the%20C%3A%5CProgramData%5C%20and%20%5C%20to%20a%20new%20column%20and%20then%20search%20on%20it%3CBR%20%2F%3E%3CBR%20%2F%3EDeviceFileEvents%3CBR%20%2F%3E%7C%20parse%20FolderPath%20with%20*%20'C%3A%5C%5CProgramData%5C%5C'%20file%20'%5C%5C'%20*%3CBR%20%2F%3E%7C%20where%20file%20contains%20%22evil.exe%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternate%20way%2C%20search%20for%20startswith%20then%20split%20based%20on%20the%20%5C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDeviceFileEvents%3CBR%20%2F%3E%7C%20where%20FolderPath%20startswith%20%22C%3A%5C%5CProgramData%5C%5C%22%3CBR%20%2F%3E%7C%20extend%20paths%20%3D%20split(FolderPath%2C%22%5C%5C%22)%3CBR%20%2F%3E%7C%20extend%20file%20%3D%20paths.%5B2%5D%3C%2FP%3E%3CP%3E%7C%20where%20file%20contains%20%22evil.exe%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Is it possible to do KQL string searches with wildcards? 

 

For example, I'm hunting for files written to C:\ProgramData\ but I don't want to see files written to subfolders. I've done this in Splunk so I was surprised that the last line in my query below does not filter out anything.

 

Show this: C:\ProgramData\evil.exe

Filter this out: C:\ProgramData\MyApp\NotEvil.exe

 

Query:

DeviceFileEvents
| where ActionType == "FileCreated"
| where FolderPath contains "ProgramData"
| where FolderPath !contains "ProgramData\\*\\*"

 

2 Replies

You can parse out the stuff between the C:\ProgramData\ and \ to a new column and then search on it

DeviceFileEvents
| parse FolderPath with * 'C:\\ProgramData\\' file '\\' *
| where file contains "evil.exe"

 

Alternate way, search for startswith then split based on the \

 

DeviceFileEvents
| where FolderPath startswith "C:\\ProgramData\\"
| extend paths = split(FolderPath,"\\")
| extend file = paths.[2]

| where file contains "evil.exe"

best response confirmed by TheDilly (Occasional Contributor)
Solution

Could use regex as well.

DeviceFileEvents

where ActionType == "FileCreated"
| where FolderPath matches regex @"(.*\\ProgramData\\[^\\]+)$"