KQL Query to retrieve Azure Subscription Name, Resource Group, Resource Name, Resource Type, Tags and Location

0
votes

I would like to retrieve all the resource names along with their types belonging to a particular subscription and resource group, along with the tags of the resources. I should be able to dump them in a CSV file where the first column would be subscription, then resource group followed by resource name, type and tags. I should be able to filter the CSV as to what i need to see. I need to run this for all my subscriptions in a particular tenant so that i get this information for all subscriptions in my tenant. Can anyone please help me writing a KQL query for this so that i can run from the portal. Thanks

3
azurepowershellautomationazure-resource-managerkql
kql query to do that? it's not a good idea. you'd better using powershell scripts to do that. – Ivan Yang

3 Answers

1
votes

Using KQL in Azure Resource Graph is actually an ideal way to retrieve this information. You can run the KQL queries from the Azure Portal using Resource Graph Explorer then export (or use PowerShell with the Search-AzGraph cmdlet and pipe to Export-Csv).

Resource Graph allows queries to the ARM graph backend using KQL, which is an extremely powerful and preferred method to access Azure configuration data. All subscriptions in the tenant are in scope by default (if checked off).

Please review Resource Graph concepts and query samples in Microsoft's docs:

Query below; if you choose to export all subscriptions and RGs at once just remove the subscriptionId and resourceGroup where clauses:

resources | where subscriptionId == "subscription-id-here" | where resourceGroup == "rg-name-here" | project subscriptionId, resourceGroup, name, type, tags
0
votes

Yes, @Ivan is right. KQL is certainly not meant for this purpose. Kusto query language, or KQL is the primary means of interaction with Azure Data Explorer and work with log data on Azure.

The simplest way to get this information about your Azure resources is from the Azure Portal itself, by viewing and filtering Azure resource information.

As your query spans across your Subscriptions, you could also run queries from Azure Resource Graph.

Azure PowerShell and Azure CLI would be other great ways to get detailed information about your Azure resources. Here is another post with a similar ask.

0
votes

had a similar challenge with KQL to provide a user friendly names for Subscriptions in Azure Workbooks. I found a solution on link

The trick is to list the subscriptionnames from the table resourcecontainers and then join the results with your resources query

The answer to your question will look like this:

resources
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| project subscriptionName, resourceGroup, name, type, tags