steampipe plugin install azure

Table: azure_cosmosdb_account - Query Azure Cosmos DB Accounts using SQL

Azure Cosmos DB is a globally distributed, multi-model database service designed for scalable and high-performance modern applications. It provides native support for NoSQL and OSS APIs, including MongoDB, Cassandra, Gremlin, et al. With turnkey global distribution and transparent multi-master replication, it offers single-digit millisecond latency, and 99.999% availability.

Table Usage Guide

The azure_cosmosdb_account table provides insights into Azure Cosmos DB Accounts within Azure's database services. As a database administrator or developer, explore account-specific details through this table, including configurations, properties, and associated resources. Utilize it to uncover information about accounts, such as their replication policies, failover policies, and the verification of virtual network rules.

Examples

List of database accounts where automatic failover is not enabled

Explore which database accounts in Azure CosmosDB do not have automatic failover enabled. This is useful to identify potential risks and ensure high availability and disaster recovery in your database setup.

select
name,
region,
enable_automatic_failover,
resource_group
from
azure_cosmosdb_account
where
not enable_automatic_failover;
select
name,
region,
enable_automatic_failover,
resource_group
from
azure_cosmosdb_account
where
enable_automatic_failover = 0;

List of database accounts which allows traffic from all networks, including the public Internet.

Explore which database accounts are potentially exposed to security risks by allowing traffic from all networks, including the public internet. This can be useful to identify potential vulnerabilities and improve security measures.

select
name,
region,
virtual_network_rules
from
azure_cosmosdb_account
where
virtual_network_rules = '[]';
select
name,
region,
virtual_network_rules
from
azure_cosmosdb_account
where
virtual_network_rules = '[]';

List of database accounts where multiple write location is not enabled

Identify the Azure Cosmos DB accounts that do not have multiple write locations enabled. This can be useful in pinpointing potential areas of risk or inefficiency, as enabling multiple write locations can enhance data redundancy and availability.

select
name,
region,
enable_multiple_write_locations
from
azure_cosmosdb_account
where
not enable_multiple_write_locations;
select
name,
region,
enable_multiple_write_locations
from
azure_cosmosdb_account
where
enable_multiple_write_locations = 0;

Failover policy info for the database accounts

Determine the areas in which your Azure CosmosDB accounts have their failover policies set. This helps in understanding the priority and location of failover events, thereby assisting in ensuring high availability and disaster recovery strategies.

select
name,
fp ->> 'failoverPriority' as failover_priority,
fp ->> 'locationName' as location_name
from
azure_cosmosdb_account
cross join jsonb_array_elements(failover_policies) as fp;
select
name,
json_extract(fp.value, '$.failoverPriority') as failover_priority,
json_extract(fp.value, '$.locationName') as location_name
from
azure_cosmosdb_account,
json_each(failover_policies) as fp;

Consistency policy info for each account

Discover the segments that detail the consistency policy for each account, useful for understanding the database account offer type and the default consistency level. This aids in managing data consistency and staleness across your Azure Cosmos DB accounts.

select
name,
consistency_policy_max_interval,
consistency_policy_max_staleness_prefix,
database_account_offer_type,
default_consistency_level
from
azure_cosmosdb_account;
select
name,
consistency_policy_max_interval,
consistency_policy_max_staleness_prefix,
database_account_offer_type,
default_consistency_level
from
azure_cosmosdb_account;

Get backup policy for accounts having periodic backups enabled

Determine the backup policies of accounts with periodic backups enabled. This is useful for understanding the frequency and retention of backups, as well as the redundancy of storage, ensuring data safety and availability.

select
name,
region,
backup_policy -> 'periodicModeProperties' ->> 'backupIntervalInMinutes' as backup_interval_mins,
backup_policy -> 'periodicModeProperties' ->> 'backupRetentionIntervalInHours' as backup_retention_interval_hrs,
backup_policy -> 'periodicModeProperties' ->> 'backupStorageRedundancy' as backup_storage_redundancy
from
azure_cosmosdb_account
where
backup_policy ->> 'type' = 'Periodic';
select
name,
region,
json_extract(
backup_policy,
'$.periodicModeProperties.backupIntervalInMinutes'
) as backup_interval_mins,
json_extract(
backup_policy,
'$.periodicModeProperties.backupRetentionIntervalInHours'
) as backup_retention_interval_hrs,
json_extract(
backup_policy,
'$.periodicModeProperties.backupStorageRedundancy'
) as backup_storage_redundancy
from
azure_cosmosdb_account
where
json_extract(backup_policy, '$.type') = 'Periodic';

Get private endpoint connection details for each account

Explore the connection details of each private endpoint linked to your account. This can help you assess the status and type of each connection, enabling better management and troubleshooting of your network resources.

