steampipe plugin install azure

Table: azure_storage_account - Query Azure Storage Accounts using SQL

Azure Storage Account is a service within Microsoft Azure that provides scalable and secure data storage. It offers services like Blob Storage, File Storage, Queue Storage, and Table Storage. Azure Storage Account supports both Standard and Premium storage account types, allowing users to store large amounts of unstructured and structured data.

Table Usage Guide

The azure_storage_account table provides insights into Storage Accounts within Microsoft Azure. As a Cloud Architect or DevOps engineer, explore account-specific details through this table, including the storage account type, creation date, access tier, and associated metadata. Utilize it to uncover information about storage accounts, such as their replication strategy, the network rules set, and the status of secure transfer.

Examples

Basic info

Explore the different tiers and locations of your Azure storage accounts. This can help you understand your storage distribution and make informed decisions about resource allocation.

select
name,
sku_name,
sku_tier,
primary_location,
secondary_location
from
azure_storage_account;
select
name,
sku_name,
sku_tier,
primary_location,
secondary_location
from
azure_storage_account;

List storage accounts with versioning disabled

Explore which Azure storage accounts have not enabled blob versioning. This is useful for identifying potential vulnerabilities in data backup and recovery systems.

select
name,
blob_versioning_enabled
from
azure_storage_account
where
not blob_versioning_enabled;
select
name,
blob_versioning_enabled
from
azure_storage_account
where
blob_versioning_enabled is not 1;

List storage accounts with blob soft delete disabled

Determine the areas in which storage accounts have the blob soft delete feature disabled. This is useful for identifying potential risk points where data might be permanently lost if accidentally deleted.

select
name,
blob_soft_delete_enabled,
blob_soft_delete_retention_days
from
azure_storage_account
where
not blob_soft_delete_enabled;
select
name,
blob_soft_delete_enabled,
blob_soft_delete_retention_days
from
azure_storage_account
where
not blob_soft_delete_enabled;

List storage accounts that allow blob public access

Determine the areas in which your Azure storage accounts are configured to allow public access to blobs. This can be used to identify potential security risks and ensure appropriate access controls are in place.

select
name,
allow_blob_public_access
from
azure_storage_account
where
allow_blob_public_access;
select
name,
allow_blob_public_access
from
azure_storage_account
where
allow_blob_public_access;

List storage accounts with encryption in transit disabled

Determine the areas in which data security may be compromised due to the lack of encryption during data transit in your Azure storage accounts. This query is useful to identify potential vulnerabilities and enhance your security measures.

select
name,
enable_https_traffic_only
from
azure_storage_account
where
not enable_https_traffic_only;
select
name,
enable_https_traffic_only
from
azure_storage_account
where
enable_https_traffic_only = 0;

List storage accounts that do not have a cannot-delete lock

Determine the areas in which storage accounts in Azure lack a 'cannot-delete' lock, which could potentially leave them vulnerable to unintentional deletion or modification. This query is useful for identifying and rectifying potential security risks within your storage management system.

select
sg.name,
ml.scope,
ml.lock_level,
ml.notes
from
azure_storage_account as sg
left join azure_management_lock as ml on lower(sg.id) = lower(ml.scope)
where
(
(ml.lock_level is null)
or(ml.lock_level = 'ReadOnly')
);
select
sg.name,
ml.scope,
ml.lock_level,
ml.notes
from
azure_storage_account as sg
left join azure_management_lock as ml on lower(sg.id) = lower(ml.scope)
where
(
(ml.lock_level is null)
or(ml.lock_level = 'ReadOnly')
);

List storage accounts with queue logging enabled

Discover the segments that have all types of queue logging enabled in their Azure storage accounts. This is useful to assess the storage accounts that are actively tracking and recording all queue activities for auditing or troubleshooting purposes.

select
name,
queue_logging_delete,
queue_logging_read,
queue_logging_write
from
azure_storage_account
where
queue_logging_delete
and queue_logging_read
and queue_logging_write;
select
name,
queue_logging_delete,
queue_logging_read,
queue_logging_write
from
azure_storage_account
where
queue_logging_delete = 1
and queue_logging_read = 1
and queue_logging_write = 1;

List storage accounts without lifecycle

Determine the storage accounts that lack a lifecycle management policy. This is useful for identifying potential risks or inefficiencies related to data retention and storage management.

select
name,
lifecycle_management_policy -> 'properties' -> 'policy' -> 'rules' as lifecycle_rules
from
azure_storage_account
where
lifecycle_management_policy -> 'properties' -> 'policy' -> 'rules' is null;
select
name,
json_extract(
lifecycle_management_policy,
'$.properties.policy.rules'
) as lifecycle_rules
from
azure_storage_account
where
json_extract(
lifecycle_management_policy,
'$.properties.policy.rules'
) is null;

