Forum Discussion
Cannot use union * for Defender Hunting query to Create Detection Rule, so what other workarounds?
I tried to create custom detection rule from KQL query in Defender XDR: Advance Hunting by custom various variable to be able to submit, but for this query to be able to go through remediation setting of detection rule, I need the entity identifiable columns like AccountUpn, that I need to union with IdentityInfo schema.
But detection rule seems not support the union * thing as the attached pic:
I searched for the same problems that seems to be occurred in all system using KQL including in Microsoft Sentinel Logs but has no workaround to bypass. So, is there any way to get through this objective without strucking with union * problem?
I'm sure this is because Sentinel excludes union * in the Logs blade within Sentinel (it will work outside Sentinel in similar looking logs blades, like in Log Analytics). It was excluded for performance reasons for Detections, as you could be looking through 10's, 100s or more tables and the results may not come back in enough time for the next alert trigger. More relevant for NRT or rules that trigger every 5mins.
Can you union by named Table (e.g. union IdentityInfo) or Join or lookup?
The screen shot you provided doesn't show the union * just a join.
4 Replies
I'm sure this is because Sentinel excludes union * in the Logs blade within Sentinel (it will work outside Sentinel in similar looking logs blades, like in Log Analytics). It was excluded for performance reasons for Detections, as you could be looking through 10's, 100s or more tables and the results may not come back in enough time for the next alert trigger. More relevant for NRT or rules that trigger every 5mins.
Can you union by named Table (e.g. union IdentityInfo) or Join or lookup?
The screen shot you provided doesn't show the union * just a join.I just noticed that join is not union as you said. And surprisingly, even normal union or union * now seems to be able to create detection rule, unlink what people said about what it is in Sentinel Logs. Thank you so much! But I swear in the afternoon with this same query, there was error as shown in the pic even many times I tried. ><
This query works when I simply ran it in advanced hunting (slightly altered for my environment), when I tried to make a detection rule the system complained that it needs a recordID value in the query results to make a rule. So I can't explain how you got this error, presumably the query runs fine on your side as well.
OMG, I try creating detection rule again with "exactly" the same query and this time it is work! So, how come lol. Anyway, thank you so much!
