vms.sql

/* get instances by resource groups */
SELECT
name,
location,
SPLIT_PART(id, '/', 3) as resource_group,
JSON_EXTRACT(properties, '$.hardwareProfile.vmSize') as vm_size,
JSON_EXTRACT(properties, '$.storageProfile.osDisk.osType') as os_type,
JSON_EXTRACT(properties, '$.timeCreated') as time_created
FROM azure.compute.virtual_machines 
WHERE resourceGroupName IN  ('stackql-ops-cicd-dev-01', 'stackqlenv1', 'stackqlenv2', 'stackqlenv3', 'stackqlenv4')
AND subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

/* get instances by locations */
SELECT
name,
location,
SPLIT_PART(id, '/', 3) as resource_group,
JSON_EXTRACT(properties, '$.hardwareProfile.vmSize') as vm_size,
JSON_EXTRACT(properties, '$.storageProfile.osDisk.osType') as os_type,
JSON_EXTRACT(properties, '$.timeCreated') as time_created
FROM azure.compute.virtual_machines 
WHERE location IN  ('southcentralus', 'australiaeast')
AND subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468';

/*
both of the above return something like...
|------|----------------|--------------------------------------|-----------------|---------|-----------------------------------|                                                          
| name |    location    |            resource_group            |     vm_size     | os_type |           time_created            |                                                          
|------|----------------|--------------------------------------|-----------------|---------|-----------------------------------|                                                          
| vm0  | southcentralus | 631d1c6d-2a65-43e7-93c2-688bfe4e1468 | Standard_B1ls   | Linux   | 2022-09-14T04:52:27.3936003+00:00 |                                                          
|------|----------------|--------------------------------------|-----------------|---------|-----------------------------------|                                                          
| vm1  | southcentralus | 631d1c6d-2a65-43e7-93c2-688bfe4e1468 | Standard_B1ls   | Linux   | 2022-09-14T04:52:26.1123486+00:00 |                                                          
|------|----------------|--------------------------------------|-----------------|---------|-----------------------------------|  
...
*/