List diagnostic settings details

Explore the diagnostic settings of your Azure storage accounts to gain insights into their configurations. This is beneficial to ensure optimal settings are in use for efficient data storage and management.

select
name,
jsonb_pretty(diagnostic_settings) as diagnostic_settings
from
azure_storage_account;
select
name,
diagnostic_settings
from
azure_storage_account;

List storage accounts with replication but unavailable secondary

Determine the areas in which Azure storage accounts have available primary status but unavailable secondary status, specifically within the 'Standard_GRS' and 'Standard_RAGRS' SKU categories. This is useful for identifying potential risk areas in your storage infrastructure where data replication might not be functioning as expected.

select
name,
status_of_primary,
status_of_secondary,
sku_name
from
azure_storage_account
where
status_of_primary = 'available'
and status_of_secondary != 'available'
and sku_name in ('Standard_GRS', 'Standard_RAGRS');
select
name,
status_of_primary,
status_of_secondary,
sku_name
from
azure_storage_account
where
status_of_primary = 'available'
and status_of_secondary != 'available'
and sku_name in ('Standard_GRS', 'Standard_RAGRS');

Get table properties of storage accounts

Explore the properties of your storage accounts to gain insights into their configuration. This can help you understand and manage your access and retention policies, as well as monitor their usage metrics.

select
name,
table_properties -> 'Cors' as table_logging_cors,
table_properties -> 'Logging' -> 'Read' as table_logging_read,
table_properties -> 'Logging' -> 'Write' as table_logging_write,
table_properties -> 'Logging' -> 'Delete' as table_logging_delete,
table_properties -> 'Logging' ->> 'Version' as table_logging_version,
table_properties -> 'Logging' -> 'RetentionPolicy' as table_logging_retention_policy,
table_properties -> 'HourMetrics' -> 'Enabled' as table_hour_metrics_enabled,
table_properties -> 'HourMetrics' -> 'IncludeAPIs' as table_hour_metrics_include_ap_is,
table_properties -> 'HourMetrics' ->> 'Version' as table_hour_metrics_version,
table_properties -> 'HourMetrics' -> 'RetentionPolicy' as table_hour_metrics_retention_policy,
table_properties -> 'MinuteMetrics' -> 'Enabled' as table_minute_metrics_enabled,
table_properties -> 'MinuteMetrics' -> 'IncludeAPIs' as table_minute_metrics_include_ap_is,
table_properties -> 'MinuteMetrics' ->> 'Version' as table_minute_metrics_version,
table_properties -> 'MinuteMetrics' -> 'RetentionPolicy' as table_minute_metrics_retention_policy
from
azure_storage_account;
select
name,
json_extract(table_properties, '$.Cors') as table_logging_cors,
json_extract(table_properties, '$.Logging.Read') as table_logging_read,
json_extract(table_properties, '$.Logging.Write') as table_logging_write,
json_extract(table_properties, '$.Logging.Delete') as table_logging_delete,
json_extract(table_properties, '$.Logging.Version') as table_logging_version,
json_extract(table_properties, '$.Logging.RetentionPolicy') as table_logging_retention_policy,
json_extract(table_properties, '$.HourMetrics.Enabled') as table_hour_metrics_enabled,
json_extract(table_properties, '$.HourMetrics.IncludeAPIs') as table_hour_metrics_include_ap_is,
json_extract(table_properties, '$.HourMetrics.Version') as table_hour_metrics_version,
json_extract(table_properties, '$.HourMetrics.RetentionPolicy') as table_hour_metrics_retention_policy,
json_extract(table_properties, '$.MinuteMetrics.Enabled') as table_minute_metrics_enabled,
json_extract(table_properties, '$.MinuteMetrics.IncludeAPIs') as table_minute_metrics_include_ap_is,
json_extract(table_properties, '$.MinuteMetrics.Version') as table_minute_metrics_version,
json_extract(
table_properties,
'$.MinuteMetrics.RetentionPolicy'
) as table_minute_metrics_retention_policy
from
azure_storage_account;

Control examples

