Automating Azure Resource Graph Queries with Logic Apps

Overview

Azure Resource Graph Explorer enables querying resources at scale across subscriptions, management groups, and entire tenants. If you need to execute queries periodically and take action on the results, Azure Logic Apps provides an automated solution.

This article provides step-by-step instructions on how to:

• Write an Azure Resource Graph query to run periodically.
• Create an Azure Logic App with a System-Assigned Managed Identity.
• Set up a Managed Identity with appropriate access.
• Automate the execution of your Azure Resource Graph query via Logic Apps.
• Store query results in CSV format in Azure Blob Storage.

Prerequisites

• An Azure subscription (Sign up for a free account if you don’t have one.)
• An Azure Storage Account with a Blob Container.

1. Write an Azure Resource Graph Query

To retrieve the power state summary of a test Virtual Machine, use the following Kusto Query Language (KQL) query:

Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend vmPowerState = tostring(properties.extended.instanceView.powerState.code)
| summarize count() by vmPowerState

2. Create an Azure Logic App

1. Navigate to the Azure Portal.
2. Search for Logic Apps in the top search bar and select it.
3. Click Add to create a new Logic App.
4. Under Plan Type, select Consumption.
5. Complete the required configurations and deploy the Logic App.

3. Set Up a Managed Identity

Enable System-Assigned Managed Identity

1. Navigate to the Logic App you created.
2. On the left menu, select Identity.
3. Choose the System-Assigned tab, set the status to On, and click Save.

Assign Role Permissions

To grant the Managed Identity the ability to query across subscriptions, resource groups, and resources:

1. Navigate to Azure Role Assignments.
2. Assign the Azure Resource Graph Reader role to the Logic App's Managed Identity.
3. Refer to Assign Azure Roles to a Managed Identity for more details.

4. Configure and Run Your Logic App

1. Open your Logic App and switch to Code View.
2. Paste the required JSON configuration.
3. If validation fails, switch to Designer View and adjust the setup.
4. Click Save and run your Logic App.

5. Store Query Results in Azure Blob Storage (CSV Format)

Convert JSON Response to CSV Format

1. Add a Data Operations - Select action to extract necessary fields.
2. Use a Data Operations - Compose action to format the data into CSV.

Example CSV Formatting Expression:

concat('Power State,VM Count\n', join(body('Select'), '\n'))

Upload to Azure Blob Storage

1. Add the Azure Blob Storage - Create Blob action.
2. Configure it to store the CSV output in a designated Blob Container.

Next Steps

• Monitor and review execution results in Logic Apps Run History.
• Expand automation to include alerts, notifications, or remediation actions.

By following these steps, you can automate periodic queries of Azure resources and store the results efficiently in Azure Blob Storage in CSV format.