select
c ->> 'PrivateEndpointConnectionName' as private_endpoint_connection_name,
c ->> 'PrivateEndpointConnectionType' as private_endpoint_connection_type,
c ->> 'PrivateEndpointId' as private_endpoint_id,
c ->> 'PrivateLinkServiceConnectionStateActionsRequired' as private_link_service_connection_state_actions_required,
c ->> 'PrivateLinkServiceConnectionStateDescription' as private_link_service_connection_state_description,
c ->> 'PrivateLinkServiceConnectionStateStatus' as private_link_service_connection_state_status,
c ->> 'ProvisioningState' as provisioning_state,
c ->> 'PrivateEndpointConnectionId' as private_endpoint_connection_id
from
azure_cosmosdb_account,
jsonb_array_elements(private_endpoint_connections) as c;
select
json_extract(c.value, '$.PrivateEndpointConnectionName') as private_endpoint_connection_name,
json_extract(c.value, '$.PrivateEndpointConnectionType') as private_endpoint_connection_type,
json_extract(c.value, '$.PrivateEndpointId') as private_endpoint_id,
json_extract(
c.value,
'$.PrivateLinkServiceConnectionStateActionsRequired'
) as private_link_service_connection_state_actions_required,
json_extract(
c.value,
'$.PrivateLinkServiceConnectionStateDescription'
) as private_link_service_connection_state_description,
json_extract(
c.value,
'$.PrivateLinkServiceConnectionStateStatus'
) as private_link_service_connection_state_status,
json_extract(c.value, '$.ProvisioningState') as provisioning_state,
json_extract(c.value, '$.PrivateEndpointConnectionId') as private_endpoint_connection_id
from
azure_cosmosdb_account,
json_each(private_endpoint_connections) as c;

Get details of accounts restored from backup

The example demonstrates how to identify the instances where Azure Cosmos DB accounts have been restored from a backup. This can be particularly useful for auditing purposes, to ensure data integrity and to track any unauthorized restorations.

select
name,
restore_parameters ->> 'restoreMode' as restore_mode,
restore_parameters ->> 'restoreSource' as restore_source,
d ->> 'databaseName' as restored_database_name,
c as restored_collection_name
from
azure_cosmosdb_account,
jsonb_array_elements(restore_parameters -> 'databasesToRestore') d,
jsonb_array_elements_text(d -> 'collectionNames') c;
select
a.name,
json_extract(a.restore_parameters, '$.restoreMode') as restore_mode,
json_extract(a.restore_parameters, '$.restoreSource') as restore_source,
json_extract(d.value, '$.databaseName') as restored_database_name,
json_extract(c.value, '$') as restored_collection_name
from
azure_cosmosdb_account a,
json_each(
json_extract(a.restore_parameters, '$.databasesToRestore')
) as d,
json_each(json_extract(d.value, '$.collectionNames')) as c;

Schema for azure_cosmosdb_account

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
backup_policyjsonbThe object representing the policy for taking backups on an account.
capabilitiesjsonbA list of Cosmos DB capabilities for the account.
cloud_environmenttextThe Azure Cloud Environment.
connector_offertextThe cassandra connector offer type for the Cosmos DB database C* account.
consistency_policy_max_intervalbigintThe time amount of staleness (in seconds) tolerated, when used with the Bounded Staleness consistency level.
consistency_policy_max_staleness_prefixbigintThe number of stale requests tolerated, when used with the Bounded Staleness consistency level.
corsjsonbA list of CORS policy for the Cosmos DB database account.
database_account_offer_typetextThe offer type for the Cosmos DB database account.
default_consistency_leveltextThe default consistency level and configuration settings of the Cosmos DB account.
disable_key_based_metadata_write_accessbooleanDisable write operations on metadata resources (databases, containers, throughput) via account keys.
disable_local_authbooleanDisable local authentication and ensure only MSI and AAD can be used exclusively for authentication. Defaults to false.
document_endpointtextThe connection endpoint for the Cosmos DB database account.
enable_analytical_storagebooleanSpecifies whether to enable storage analytics, or not.
enable_automatic_failoverbooleanEnables automatic failover of the write region in the rare event that the region is unavailable due to an outage.
enable_cassandra_connectorbooleanEnables the cassandra connector on the Cosmos DB C* account.
enable_free_tierbooleanSpecifies whether free Tier is enabled for Cosmos DB database account, or not.
enable_multiple_write_locationsbooleanEnables the account to write in multiple locations.
failover_policiesjsonbA list of regions ordered by their failover priorities.
idtextContains ID to identify a database account uniquely.
ip_rulesjsonbA list of IP rules.
is_virtual_network_filter_enabledbooleanSpecifies whether to enable/disable Virtual Network ACL rules.
key_vault_key_uritextThe URI of the key vault, used to encrypt the Cosmos DB database account.
kindtextIndicates the type of database account.
locationsjsonbA list of all locations that are enabled for the Cosmos DB account.
nametext=The friendly name that identifies the database account.
private_endpoint_connectionsjsonbA list of Private Endpoint Connections configured for the Cosmos DB account.
provisioning_statetextThe provisioning state of the database account resource.
public_network_accesstextIndicates whether requests from Public Network are allowed.
read_locationsjsonbA list of read locations enabled for the Cosmos DB account.
regiontextThe Azure region/location in which the resource is located.
resource_grouptext=The resource group which holds this resource.
restore_parametersjsonbParameters to indicate the information about the restore.
server_versiontextDescribes the ServerVersion of an a MongoDB account.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
typetextType of the resource.
virtual_network_rulesjsonbA list of Virtual Network ACL rules configured for the Cosmos DB account.
write_locationsjsonbA list of write locations enabled for the Cosmos DB account.

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_cosmosdb_account