Skip to content

Instantly share code, notes, and snippets.

@alfredopalhares
Last active March 1, 2021 16:47
Show Gist options
  • Save alfredopalhares/c6b9391cb20da48f90f40295551aeed1 to your computer and use it in GitHub Desktop.
Save alfredopalhares/c6b9391cb20da48f90f40295551aeed1 to your computer and use it in GitHub Desktop.
Collect Azure Resource List and Virtual Machines into SQLite

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;
#!/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()
azure-mgmt-portal
azure-mgmt-resource
azure-identity
msrestazure
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment