turbot/azure

steampipe plugin install azuresteampipe plugin install azure
azure_ad_groupazure_ad_service_principalazure_ad_userazure_api_managementazure_app_service_environmentazure_app_service_function_appazure_app_service_planazure_app_service_web_appazure_application_security_groupazure_batch_accountazure_compute_availability_setazure_compute_diskazure_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_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_container_registryazure_cosmosdb_accountazure_cosmosdb_mongo_databaseazure_cosmosdb_sql_databaseazure_data_factoryazure_data_factory_datasetazure_data_factory_pipelineazure_data_lake_analytics_accountazure_data_lake_storeazure_diagnostic_settingazure_eventhub_namespaceazure_express_route_circuitazure_firewallazure_iothubazure_key_vaultazure_key_vault_deleted_vaultazure_key_vault_keyazure_key_vault_managed_hardware_security_moduleazure_key_vault_secretazure_kubernetes_clusterazure_lbazure_lb_backend_address_poolazure_lb_nat_ruleazure_lb_outbound_ruleazure_lb_probeazure_lb_ruleazure_locationazure_log_alertazure_log_profileazure_logic_app_workflowazure_management_lockazure_mariadb_serverazure_mssql_elasticpoolazure_mssql_managed_instanceazure_mysql_serverazure_network_interfaceazure_network_security_groupazure_network_watcherazure_network_watcher_flow_logazure_policy_assignmentazure_policy_definitionazure_postgresql_serverazure_providerazure_public_ipazure_recovery_services_vaultazure_redis_cacheazure_resource_groupazure_resource_linkazure_role_assignmentazure_role_definitionazure_route_tableazure_search_serviceazure_security_center_auto_provisioningazure_security_center_contactazure_security_center_jit_network_access_policyazure_security_center_settingazure_security_center_subscription_pricingazure_servicebus_namespaceazure_sql_databaseazure_sql_serverazure_storage_accountazure_storage_blobazure_storage_blob_serviceazure_storage_containerazure_storage_queueazure_storage_tableazure_storage_table_serviceazure_stream_analytics_jobazure_subnetazure_subscriptionazure_tenantazure_virtual_networkazure_virtual_network_gateway

Table: azure_sql_server

An Azure SQL server is a relational database management system. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).

Examples

List servers that have auditing disabled

select
name,
id,
audit -> 'properties' ->> 'state' as audit_policy_state
from
azure_sql_server,
jsonb_array_elements(server_audit_policy) as audit
where
audit -> 'properties' ->> 'state' = 'Disabled';

List servers with an audit log retention period less than 90 days

select
name,
id,
(audit -> 'properties' ->> 'retentionDays')::integer as audit_policy_retention_days
from
azure_sql_server,
jsonb_array_elements(server_audit_policy) as audit
where
(audit -> 'properties' ->> 'retentionDays')::integer < 90;

List servers that have advanced data security disabled

select
name,
id,
security -> 'properties' ->> 'state' as security_alert_policy_state
from
azure_sql_server,
jsonb_array_elements(server_security_alert_policy) as security
where
security -> 'properties' ->> 'state' = 'Disabled';

List servers that have Advanced Threat Protection types set to All

select
name,
id,
security -> 'properties' -> 'disabledAlerts' as security_alert_policy_state
from
azure_sql_server,
jsonb_array_elements(server_security_alert_policy) as security,
jsonb_array_elements_text(security -> 'properties' -> 'disabledAlerts') as disabled_alerts,
jsonb_array_length(security -> 'properties' -> 'disabledAlerts') as alert_length
where
alert_length = 1
and disabled_alerts = '';

List servers that do not have an Active Directory admin set

select
name,
id
from
azure_sql_server
where
server_azure_ad_administrator is null;

List servers for which TDE protector is encrypted with the service-managed key

select
name,
id,
encryption ->> 'kind' as encryption_protector_kind
from
azure_sql_server,
jsonb_array_elements(encryption_protector) as encryption
where
encryption ->> 'kind' = 'servicemanaged';

.inspect azure_sql_server

Azure SQL Server

NameTypeDescription
administrator_logintextSpecifies the username of the administrator for this server.
administrator_login_passwordtextThe administrator login password.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
encryption_protectorjsonbThe server encryption protector.
firewall_rulesjsonbA list of firewall rules fro this server.
fully_qualified_domain_nametextThe fully qualified domain name of the server.
idtextContains ID to identify a SQL server uniquely.
kindtextThe Kind of sql server.
locationtextThe resource location.
nametextThe friendly name that identifies the SQL server.
regiontextThe Azure region/location in which the resource is located.
resource_grouptextThe resource group which holds this resource.
server_audit_policyjsonbSpecifies the audit policy configuration for server.
server_azure_ad_administratorjsonbSpecifies the active directory administrator.
server_security_alert_policyjsonbSpecifies the security alert policy configuration for server.
server_vulnerability_assessmentjsonbSpecifies the server's vulnerability assessment.
statetextThe state of the server.
subscription_idtextThe Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
tags_srcjsonbSpecifies the set of tags attached to the server.
titletextTitle of the resource.
typetextThe resource type of the SQL server.
versiontextThe version of the server.
virtual_network_rulesjsonbA list of virtual network rules for this server.