Search code examples
azure-data-explorerkqlazure-sentinel

Summarize 2 sets into 1 set per user KQL

What would be the proper way to summarize 2 sets into 1 set by user?

For example, in the picture below:

enter image description here

I want to create a new set (the column that has the question mark) combining the X_locations and Y_Locations columns by User.

I did try strcat_array, but I am not sure those results will work, is anyone aware of a proper way to do this?, I envision something like this?:

| summarize whateverSetUnionFunctionHere(X_Locations,Y_Locations) by User
Solution

  • It seems you are looking for the combination of @Avnera & @Yoni K. answers

    datatable(User:string, X_locations:dynamic, Y_locations:dynamic)
[
    "user1", dynamic(["a"]), dynamic(["a"]),
    "user2", dynamic(["b","c"]), dynamic(["c"]),
    "user2", dynamic(["b"]), dynamic(["b","d"]),
]
| summarize make_set(set_union(X_locations, Y_locations)) by User
    User set_
    user1 ["a"]
    user2 ["b","c","d"]

    Fiddle

    P.S.
    There are multiple variations for that, E.g. set_union could be replaced by array_concat