Skip to content

Programmatically Downloading Azure Resource Inventory and Tag Management

Introduction

Maintaining an up-to-date inventory of Azure resources and their associated tags is critical for governance, cost management, and compliance. This article provides a detailed, production-grade approach to programmatically fetching Azure resource metadata and synchronizing it with a SQL Server database using Python.

1. Authentication: Secure Access to Azure APIs

Before accessing Azure resources, authenticate using a secure method. The function below demonstrates using the Azure Identity SDK's ClientSecretCredential for authentication. This is a common approach for automation scenarios, but for higher security, certificate-based authentication is recommended (see the article Certificate Based Authorization for Azure AD.)

Deep Dive: get_azure_credential Function

The get_azure_credential function leverages the azure-identity Python SDK, which provides a unified way to authenticate to Azure services. Here, we use the ClientSecretCredential class, which is suitable for service principals (app registrations) with a client secret.

Python Example:

from azure.identity import ClientSecretCredential

def get_azure_credential(tenant_id, client_id, client_secret):
    """
    Returns a credential object for authenticating with Azure SDKs.
    Uses the azure-identity library's ClientSecretCredential.
    """
    return ClientSecretCredential(
        tenant_id=tenant_id,
        client_id=client_id,
        client_secret=client_secret
    )
  • azure-identity SDK: This is the official Microsoft library for Azure authentication in Python. It supports multiple credential types, including secrets, certificates, managed identity, and interactive login.
  • ClientSecretCredential: This class is used for service-to-service authentication using a client ID and secret. It is widely supported by Azure SDKs, including resource management, storage, and more.
  • When to use: Use this for automation where a client secret is securely stored (e.g., in Azure Key Vault or environment variables). For higher security, use CertificateCredential instead.

2. Fetching Azure Resource Inventory

Use the Azure SDK to enumerate all resources in a subscription. Extract key metadata such as resource ID, name, location, type, and tags.

Python Example:

def fetch_azure_resources(credential, subscription_id):
    client = ResourceManagementClient(credential, subscription_id)
    resource_list = []
    for item in client.resources.list():
        type_parts = str(item.type).split('/')
        type1, type2, type3, type4, type5 = (type_parts + [''] * 5)[:5]
        resource_group_list = str(item.id).split('/')
        resource_data = {
            "id": str(item.id).replace(f'/subscriptions/{subscription_id}/', ''),
            "location": item.location,
            "name": item.name,
            "tags": item.tags,
            "resourceGroup": resource_group_list[4] if len(resource_group_list) >= 4 else '',
            "type1": type1,
            "type2": type2,
            "type3": type3,
            "type4": type4,
            "type5": type5,
        }
        resource_list.append(resource_data)
    return resource_list
  • Resource Types: The code splits the resource type string to extract up to five type levels for flexible reporting.
  • Tags: Tags are included for governance and cost allocation.

3. Synchronizing with SQL Server: Fast Bulk Operations

Efficiently update the SQL Server inventory table by marking all resources as inactive, then bulk updating existing resources and inserting new ones. This ensures the database reflects the current Azure state.

Note: The Dim_Resources table is not a full load (truncate-and-reload) table. Instead, it is designed to retain records of resources that may have been deleted from Azure. By marking resources as inactive rather than removing them, you can track the lifecycle of resources, including those that have been deleted, for audit, compliance, and historical analysis purposes.

Python Example:

import pyodbc

def sync_resources_to_sql(resource_list, connection_string):
    conn = pyodbc.connect(connection_string)
    cursor = conn.cursor()
    existing_resource_ids = {str(row[0]).lower() for row in cursor.execute("SELECT ResourceID FROM Dim_Resources").fetchall()}
    updateresources = [
        [r['location'], r['name'], r['resourceGroup'], r['type1'], r['type2'], r['type3'], r['type4'], r['type5'], True, r['id']]
        for r in resource_list if str(r['id']).lower() in existing_resource_ids
    ]
    newresources = [
        [r['id'], r['location'], r['name'], r['resourceGroup'], r['type1'], r['type2'], r['type3'], r['type4'], r['type5'], True]
        for r in resource_list if str(r['id']).lower() not in existing_resource_ids
    ]
    cursor.execute('UPDATE Dim_Resources SET Active = 0')
    if updateresources:
        query = '''UPDATE Dim_Resources SET Location=?, Name=?, ResourceGroup=?, Type1=?, Type2=?, Type3=?, Type4=?, Type5=?, Active=? WHERE ResourceId=?'''
        cursor.fast_executemany = True
        cursor.executemany(query, updateresources)
    if newresources:
        query = '''INSERT INTO Dim_Resources (ResourceID, Location, Name, ResourceGroup, Type1, Type2, Type3, Type4, Type5, Active) VALUES (?,?,?,?,?,?,?,?,?,?)'''
        cursor.fast_executemany = True
        cursor.executemany(query, newresources)
    conn.commit()
    cursor.close()
    conn.close()
  • Bulk Operations: Use fast_executemany for high-performance updates and inserts.
  • Active Flag: Mark all resources as inactive before updating, then set active for current resources.

4. End-to-End Orchestration

A typical workflow for resource inventory management:

def fetch_and_store_resources():
    credential = get_azure_credential(tenant_id=..., client_id=..., client_secret=...)
    resource_list = fetch_azure_resources(credential, subscription_id=...)
    sync_resources_to_sql(resource_list, connection_string=...)

5. Best Practices and Considerations

  • Security: Use certificate-based authentication for automation when possible. Store credentials securely.
  • Performance: Use bulk operations for large datasets.
  • Data Quality: Regularly update the inventory to reflect the current Azure state.
  • Scheduling: Automate the process with a scheduler (e.g., cron, Azure Automation).
  • Auditing: Keep logs of changes and exceptions for compliance.

Conclusion

By following this approach, you can automate the discovery and inventory of Azure resources, ensuring your SQL Server database remains a reliable source of truth for governance and reporting.

References