Forum Discussion
Minemeld Threat Intel Integration to Sentinel
JoachimLassus You will need to add the parse_ipv4_mask and parse_ipv4 commands before doing the compare. Like CliveWatson I do not have the needed data to completely test but from my simple tests, this appears to work
ThreatIntelligenceIndicator
| where isnotempty(NetworkCidrBlock)
| project NetworkCidrBlock = "193.228.91.0/26"
| project justIP = tostring(split(NetworkCidrBlock,"/").[0]) , prefixIP = toint(split(NetworkCidrBlock,"/").[1])
| extend compareIP = parse_ipv4_mask(justIP,prefixIP)
| join (
SigninLogs
| project IPAddress = "193.228.91.0"
//use parse_ipv4 to convert IP to long
| extend compareIP = parse_ipv4(IPAddress)
)
on $left.compareIP== $right.compareIP
Gary, Minemeld gives lots of different NetworkCIDRBlocks records that are considered malicious. Some contains 1 host but many contains several hosts. For example 2 records could looks like this:
1. 94.102.56.232/30 which gives you the following range 94.102.56.232 to 94.102.56.235
2. 94.102.56.239/32 which gives you 94.102.56.239
So the query would need to check each IPAddress from Signinglogs and see if they exist within those ranges.
- Very pleased you got it working, and pleased the work Gary did and my tip to match on octets helped 😉
I think the following query gets the correct result, so far the testing hasnt shown anything incorrect anyway.
SigninLogs| extend sign_get_three_octet=extract("([0-9]*.[0-9]*.[0-9]*)", 1, IPAddress)//| project sign_get_three_octet , IPAddress| join kind=inner (ThreatIntelligenceIndicator| extend threat_get_three_octet=extract("([0-9]*.[0-9]*.[0-9]*)", 1, NetworkCIDRBlock)//| project threat_get_three_octet , NetworkCIDRBlock) on $left.sign_get_three_octet== $right.threat_get_three_octet| where ipv4_is_match(IPAddress,NetworkCIDRBlock)| project IPAddress, NetworkCIDRBlockA million thanks to CliveWatson and GaryBusheyJoachimLassus You are correct, I told you to use the wrong command. ipv4_compare is what you need to use (BTW, also found out that you can use CIDR notation in parse_ipv4 so parse_ipv4_mask is not needed.
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ipv4-comparefunction
Now we just need to figure out how to use the ip4_compare in a join. The best I could come up with, since joins only allows equality comparisons, is something like this. Hopefully someone better with KQL can come up with something a bit more elegent.
let X = datatable(Key:string, Value1:string) [ 'a',"193.228.91.1", ]; let Y = datatable(Key:string, Value2:string) [ 'a',"193.228.91.0/26", ]; let Z =X | join Y on Key; Z | where ipv4_compare( Value1, Value2)==0- If I use one of the previous examples and create a datatable like this:
let ThreatIntelligenceIndicator = datatable (timeGenerated:datetime,NetworkCidrBlock:string, NetworkIP:string) [ datetime("10/7/2020, 1:25:34.971 PM"),"193.228.91.0/26","193.228.91.0" ] ; ThreatIntelligenceIndicator | extend CompareIP2 = parse_ipv4(NetworkIP) | project justIP = tostring(split(NetworkCidrBlock,"/").[0]) , prefixIP = toint(split(NetworkCidrBlock,"/").[1]), CompareIP2 | extend compareIP = parse_ipv4_mask(justIP,prefixIP)The output for parse_ipv4(NetworkIP) is 3,252,968,192 and the output for parse_ipv4_mask(justIP,prefixIP) is also 3,252,968,192 .
If I change NetworkIP in the table to be 193.228.91.1 then the output for parse_ipv4(NetworkIP) is 3,252,968,193.
That would mean if we have 193.228.91.1 in the Signinlogs then the join from your query wouldn't work, right? JoachimLassus Unless I am missing something the query I posted in my last response should do just that.
