The in operator in Kusto Query Language (KQL) is a useful tool for filtering data based on a specific set of values. It’s similar to the SQL IN operator and is helpful when you want to check if a value appears in a list. This allows you to simplify queries by matching multiple values in a single condition.

---

Concept of in

• The in operator is a filter that checks if a value in a specified column exists in a list of values.
• If the value in the column matches any value in the provided list, the row is returned.
• The in operator is very helpful when you want to quickly filter a column for several specific values without writing multiple conditions.

---

Syntax

The basic syntax of the in operator in KQL is:

<table> | where <column> in (<value1>, <value2>, <value3>, ...)

• <table>: The name of the table you are querying.
• <column>: The column to filter by.
• <value1>, <value2>, ...: The list of values to check against.

---

Usage and Examples

Here are some common use cases and examples that demonstrate how to use the in operator effectively.

Example 1: Filtering by Multiple Values

Suppose you have a table called SystemLogs with a column called EventType, and you want to retrieve rows where the EventType is either "Error", "Warning", or "Information".

SystemLogs 
| where EventType in ("Error", "Warning", "Information")

• Explanation: This query returns rows where EventType is "Error", "Warning", or "Information". If the EventType in a row matches any of these values, that row is included in the results.

Example 2: Filtering with Numeric Values

Imagine you have a table called SalesData with a column ProductID, and you want to see only the rows where the ProductID is 101, 102, or 103.

SalesData
| where ProductID in (101, 102, 103)

• Explanation: This query retrieves rows where the ProductID is either 101, 102, or 103. It’s a quick way to filter for multiple values without having to write separate where conditions.

Example 3: Using in with Dates

Let’s say you have a table called Orders with a column OrderDate, and you want to filter for specific dates only.

Orders
| where OrderDate in (datetime(2023-01-01), datetime(2023-02-15), datetime(2023-03-10))

• Explanation: This query retrieves orders that were placed on the dates specified in the list. The datetime function is used to specify exact dates, as KQL recognizes dates as a distinct type.

Example 4: Combining in with Other Filters

You can also combine the in operator with other where conditions to make your filter more specific. For example, if you want to retrieve error events from the SystemLogs table with specific EventIDs:

SystemLogs
| where EventType == "Error" and EventID in (5001, 5002, 5003)

• Explanation: This query retrieves rows where EventType is "Error" and EventID is 5001, 5002, or 5003. Combining conditions in this way allows for precise filtering.

Example 5: Using in with a Subquery

In KQL, you can use the in operator with a subquery to filter a table based on results from another query. For instance, if you have a Users table and a LoginAttempts table, you could retrieve rows where UserID matches users who have had failed login attempts.

Users
| where UserID in (LoginAttempts | where Status == "Failed" | project UserID)

• Explanation: This query first filters the LoginAttempts table for rows where Status is "Failed" and then extracts the UserID values. It then retrieves rows from the Users table where the UserID matches any of these values.

---

• The in operator allows you to filter data based on a list of values, making queries shorter and more readable.
• It works with text, numbers, dates, and even subqueries.
• Common use cases include filtering for specific event types, product IDs, or matching data between tables.

The in operator is a versatile tool in KQL, simplifying complex filtering tasks and allowing you to query your data more effectively.