turbot/azure

steampipe plugin install azuresteampipe plugin install azure
azure_ad_groupazure_ad_service_principalazure_ad_userazure_api_managementazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_application_security_groupazure_compute_availability_setazure_compute_diskazure_compute_disk_encryption_setazure_compute_disk_metric_read_opsazure_compute_disk_metric_read_ops_dailyazure_compute_disk_metric_read_ops_hourlyazure_compute_disk_metric_write_opsazure_compute_disk_metric_write_ops_dailyazure_compute_disk_metric_write_ops_hourlyazure_compute_imageazure_compute_resource_skuazure_compute_snapshotazure_compute_virtual_machineazure_compute_virtual_machine_metric_cpu_utilizationazure_compute_virtual_machine_metric_cpu_utilization_dailyazure_compute_virtual_machine_metric_cpu_utilization_hourlyazure_cosmosdb_accountazure_cosmosdb_mongo_databaseazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_diagnostic_settingazure_express_route_circuitazure_firewallazure_key_vaultazure_key_vault_keyazure_key_vault_secretazure_kubernetes_clusterazure_locationazure_log_alertazure_log_profileazure_management_lockazure_mysql_serverazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_serverazure_providerazure_public_ipazure_resource_groupazure_role_assignmentazure_role_definitionazure_route_tableazure_security_center_auto_provisioningazure_security_center_contactazure_security_center_settingazure_security_center_subscription_pricingazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_tableazure_storage_table_serviceazure_subnetazure_subscriptionazure_tenantazure_virtual_networkazure_virtual_network_gateway

Table: azure_compute_virtual_machine

Azure Virtual Machines (VM) is one of several types of on-demand, scalable computing resources that Azure offers.

Examples

Virtual machine configuration info

select
name,
power_state,
private_ips,
public_ips,
vm_id,
size,
os_type,
image_offer,
image_sku
from
azure_compute_virtual_machine;

Virtual machine count in each region

select
region,
count(name)
from
azure_compute_virtual_machine
group by
region;

List of VMs whose OS disk is not encrypted by customer managed key

select
vm.name,
disk.encryption_type
from
azure_compute_disk as disk
join azure_compute_virtual_machine as vm on disk.name = vm.os_disk_name
where
not disk.encryption_type = 'EncryptionAtRestWithCustomerKey';

List of VMs provisioned with undesired(for example Standard_D8s_v3 and Standard_DS3_v3 is desired) sizes.

select
size,
count(*) as count
from
azure_compute_virtual_machine
where
size not in ('Standard_D8s_v3', 'Standard_DS3_v3')
group by
size;

Availability set info of VMs

select
vm.name vm_name,
aset.name availability_set_name,
aset.platform_fault_domain_count,
aset.platform_update_domain_count,
aset.sku_name
from
azure_compute_availability_set as aset
join azure_compute_virtual_machine as vm on lower(aset.id) = lower(vm.availability_set_id);

List of all spot type VM and their eviction policy

select
name,
vm_id,
eviction_policy
from
azure_compute_virtual_machine
where
priority = 'Spot';

Disk Storage Summary, by VM

select
vm.name,
count(d) as num_disks,
sum(d.disk_size_gb) as total_disk_size_gb
from
azure.azure_compute_virtual_machine as vm
left join azure_compute_disk as d on lower(vm.id) = lower(d.managed_by)
group by
vm.name
order by
vm.name;

View Network Security Group Rules for a VM

select
vm.name,
nsg.name,
jsonb_pretty(security_rules)
from
azure.azure_compute_virtual_machine as vm,
jsonb_array_elements(vm.network_interfaces) as vm_nic,
azure_network_security_group as nsg,
jsonb_array_elements(nsg.network_interfaces) as nsg_int
where
lower(vm_nic ->> 'id') = lower(nsg_int ->> 'id')
and vm.name = 'warehouse-01';

.inspect azure_compute_virtual_machine

Azure Compute Virtual Machine

NameTypeDescription
admin_user_nametextSpecifies the name of the administrator account
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
allow_extension_operationsbooleanSpecifies whether extension operations should be allowed on the virtual machine
availability_set_idtextSpecifies the ID of the availability set
billing_profile_max_pricedouble precisionSpecifies the maximum price you are willing to pay for a Azure Spot VM/VMSS
boot_diagnostics_enabledbooleanSpecifies whether boot diagnostics should be enabled on the Virtual Machine, or not
boot_diagnostics_storage_uritextContains the Uri of the storage account to use for placing the console output and screenshot
computer_nametextSpecifies the host OS name of the virtual machine
data_disksjsonbA list of parameters that are used to add a data disk to a virtual machine
disable_password_authenticationbooleanSpecifies whether password authentication should be disabled
eviction_policytextSpecifies the eviction policy for the Azure Spot virtual machine and Azure Spot scale set
extensionsjsonbSpecifies the details of VM Extensions.
idtextThe unique id identifying the resource in subscription
image_exact_versiontextSpecifies in decimal numbers, the version of platform image or marketplace image used to create the virtual machine
image_idtextSpecifies the ID of the image to use
image_offertextSpecifies the offer of the platform image or marketplace image used to create the virtual machine
image_publishertextSpecifies the publisher of the image to use
image_skutextSpecifies the sku of the image to use
image_versiontextSpecifies the version of the platform image or marketplace image used to create the virtual machine
linux_configuration_ssh_public_keysjsonbA list of ssh key configuration for a Linux OS
managed_disk_idtextSpecifies the ID of the managed disk used by the virtual machine
nametextThe friendly name that identifies the virtual machine
network_interfacesjsonbA list of resource Ids for the network interfaces associated with the virtual machine
os_disk_cachingtextSpecifies the caching requirements of the operating system disk used by the virtual machine
os_disk_create_optiontextSpecifies how the virtual machine should be created
os_disk_nametextSpecifies the name of the operating system disk used by the virtual machine
os_disk_vhd_uritextSpecifies the virtual hard disk's uri.
os_typetextSpecifies the type of the OS that is included in the disk if creating a VM from user-image or a specialized VHD
power_statetextSpecifies the power state of the vm
prioritytextSpecifies the priority for the virtual machine
private_ipsjsonbAn array of private ip addesses associated with the vm.
provision_vm_agentbooleanSpecifies whether virtual machine agent should be provisioned on the virtual machine
provisioning_statetextThe virtual machine provisioning state
public_ipsjsonbAn array of public ip addesses associated with the vm.
regiontextThe Azure region/location in which the resource is located.
require_guest_provision_signalbooleanSpecifies whether the guest provision signal is required to infer provision success of the virtual machine
resource_grouptextThe resource group which holds this resource.
secretsjsonbA list of certificates that should be installed onto the virtual machine
sizetextSpecifies the size of the virtual machine
statusesjsonbSpecifies the resource status information
subscription_idtextThe Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
typetextThe type of the resource in Azure
ultra_ssd_enabledbooleanSpecifies whether managed disks with storage account type UltraSSD_LRS can be added to a virtual machine or virtual machine scale set, or not
vm_idtextSpecifies an unique ID for VM, which is a 128-bits identifier that is encoded and stored in all Azure IaaS VMs SMBIOS and can be read using platform BIOS commands
zonesjsonbA list of virtual machine zones