Mehmet Ergene

Querying Azure Resource Graph Without Limits Using KQL

One of the cool features of Microsoft Sentinel and Defender XDR is that they allow us to query Azure resources using KQL. However, this capability has limitations which makes it unusable in large environments. In this post, I’ll explain how to query Azure resources using KQL by bypassing the limits.

Scenario

You want to list all primary endpoints of all storage accounts in your Azure environment so that you can whitelist them when running your hunting query.

Querying Azure Resources Using KQL

You can query storage accounts using the KQL query below:
 
arg("").Resources
| where type has "storageAccounts"

Azure Resource Graph returns maximum 1000 results when querying:
Empty space, drag to resize
If we just count the number of storage accounts, we see there are more than 1000 storage accounts in the environment.
Empty space, drag to resize
So, how can we get all the data?

First Attempt: make_list and mv-expand

What if we summarize all the results into a list using make_list() and then expand all of them using mv-expand? The make_list() results in a single row containing all the storage accounts, and mv-expand should then return all the storage accounts, right?
 The primary endpoints information is stored under properties.primaryEndpoints and contains the URLs for blob, table, queue, etc. services
Empty space, drag to resize
Summarizing into a list works. How about mv-expand?
Unfortunately, we hit the mv-expand limit of Azure Resource Graph. We get this error because the query runs on Azure Resource Graph. Normally, we don't have any limitations on the mv-expand operator on Microsoft Sentinel or Defender XDR. 

So, how can we achieve our goal?

Enter Cross-cluster Join

What is cross-cluster join? From the docs:

A cross-cluster join involves joining data from datasets that reside in different clusters.
In a cross-cluster join, the query can be executed in three possible locations, each with a specific designation for reference:
  • Local cluster: The cluster to which the request is sent, which is also known as the cluster hosting the database in context.
  • Left cluster: The cluster hosting the data on the left side of the join operation(hint.remote=left).
  • Right cluster: The cluster hosting the data on the right side of the join operation(hint.remote=right).

The cluster that runs the query fetches the data from the other cluster.
Empty space, drag to resize
Since both Microsoft Sentinel and Defender XDR run on Kusto clusters, we should be able to fetch the data onto them and perform the operation. To do that, we need to specify a join hint and say "perform the operation on Sentinel/Defender". 

But first, we need some data on Sentinel to join Azure Resource Graph data. How about creating a dummy table and performing a fullouter join on a dummy key?
Empty space, drag to resize
That works! We are able to list all storage accounts with this simple trick! There is still a small issue that we get all primary endpoints in a JSON format, which is easy to solve with an array kind mv-expand:
Empty space, drag to resize
We can now perform the whitelisting, or whatever we want in our query.

Conclusion

Although Azure Resource Graph has limitations, we can bypass them by fetching the data onto our Kusto cluster, be it ADX, Sentinel, Defender, and perform operations. This, of course, doesn't apply only to the storage accounts; we can do the same for any resource in Azure!

Here is the whole query:
let dummy_table = datatable (primary_endpoints:dynamic, dummy_num:long) [
    dynamic([]), 1
]
;
let strg_acc = materialize(
    arg("").Resources
    | where type has "storageAccounts"
    | project primary_endpoints = properties.primaryEndpoints    
    | summarize primary_endpoints = make_list(primary_endpoints)
    | extend dummy_num=long(1)
)
;
dummy_table
| join hint.remote=left kind=fullouter  strg_acc on dummy_num
| project primary_endpoints = primary_endpoints1
| mv-expand primary_endpoints
| mv-expand kind=array primary_endpoints
| project endpoint = tostring(primary_endpoints[1])
Share this post

Latest from our blog