Cannot use join KQL for Analytics Rules

[jeffrywu28]

Describe the bug
why i cannot use detection rules that correlated with ThreatIntelIndicator join table ?

To Reproduce
Steps to reproduce the behavior:

1. Go to Analytics Rules
2. Create analytics rules
3. use the query :

_Im_NetworkSession
| where ipv4_is_private(SrcIpAddr) == False
| where SrcIpAddr !in ("127.0.0.1", "::1")
| summarize
AttemptedPortsCount = dcount(DstPortNumber),
AttemptedPorts = make_set(DstPortNumber),
ReportedBy = make_set(strcat(EventVendor, "/", EventProduct)),
LastTimeGenerated = max(TimeGenerated)
by SrcIpAddr
| where AttemptedPortsCount > PortScanThreshold
| join kind=inner (
ThreatIntelligenceIndicator
| summarize ThreatTypes = make_set(ThreatType) by NetworkSourceIP // Aggregate ThreatTypes for each NetworkSourceIP
)
on $left.SrcIpAddr == $right.NetworkSourceIP
| project
SrcIpAddr,
LastTimeGenerated,
AttemptedPortsCount,
AttemptedPorts,
ReportedBy,
ThreatTypes

4. See error

Expected behavior
It's running and showing the test result clearly

Screenshots

[github-actions]

Thank you for submitting an Issue to the Azure Sentinel GitHub repo! You should expect an initial response to your Issue from the team within 5 business days. Note that this response may be delayed during holiday periods. For urgent, production-affecting issues please raise a support ticket via the Azure Portal.

[v-rbajaj]

Hi @jeffrywu28, thanks for flagging this issue, we will get back to you by 30 Nov 2023.

[v-sudkharat]

Hi @jeffrywu28, just want to know, have you declaired the "let PortScanThreshold = 50;" in the query?
We have run your provided query in our workspace we are got below result. -

If we declare the "let PortScanThreshold = 50;" in query and re-run it, then query executed successfully and start getting the result -

FYI. we have defined the "let PortScanThreshold = 50;" as 50 at the beginning of the query. This means that the query will only consider source IP addresses (SrcIpAddr) that have attempted to connect to more than 50 different ports (AttemptedPortsCount > PortScanThreshold).

Could you please check with "let PortScanThreshold = 50;" and let us know if your issue still persists.
Thanks!

[v-sudkharat]

Hi @jeffrywu28, we are waiting for your response on above comment. Thanks!

[v-sudkharat]

Hi @jeffrywu28, Gentle Reminder: We are waiting for your response on this issue. If you still need to keep this issue active, please respond to it in the next 2 days. If we don't receive a response by 08-12-2023 date, we will be closing this issue.
Thanks!

[v-sudkharat]

Hi @jeffrywu28, since we have not received a response in the last 5 days, we are closing your issue- #9484 as per our standard operating procedures. If you still need support for this issue, feel free to re-open at any time. Thank you for your co-operation.