Search code examples
kqlazure-resource-graph

KQL for ARG - Checking if any element of array satisfies a predicate

Suppose I have the following input as some table cell value:

Addrs
[{"ip": "1.1.1.1"},{"ip": "2.2.2.2"},{"ip": "3.3.3.3"},{"ip": "4.4.4.4"}]

Is it possible to filter the rows by checking if any of the array values satisfies a predicate?

Currently I am able to filer only on the first value.

| where parse_ipv4(tostring(addrs[0].ip)) > parse_ipv4("1.1.1.1")

However I wasn't able to apply this for all the values of the array.

Tried to use mvexpand (that's the only option in kql for arg) in the following manner:

| extend addrs = properties.addrs
| mvexpand bagexpansion = array addrs

However this seems to drop all the array values except for the first one.

Solution

  • Azure Resource Graph doesn't support KQL Language Elements such as "let" which makes creating custom data difficult, one way around this would be to extend a column of an existing table

    If you want to do an exact comparison, then a join would be sufficient enough

    resources
| take 1
| project  ComparisonIPs = pack_array("1.1.1.1","2.2.2.2","3.3.3.3","4.4.4.4")
| mv-expand ComparisonIPs to typeof(string)
| join kind = innerunique (
    // Your
    resources
    | where type == "microsoft.network/publicipaddresses"
    | project ipAddress = tostring(parse_json(properties).ipAddress), id
) on $left.ComparisonIPs == $right.ipAddress

    Alternatively to compare on a predicate, create a generic column to join the two data sets, perform an inner join and then do the comparison on the resultant set

    resources
| take 1
| project  ComparisonIPs = pack_array("1.1.1.1","2.2.2.2","3.3.3.3","4.4.4.4")
| mv-expand ComparisonIPs to typeof(string)
| extend Parsed = parse_ipv4(ComparisonIPs)
| extend JoinColumn = "1"
| join kind = inner (
resources
    | where type == "microsoft.network/publicipaddresses"
    | project ipAddress = tostring(parse_json(properties).ipAddress)
    | extend Parsed = parse_ipv4(ipAddress)
    | extend JoinColumn = "1"
) on JoinColumn
| where Parsed > Parsed1

    There are other options, depending on what you are trying to achieve, such as passing the data as parameters in Azure Monitor Workbooks and reusing the ARG results in Log Analytics, possibly referencing externaldata() or a watchlist in Azure Sentinel.