Search code examples
kqlazure-monitoringazure-monitor-workbooks

Combine Resource graph query with Log queries in Azure Monitor Workbook

My goal is to map the sourceIp from Azure Firewall Logs to it's corresponding Virtual Network.

To obtain the necessary information I have a Azure Resource Graph Query like this:

// Is also filtered based on parameters for subscriptions
resources
| where type == "microsoft.network/virtualnetworks"
| extend AddressSpace_AddressPrefixes = tostring(properties.['addressSpace'].['addressPrefixes'])  
| project id, AddressSpace_AddressPrefixes

Then I want to use this information in a Logs query and get the rows with sourceIps in any of the ranges from the above query.

AZFWDnsQuery
| where SourceIp in "the result from query1"

As a test using Resource Graph query in Azure Monitor (from the example of John Gardner below) it's not working to use the mv-expand operator.

arg("").resources
| where type == "microsoft.network/virtualnetworks"
| project AddressSpace_AddressPrefixes = properties.['addressSpace'].['addressPrefixes']
| mv-expand AddressSpace_AddressPrefixes
| summarize list=tostring(makeset(tostring(AddressSpace_AddressPrefixes)))

Using mv-expand operator

Even hardcoding in an array doesn't work to expand

// Not working example
arg("").resources
| where type == "microsoft.network/virtualnetworks"
| extend addressPrefixes = dynamic(['10.200.19.0/26', '10.200.26.0/26'])
| project addressPrefixes
| mv-expand addressPrefixes

enter image description here

The problem is that

  1. Resource graph Queries and Log queries can't be done in the same query.
  2. Using Data Source Merge doesn't work since it can only join on "==" and I need to match on "in".

Is there a solution for this?

Solution

  • except... assumption #1 is no longer actually true! (in preview form at least)

    1. answer with preview querying ARG from logs (see limitations!)

    https://learn.microsoft.com/en-us/azure/azure-monitor/logs/azure-monitor-data-explorer-proxy#query-data-in-azure-resource-graph-preview

    there is a new arg("") operator that lets you query azure resource graph from logs queries:

    arg("").Resources
| where type == "microsoft.compute/virtualmachines" and properties.hardwareProfile.vmSize startswith "Standard_D"
| join (
    Heartbeat
    | where TimeGenerated > ago(1d)
    | distinct Computer
    )
    on $left.name == $right.Computer

    copying the limitations from the docs for now, since it is in preview and the content/link changing/becoming broken are high. All of ARG's limitations still apply, like resource limits and row limits!

    Limitations

    General cross-service query limitations

    • Database names are case sensitive.
    • Identifying the Timestamp column in the cluster isn't supported. The Log Analytics Query API won't pass the time filter.
    • Cross-service queries support data retrieval only.
    • Private Link (private endpoints) and IP restrictions do not support cross-service queries.
    • mv-expand is limited to 2000 records.

    Azure Resource Graph cross-service query limitations

    When you query Azure Resource Graph data from Azure Monitor:

    • The query returns the first 1000 records only.
    • Azure Monitor doesn't return Azure Resource Graph query errors.
    • The Log Analytics query editor marks valid Azure Resource Graph queries as syntax errors.
    • These operators aren't supported: smv-apply(), rand(), arg_max(), arg_min(), avg(), avg_if(), countif(), sumif(), percentile(), percentiles(), percentilew(), percentilesw(), stdev(), stdevif(), stdevp(), variance(), variancep(), varianceif().

    2. Workbooks specific way of doing this with no preview

    if any of the limits above don't work for you, or you can't use preview things, there are other possibilities, like having the ARG query be a text parameter from a query and have it return a big string comma separated list of values, and then use that in the Logs query?

    create a text parameter with arg query like:

    // Is also filtered based on parameters for subscriptions
resources
| where type == "microsoft.network/virtualnetworks"
| project AddressSpace_AddressPrefixes = properties.['addressSpace'].['addressPrefixes']
| mv-expand AddressSpace_AddressPrefixes
| summarize list=tostring(makeset(tostring(AddressSpace_AddressPrefixes)))

    you'd have a parameter that is a string value of those items as an array, like this (you can mark the parameter hidden in reading mode):

    parameter and logs query

    and you can then use it in a logs query like this:

    let prefixes = dynamic({prefixes});
AZFWDnsQuery
| where SourceIp in (prefixes)