Table: azure_resource - Query Azure Resources using SQL

Azure resources are entities managed by Azure, such as virtual machines, storage accounts, and network interfaces. This table allows you to query various details about these resources, including their properties, identities, tags, and more.

Table Usage Guide

The azure_resource table provides insights into resources within your Azure environment. As an Azure administrator, you can explore resource-specific details through this table, including resource IDs, names, types, provisioning states, and associated tags. Utilize it to manage and audit resources, verify configurations, and enforce governance policies.

Important notes:

For improved performance, it is advised that you use the optional qual to limit the result set. Optional quals are supported for the following columns:
- region
- type
- name
- identity_principal_id
- plan_publisher
- plan_name
- plan_product
- plan_promotion_code
- plan_version
- resource_group
- filter

Examples

Basic Information

Retrieve basic information about all Azure resources, including their names, types, and regions.

select
  name,
  type,
  region
from
  azure_resource;

select
  name,
  type,
  region
from
  azure_resource;

Filter by resource type

Get a list of all virtual networks.

select
  name,
  type,
  region
from
  azure_resource
where
  filter = 'resourceType eq ''Microsoft.Network/virtualNetworks''';

select
  name,
  type,
  region
from
  azure_resource
where
  filter = 'resourceType eq ''Microsoft.Network/virtualNetworks''';

Retrieve resources with specific tag key

Fetch resources that have specific tags associated with them.

select
  name,
  type,
  tags
from
  azure_resource
where
  filter = 'startswith(tagName, ''cost'')';

select
  name,
  type,
  tags
from
  azure_resource
where
  filter = 'startswith(tagName, ''cost'')';

Get Resource identity details

List resources along with their identity principal IDs and types.

select
  name,
  type,
  identity_principal_id,
  identity ->> 'type' as identity_type,
  identify -> 'userAssignedIdentities' as user_assigned_identities
from
  azure_resource;

select
  name,
  type,
  identity_principal_id,
  json_extract(identity, '$.type') as identity_type,
  json_extract(identity, '$.userAssignedIdentities') as user_assigned_identities
from
  azure_resource;

List managed resources

Identify resources that are managed by other resources.

select
  name,
  type,
  managed_by
from
  azure_resource
where
  managed_by is not null;

select
  name,
  type,
  managed_by
from
  azure_resource
where
  managed_by is not null;

Schema for azure_resource

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
changed_time	timestamp with time zone		The changed time of the resource.
cloud_environment	text		The Azure Cloud Environment.
created_time	timestamp with time zone		The created time of the resource.
extended_location	jsonb		Resource extended location.
filter	text	=	The filter to apply on the operation.
id	text	=	Resource ID.
identity	jsonb		The identity of the resource.
identity_principal_id	text	=, !=	The principal ID of resource identity.
kind	text		The kind of the resource.
managed_by	text		ID of the resource that manages this resource.
name	text	=, !=	Resource name.
plan_name	text	=, !=	The plan ID.
plan_product	text	=, !=	The plan offer ID.
plan_promotion_code	text	=, !=	The plan promotion code.
plan_publisher	text	=, !=	The plan publisher ID.
plan_version	text	=, !=	The plan's version.
properties	jsonb		The resource properties.
provisioning_state	text		The provisioning state of the resource.
region	text	=, !=	The Azure region/location in which the resource is located.
resource_group	text	=, !=	The resource group which holds this resource.
sku	jsonb		The SKU of the resource.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
subscription_id	text	=, !=, ~~, ~~, !~~, !~~	The Azure Subscription ID in which the resource is located.
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.
type	text	=, !=	Resource type.

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_resource