steampipe plugin install azuresteampipe plugin install azure
azure_ad_groupazure_ad_service_principalazure_ad_userazure_alert_managementazure_api_managementazure_api_management_backendazure_app_configurationazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_app_service_web_app_slotazure_application_gatewayazure_application_insightazure_application_security_groupazure_automation_accountazure_automation_variableazure_bastion_hostazure_batch_accountazure_cognitive_accountazure_compute_availability_setazure_compute_diskazure_compute_disk_accessazure_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_ssh_keyazure_compute_virtual_machineazure_compute_virtual_machine_metric_cpu_utilizationazure_compute_virtual_machine_metric_cpu_utilization_dailyazure_compute_virtual_machine_metric_cpu_utilization_hourlyazure_compute_virtual_machine_scale_setazure_compute_virtual_machine_scale_set_network_interfaceazure_compute_virtual_machine_scale_set_vmazure_consumption_usageazure_container_groupazure_container_registryazure_cosmosdb_accountazure_cosmosdb_mongo_collectionazure_cosmosdb_mongo_databaseazure_cosmosdb_restorable_database_accountazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_data_lake_analytics_accountazure_data_lake_storeazure_databox_edge_deviceazure_databricks_workspaceazure_diagnostic_settingazure_dns_zoneazure_eventgrid_domainazure_eventgrid_topicazure_eventhub_namespaceazure_express_route_circuitazure_firewallazure_firewall_policyazure_frontdoorazure_hdinsight_clusterazure_healthcare_serviceazure_hpc_cacheazure_hybrid_compute_machineazure_hybrid_kubernetes_connected_clusterazure_iothubazure_iothub_dpsazure_key_vaultazure_key_vault_deleted_vaultazure_key_vault_keyazure_key_vault_key_versionazure_key_vault_managed_hardware_security_moduleazure_key_vault_secretazure_kubernetes_clusterazure_kubernetes_service_versionazure_kusto_clusterazure_lbazure_lb_backend_address_poolazure_lb_nat_ruleazure_lb_outbound_ruleazure_lb_probeazure_lb_ruleazure_locationazure_log_alertazure_log_profileazure_logic_app_workflowazure_machine_learning_workspaceazure_management_groupazure_management_lockazure_mariadb_serverazure_monitor_activity_log_eventazure_monitor_log_profileazure_mssql_elasticpoolazure_mssql_managed_instanceazure_mssql_virtual_machineazure_mysql_flexible_serverazure_mysql_serverazure_nat_gatewayazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_flexible_serverazure_postgresql_serverazure_private_dns_zoneazure_providerazure_public_ipazure_recovery_services_backup_jobazure_recovery_services_vaultazure_redis_cacheazure_resource_groupazure_resource_linkazure_role_assignmentazure_role_definitionazure_route_tableazure_search_serviceazure_security_center_auto_provisioningazure_security_center_automationazure_security_center_contactazure_security_center_jit_network_access_policyazure_security_center_settingazure_security_center_sub_assessmentazure_security_center_subscription_pricingazure_service_fabric_clusterazure_servicebus_namespaceazure_signalr_serviceazure_spring_cloud_serviceazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_share_fileazure_storage_syncazure_storage_tableazure_storage_table_serviceazure_stream_analytics_jobazure_subnetazure_subscriptionazure_synapse_workspaceazure_tenantazure_virtual_networkazure_virtual_network_gateway

Table: azure_compute_virtual_machine - Query Azure Compute Virtual Machines using SQL

Azure Compute is a service within Microsoft Azure that allows you to run applications on virtual machines in the cloud. It provides scalable, on-demand compute capacity in the cloud and lets you create and manage virtual machines to run applications. Azure Compute supports a range of operating systems, languages, tools, and frameworks.

Table Usage Guide

The azure_compute_virtual_machine table provides insights into the virtual machines within Azure Compute. As a system administrator, you can explore detailed information about each virtual machine, including its configuration, status, and operational aspects. Utilize this table to manage and monitor your virtual machines effectively, ensuring optimal performance and resource usage.

Examples

Virtual machine configuration info

Explore the configuration of your virtual machines to gain insights into their power state, IP addresses, size, operating system, and image details. This can help in managing resources and ensuring optimal performance of your virtual machines.

select
name,
power_state,
private_ips,
public_ips,
vm_id,
size,
os_type,
image_offer,
image_sku
from
azure_compute_virtual_machine;
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

Analyze the distribution of virtual machines across different regions. This information can be useful for understanding your infrastructure's geographical spread and planning resource allocation.

select
region,
count(name)
from
azure_compute_virtual_machine
group by
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

