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_sql_database - Query Azure SQL Databases using SQL

Azure SQL Database is a fully managed platform as a service (PaaS) Database Engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. It is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. Azure SQL Database is based on the latest stable version of the Microsoft SQL Server database engine.

Table Usage Guide

The azure_sql_database table provides insights into SQL databases within Microsoft Azure. As a Database Administrator, explore database-specific details through this table, including status, server information, and configuration settings. Utilize it to uncover information about databases, such as their current status, the server they are hosted on, and specific configuration settings.

Examples

Basic info

Explore the basic details of your Azure SQL databases such as name, id, server name, location, and edition. This query can be utilized to better understand your SQL database configuration and assess any potential changes or updates that may be necessary.

select
name,
id,
server_name,
location,
edition
from
azure_sql_database;
select
name,
id,
server_name,
location,
edition
from
azure_sql_database;

List databases that are not online

Discover the segments that consist of databases that are not currently online. This is particularly useful for identifying potential issues and ensuring the smooth functioning of your system.

select
name,
id,
server_name,
location,
edition,
status
from
azure_sql_database
where
status != 'Online';
select
name,
id,
server_name,
location,
edition,
status
from
azure_sql_database
where
status != 'Online';

List databases that are not encrypted

Identify instances where Azure SQL databases are unencrypted. This is crucial for assessing potential security vulnerabilities in your database infrastructure.

select
name,
id,
server_name,
location,
edition,
transparent_data_encryption ->> 'status' as encryption_status
from
azure_sql_database
where
transparent_data_encryption ->> 'status' != 'Enabled';
select
name,
id,
server_name,
location,
edition,
json_extract(transparent_data_encryption, '$.status') as encryption_status
from
azure_sql_database
where
json_extract(transparent_data_encryption, '$.status') != 'Enabled';

Query examples

Control examples

Schema for azure_sql_database

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
audit_policyjsonbThe database blob auditing policy.
cloud_environmenttextThe Azure Cloud Environment.
collationtextThe collation of the database.
containment_statebigintThe containment state of the database.
create_modetextSpecifies the mode of database creation.
creation_datetimestamp with time zoneThe creation date of the database.
current_service_objective_idtextThe current service level objective ID of the database.
database_idtextThe ID of the database.
default_secondary_locationtextThe default secondary region for this database.
earliest_restore_datetimestamp with time zoneThis records the earliest start date and time that restore is available for this database.
editiontextThe edition of the database.
elastic_pool_nametextThe name of the elastic pool the database is in.
failover_group_idtextThe resource identifier of the failover group containing this database.
idtextContains ID to identify a database uniquely.
kindtextKind of the database.
locationtextLocation of the database.
max_size_bytestextThe max size of the database expressed in bytes.
nametext=The friendly name that identifies the database.
read_scaletextReadScale indicates whether read-only connections are allowed to this database or not if the database is a geo-secondary.
recommended_indexjsonbThe recommended indices for this database.
recovery_services_recovery_point_resource_idtextSpecifies the resource ID of the recovery point to restore from if createMode is RestoreLongTermRetentionBackup.
regiontextThe Azure region/location in which the resource is located.
requested_service_objective_idtextThe configured service level objective ID of the database.
requested_service_objective_nametextThe name of the configured service level objective of the database.
resource_grouptext=The resource group which holds this resource.
restore_point_in_timetimestamp with time zoneSpecifies the point in time of the source database that will be restored to create the new database.
retention_policy_idtextRetention policy ID.
retention_policy_nametextRetention policy Name.
retention_policy_propertyjsonbLong term Retention policy Property.
retention_policy_typetextLong term Retention policy Type.
sample_namejsonbIndicates the name of the sample schema to apply when creating this database.
server_nametext=The name of the parent server of the database.
service_level_objectivejsonbThe current service level objective of the database.
service_tier_advisorsjsonbThe list of service tier advisors for this database.
source_database_deletion_datetimestamp with time zoneSpecifies the time that the database was deleted when createMode is Restore and sourceDatabaseId is the deleted database's original resource id.
source_database_idtextSpecifies the resource ID of the source database if createMode is Copy, NonReadableSecondary, OnlineSecondary, PointInTimeRestore, Recovery, or Restore.
statustextThe status of the database.
subscription_idtextThe Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
transparent_data_encryptionjsonbThe transparent data encryption info for this database.
typetextType of the database.
vulnerability_assessment_scan_recordsjsonbThe vulnerability assessment scan records for this database.
vulnerability_assessmentsjsonbThe vulnerability assessments for this database.
zone_redundantbooleanIndicates if the database is zone redundant or not.

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_sql_database