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

Name	Type	Description
administrator_login	text	Specifies the username of the administrator for this server.
administrator_login_password	text	The administrator login password.
akas	jsonb	Array of globally unique identifier strings (also known as) for the resource.
encryption_protector	jsonb	The server encryption protector.
firewall_rules	jsonb	A list of firewall rules fro this server.
fully_qualified_domain_name	text	The fully qualified domain name of the server.
id	text	Contains ID to identify a SQL server uniquely.
kind	text	The Kind of sql server.
location	text	The resource location.
name	text	The friendly name that identifies the SQL server.
region	text	The Azure region/location in which the resource is located.
resource_group	text	The resource group which holds this resource.
server_audit_policy	jsonb	Specifies the audit policy configuration for server.
server_azure_ad_administrator	jsonb	Specifies the active directory administrator.
server_security_alert_policy	jsonb	Specifies the security alert policy configuration for server.
server_vulnerability_assessment	jsonb	Specifies the server's vulnerability assessment.
state	text	The state of the server.
subscription_id	text	The Azure Subscription ID in which the resource is located.
tags	jsonb	A map of tags for the resource.
tags_src	jsonb	Specifies the set of tags attached to the server.
title	text	Title of the resource.
type	text	The resource type of the SQL server.
version	text	The version of the server.
virtual_network_rules	jsonb	A list of virtual network rules for this server.

turbot/azure