Determine the areas in which virtual machines are potentially vulnerable due to their operating system disk not being encrypted by a customer-managed key. This query is useful in identifying security risks and enhancing data protection measures.

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';
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
disk.encryption_type != 'EncryptionAtRestWithCustomerKey';

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

Determine the areas in which virtual machines have been provisioned with non-standard sizes. This is useful for identifying potential inefficiencies or areas for optimization in your Azure Compute resources.

select
size,
count(*) as count
from
azure_compute_virtual_machine
where
size not in ('Standard_D8s_v3', 'Standard_DS3_v3')
group by
size;
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

Explore the relationship between virtual machines and their respective availability sets in Azure, including fault domain count, update domain count and SKU name. This can be beneficial for understanding the resilience and update strategy of your virtual machines.

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);
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

Explore which virtual machines are of the spot type and understand their eviction policies. This can be useful in managing costs and resource allocation in an Azure environment.

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

Disk Storage Summary, by VM

This query is useful to gain insights into the disk storage usage across all virtual machines in an Azure environment. It helps in managing and optimizing storage resources by providing a summary of the number and total size of disks used by each virtual machine.

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;
select
vm.name,
count(d.disk_size_gb) as num_disks,
sum(d.disk_size_gb) as total_disk_size_gb
from
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

Explore the security rules applied to a specific virtual machine within your network. This can be useful for auditing security configurations and identifying potential vulnerabilities.

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';
select
vm.name,
nsg.name,
security_rules
from
azure_compute_virtual_machine as vm,
json_each(vm.network_interfaces) as vm_nic,
azure_network_security_group as nsg,
json_each(nsg.network_interfaces) as nsg_int
where
lower(json_extract(vm_nic.value, '$.id')) = lower(json_extract(nsg_int.value, '$.id'))
and vm.name = 'warehouse-01';

List virtual machines with user assigned identities

This example helps you identify the virtual machines in your Azure environment that are configured with user-assigned identities. This is useful for understanding your identity management practices, specifically in scenarios where you want to delegate permissions to resources in your Azure environment.

select
name,
identity -> 'type' as identity_type,
jsonb_pretty(identity -> 'userAssignedIdentities') as identity_user_assignedidentities
from
azure_compute_virtual_machine
where
exists (
select
from
unnest(regexp_split_to_array(identity ->> 'type', ',')) elem
where
trim(elem) = 'UserAssigned'
);
select
name,
json_extract(identity, '$.type') as identity_type,
identity_user_assignedidentities
from
azure_compute_virtual_machine
where
instr(identity_type, 'UserAssigned') > 0;

List security profile details

Determine the areas in which encryption is being used at host level within Azure's virtual machines. This can be useful for assessing security measures and identifying potential vulnerabilities.

select
name,
vm_id,
security_profile -> 'encryptionAtHost' as encryption_at_host
from
azure_compute_virtual_machine;
select
name,
vm_id,
json_extract(security_profile, '$.encryptionAtHost') as encryption_at_host
from
azure_compute_virtual_machine;

Query examples

Control examples

Schema for azure_compute_virtual_machine

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
additional_unattend_contentjsonbSpecifies additional base-64 encoded XML formatted information that can be included in the Unattend.xml file, which is used by windows setup.
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.
cloud_environmenttextThe Azure Cloud Environment.
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.
enable_automatic_updatesbooleanIndicates whether automatic updates is enabled for the windows virtual machine.
eviction_policytextSpecifies the eviction policy for the Azure Spot virtual machine and Azure Spot scale set.
extensionsjsonbSpecifies the details of VM Extensions.
guest_configuration_assignmentsjsonbGuest configuration assignments for a virtual machine.
idtextThe unique id identifying the resource in subscription.
identityjsonbThe identity of the virtual machine, if configured.
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.
nametext=The 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_nametextThe Operating System running on the virtual machine.
os_typetextSpecifies the type of the OS that is included in the disk if creating a VM from user-image or a specialized VHD.
os_versiontextThe version of Operating System running on the virtual machine.
patch_settingsjsonbSpecifies settings related to in-guest patching (KBs).
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 for linux configuration.
provision_vm_agent_windowsbooleanSpecifies whether virtual machine agent should be provisioned on the virtual machine for windows configuration.
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_grouptext=The resource group which holds this resource.
secretsjsonbA list of certificates that should be installed onto the virtual machine.
security_profilejsonbSpecifies the security related profile settings for 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.
time_zonetextSpecifies the time zone of the virtual machine.
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.
win_rmjsonbSpecifies the windows remote management listeners. This enables remote windows powershell.
zonesjsonbA list of virtual machine zones.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_compute_virtual_machine