SOLVED

KQL Query for Match IoC from WatchList

%3CLINGO-SUB%20id%3D%22lingo-sub-3213168%22%20slang%3D%22en-US%22%3EKQL%20Query%20for%20Match%20IoC%20from%20WatchList%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3213168%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3Ecan%20you%20help%20me%20to%20make%20a%20query%20to%20match%20IoC%20that%20i%20imported%20from%20a%20csv%20file%20in%20to%20a%20a%20watchlist%3F%3C%2FP%3E%3CP%3EMy%20query%20at%20the%20moment%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20Ioc%20%3D%20_GetWatchlist('ioc')%3B%3CBR%20%2F%3EAzureActivity%3CBR%20%2F%3E%7C%20where%20CallerIpAddress%20!%3D%20''%3CBR%20%2F%3E%7C%20extend%20WhoDidIt%20%3D%20Caller%2C%20ResourceName%20%3D%20tostring(parse_json(Properties).resource)%3CBR%20%2F%3E%7C%20join%20Ioc%20on%20%24left.CallerIpAddress%20%3D%3D%20%24right.SearchKey%3CBR%20%2F%3E%7C%20project%20TimeGenerated%2C%20SearchKey%2C%20OperationNameValue%2C%20Type%2C%20SubscriptionId%2C%20WhoDidIt%2C%20ResourceName%2C%20ResourceGroup%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebut%20my%20ioc%20list%20contains%20hash%2C%20domains%2C%20url%20and%20i%20wanto%20to%20integrate%20in%20my%20threat%20hunting%20query.%3C%2FP%3E%3CP%3EMy%20ioc%20list%20has%202%20columns%20ioc_type%20and%20ioc_value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20all%2C%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3213168%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDetection%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESIEM%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EThreat%20Hunting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EThreat%20Intelligence%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3215363%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Query%20for%20Match%20IoC%20from%20WatchList%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3215363%22%20slang%3D%22en-US%22%3EHave%20a%20look%20at%20this%20example%20here%20-%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FDetections%2FMultipleDataSources%2FZincJan272021IOCs.yaml%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FDetections%2FMultipleDataSources%2FZincJan272021IOCs.yaml%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20has%20a%20few%20different%20types%20of%20IOCs%2C%20in%20this%20example%20they%20are%20just%20a%20list%20which%20is%20cast%20as%20a%20variable%20but%20with%20your%20example%20you%20can%20use%20your%20watchlist%20as%20the%20source%2C%20i.e%3CBR%20%2F%3E%3CBR%20%2F%3Elet%20domains%3D%20_GetWatchlist('ioc')%20%7C%20where%20ioc_type%20%3D%3D%20%22domains%22%20%7C%20project%20ioc_type%3B%3CBR%20%2F%3Elet%20hashes%3D%20_GetWatchlist('ioc')%20%7C%20where%20ioc_type%20%3D%3D%20%22hashes%22%20%7C%20project%20ioc_type%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20search%20in%20your%20relevant%20data%20for%20the%20information%20using%20unions%20like%20in%20that%20example%20above%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hi all,

can you help me to make a query to match IoC that i imported from a csv file in to a a watchlist?

My query at the moment is:

 

let Ioc = _GetWatchlist('ioc');
AzureActivity
| where CallerIpAddress != ''
| extend WhoDidIt = Caller, ResourceName = tostring(parse_json(Properties).resource)
| join Ioc on $left.CallerIpAddress == $right.SearchKey
| project TimeGenerated, SearchKey, OperationNameValue, Type, SubscriptionId, WhoDidIt, ResourceName, ResourceGroup

 

but my ioc list contains hash, domains, url and i wanto to integrate in my threat hunting query.

My ioc list has 2 columns ioc_type and ioc_value.

 

Thanks all,

Regards

1 Reply
best response
Solution
Have a look at this example here - https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/ZincJan272021IOCs...

This has a few different types of IOCs, in this example they are just a list which is cast as a variable but with your example you can use your watchlist as the source, i.e

let domains= _GetWatchlist('ioc') | where ioc_type == "domains" | project ioc_type;
let hashes= _GetWatchlist('ioc') | where ioc_type == "hashes" | project ioc_type;

Then search in your relevant data for the information using unions like in that example above