Schema for azure_storage_account

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
access_keysjsonbThe list of access keys or Kerberos keys (if active directory enabled) for the specified storage account.
access_tiertextThe access tier used for billing.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
allow_blob_public_accessbooleanSpecifies whether allow or disallow public access to all blobs or containers in the storage account.
blob_change_feed_enabledbooleanSpecifies whether change feed event logging is enabled for the Blob service.
blob_container_soft_delete_enabledbooleanSpecifies whether DeleteRetentionPolicy is enabled.
blob_container_soft_delete_retention_daysbigintIndicates the number of days that the deleted item should be retained.
blob_restore_policy_daysbigintSpecifies how long the blob can be restored.
blob_restore_policy_enabledbooleanSpecifies whether blob restore is enabled.
blob_service_loggingjsonbSpecifies the blob service properties for logging access.
blob_soft_delete_enabledbooleanSpecifies whether DeleteRetentionPolicy is enabled.
blob_soft_delete_retention_daysbigintIndicates the number of days that the deleted item should be retained.
blob_versioning_enabledbooleanSpecifies whether versioning is enabled.
cloud_environmenttextThe Azure Cloud Environment.
creation_timetimestamp with time zoneCreation date and time of the storage account.
diagnostic_settingsjsonbA list of active diagnostic settings for the storage account.
enable_https_traffic_onlybooleanAllows https traffic only to storage service if sets to true.
encryption_key_sourcetextContains the encryption keySource (provider).
encryption_key_vault_properties_key_current_version_idtextThe object identifier of the current versioned Key Vault Key in use.
encryption_key_vault_properties_key_nametextThe name of KeyVault key.
encryption_key_vault_properties_key_vault_uritextThe Uri of KeyVault.
encryption_key_vault_properties_key_versiontextThe version of KeyVault key.
encryption_key_vault_properties_last_rotation_timetimestamp with time zoneTimestamp of last rotation of the Key Vault Key.
encryption_scopejsonbEncryption scope details for the storage account.
encryption_servicesjsonbA list of services which support encryption.
failover_in_progressbooleanSpecifies whether the failover is in progress.
file_soft_delete_enabledbooleanSpecifies whether DeleteRetentionPolicy is enabled.
file_soft_delete_retention_daysbigintIndicates the number of days that the deleted item should be retained.
idtextContains ID to identify a storage account uniquely.
is_hns_enabledbooleanSpecifies whether account HierarchicalNamespace is enabled.
kindtextThe kind of the resource.
lifecycle_management_policyjsonbThe managementpolicy associated with the specified storage account.
minimum_tls_versiontextContains the minimum TLS version to be permitted on requests to storage.
nametext=The friendly name that identifies the storage account.
network_ip_rulesjsonbA list of IP ACL rules.
network_rule_bypasstextSpecifies whether traffic is bypassed for Logging/Metrics/AzureServices.
network_rule_default_actiontextSpecifies the default action of allow or deny when no other rules match.
primary_blob_endpointtextContains the blob endpoint.
primary_dfs_endpointtextContains the dfs endpoint.
primary_file_endpointtextContains the file endpoint.
primary_locationtextContains the location of the primary data center for the storage account.
primary_queue_endpointtextContains the queue endpoint.
primary_table_endpointtextContains the table endpoint.
primary_web_endpointtextContains the web endpoint.
private_endpoint_connectionsjsonbA list of private endpoint connection associated with the specified storage account.
provisioning_statetextThe provisioning state of the storage account resource.
public_network_accesstextAllow or disallow public network access to Storage Account. Value is optional but if passed in, must be Enabled or Disabled.
queue_logging_deletebooleanSpecifies whether all delete requests should be logged.
queue_logging_readbooleanSpecifies whether all read requests should be logged.
queue_logging_retention_daysbigintIndicates the number of days that metrics or logging data should be retained.
queue_logging_retention_enabledbooleanSpecifies whether a retention policy is enabled for the storage service.
queue_logging_versiontextThe version of Storage Analytics to configure.
queue_logging_writebooleanSpecifies whether all write requests should be logged.
regiontextThe Azure region/location in which the resource is located.
require_infrastructure_encryptionbooleanSpecifies whether or not the service applies a secondary layer of encryption with platform managed keys for data at rest.
resource_grouptext=The resource group which holds this resource.
secondary_locationtextContains the location of the geo-replicated secondary for the storage account.
sku_nametextContains sku name of the storage account.
sku_tiertextContains sku tier of the storage account.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
status_of_primarytextThe status indicating whether the primary location of the storage account is available or unavailable. Possible values include: 'available', 'unavailable'.
status_of_secondarytextThe status indicating whether the secondary location of the storage account is available or unavailable. Only available if the SKU name is Standard_GRS or Standard_RAGRS. Possible values include: 'available', 'unavailable'.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which the resource is located.
table_logging_deletebooleanIndicates whether all delete requests should be logged.
table_logging_readbooleanIndicates whether all read requests should be logged.
table_logging_retention_policyjsonbThe retention policy.
table_logging_versiontextThe version of Analytics to configure.
table_logging_writebooleanIndicates whether all write requests should be logged.
table_propertiesjsonbAzure Analytics Logging settings of tables.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
typetextType of the resource.
virtual_network_rulesjsonbA list of virtual network rules.

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_storage_account