Using the Union Operator in KQL

The union operator is a super handy organizational tool in the Kusto Query Language (KQL). It makes it possible to combine data from multiple tables to show the results in one space. Essentially it allows you to avoid running the same query multiple times if only a few parameters changed. In this example, we’re going to use a query with a union operator to display incident closures with the owners, and the amount closed within a certain period of time.

The union operator can work in conjunction with the let statement. So first we are going to assign “let” to a user we know is closing incidents, and then proceed with the normal query.

Here is the command written out:

let User = view() {

SecurityIncident

| where Status == 'Closed'

| where TimeGenerated > ago(7d)

| where (Owner contains "user")

| summarize count() by tostring(Owner)

};

When viewing a SecurityIncident in KQL, there are a lot of Owner parameters, so I’ve found it easiest to use a “contains” function to sort this information. This query by itself will give us a table of the user and count of closed incidents within the last 7 days. But we’re here to union! So we need to create an identical query with a different user directly underneath it.

Now we have two let variables, User1, and User2. Now let’s union them together! This part is easy. We are simply going to use the “union” operator underneath our previous query using a “withsource” argument to pull from our previous let parameters we set. We are also going to assign a name to the column.

union withsource="ClosedByHumans" User1, User2

Now, let’s put this all together and see what our output is.

Check that out! Now we can see the count of closures based on multiple users using the “union” operator. Remember, you can add as many users as you want, you could make a “No one assigned” parameter, and you could even sort your count. KQL is a powerful language, and this is just one of the many commands that could make your querying simpler and more efficient.

Learn more about the union operator on this Microsoft Learn page.