Forum Discussion

kudumum's avatar
kudumum
Copper Contributor
Nov 14, 2024

KQL- in/has-any usage

For the below query, when I use "contains" for single app its works fine but have bulk AppIDs to check, how can i use "in' here?

 

query fails when I replace contains with in or has-any. please help. thank you.

 

let AppIDList = dynamic(["APPID01", "APPID02", "APPID03"]); 

resources

| where type !in~ ("microsoft.compute/snapshots", "microsoft.compute/virtualmachines/extensions")

| project subscriptionId, type, resourceGroup, name,AppID = tostring(['tags']['AppID'])                         //Here AppID is comma sepeated list os AppIDs

| where AppID in (AppIDList)

| join kind=inner (

    resourcecontainers

    | where ['type'] == "microsoft.resources/subscriptions"

    | project subscriptionId, name, subname = name

) on $left.subscriptionId == $right.subscriptionId

| project subname, subscriptionId, type, resourceGroup, name

KQL

2 Replies

  • kyazaferr's avatar
    kyazaferr
    MCT
    Nov 15, 2024

    o check if AppID (a comma-separated list) matches any value in AppIDList using Kusto Query Language (KQL), you should use the has_any operator instead of in. The in operator works when comparing a single value against a set, but for lists of values (like tags), has_any checks for intersections between arrays or strings.

    Here's how you can modify your query:

    let AppIDList = dynamic(["APPID01", "APPID02", "APPID03"]);

    resources
    | where type !in~ ("microsoft.compute/snapshots", "microsoft.compute/virtualmachines/extensions")
    | project subscriptionId, type, resourceGroup, name, AppID = tostring(['tags']['AppID'])
    | where AppID has_any (AppIDList)    // Use has_any for matching against a list of AppIDs
    | join kind=inner (
        resourcecontainers
        | where ['type'] == "microsoft.resources/subscriptions"
        | project subscriptionId, name, subname = name
    ) on $left.subscriptionId == $right.subscriptionId
    | project subname, subscriptionId, type, resourceGroup, name

    • kyazaferr's avatar
      kyazaferr
      MCT
      Nov 15, 2024
      • has_any: This operator checks if any of the values in AppIDList exist in the AppID field (which can be a string containing comma-separated values).
      • tostring(): Ensures that AppID is treated as a string for the comparison.

      This modification should allow the query to check if any value in the AppIDList appears in the AppID field.

Resources