Collect Azure Resource List and Virtual Machines into SQLite

README.md

Intro

The problem: https://community.grafana.com/t/integration-with-azure-metrics-variables-not-getting-full-scope/43216 This is part of a litte workaround on using Azure Metrics with Grafana.

You currently simply cannot fected the resources_groups from the KSL on the variables scope. (please tell me if you can)

So This script uses an Azure Subscription ID and pull the data into a sqlite database. From there using the sqlite plugin you can query them.

Examples of variables

Listing the resource groups

SELECT DISTINCT(resource_group) FROM virtual_machines:

Listing the virtual machine withing the previous resource groups

SELECT name FROM virtual_machines WHERE resource_group=$resource_group;

main.py

#!/usr/bin/python

from azure.identity import ClientSecretCredential
from azure.mgmt.resource import ResourceManagementClient
from datetime import datetime
import os
import sqlite3
import time

""" Gets Azure resource groups and virtaul machines
 I assume the following variables exist
 AZURE_TENANT_ID: with your Azure Active Directory tenant id or domain
 AZURE_CLIENT_ID: with your Azure Active Directory Application Client ID
 AZURE_CLIENT_SECRET: with your Azure Active Directory Application Secret
 AZURE_SUBSCRIPTION_ID: with your Azure Subscription Id
 Optionally:
 DATA_DB_PATH: The path for the sqlite database to be in otherwise, if not
 set it  be data.db on the current directory
"""


db_path = "data.db"
if os.environ.get("DATA_DB_PATH"):
    db_path = os.environ.get("DATA_DB_PATH")
    if not os.path.exists(os.path.dirname(db_path)):
        print("Creating database basedir: {dir}".format(dir = os.path.dirname(db_path)))
        os.mkdir(os.path.dirname(db_path))


conn = sqlite3.connect(db_path)
conn.execute("""
    CREATE TABLE IF NOT EXISTS virtual_machines 
        (id INTEGER PRIMARY KEY AUTOINCREMENT, resource_group VARCHAR(50), name TEXT);""")
conn.execute("""CREATE UNIQUE INDEX IF NOT EXISTS idx_virtual_machines_name
        ON virtual_machines (name);""")


def fetch_data():

    subscription_id = os.environ.get("AZURE_SUBSCRIPTION_ID")
    client_id = os.environ.get("AZURE_CLIENT_ID")
    client_secret = os.environ.get("AZURE_CLIENT_SECRET")
    tenant_id = os.environ.get("AZURE_TENANT_ID")

    credentials = ClientSecretCredential(
        client_id = client_id,
        client_secret = client_secret,
        tenant_id = tenant_id
    )

    client = ResourceManagementClient(credentials, subscription_id)

    for res_group in client.resource_groups.list():
        print("Listing resources for {n}".format(n = res_group.name))
        for res in client.resources.list_by_resource_group(res_group.name):
            if res.type == "Microsoft.Compute/virtualMachines":
                conn.execute("INSERT OR REPLACE INTO virtual_machines(resource_group, name) VALUES(?,?)", [res_group.name, res.name])

    conn.commit()

interrupt = False
while not interrupt:
    try:
        fetch_data()
        if not interrupt:
            print("Resting for 10 minutes")
            time.sleep(600)
    except(KeyboardInterrupt, SystemExit):
        print("Received Keyboard Interrupt or system existing, exiting")
        interrupt=True

conn.close()

requirements.txt

azure-mgmt-portal
azure-mgmt-resource
azure-identity
msrestazure